Sherman,
I found the following SQL from Tim Gorman's site...
this will create a file , which can be run to pin the objects, It Pins
CURSORS as well as the other objects ( not the tables ).
set tab off echo off feedback off timing off trimout on pause off
set trimspool on pages 0 lines 500 verify off
col instance new_value V_INSTANCE noprint
select lower(replace(t.instance,chr(0),'')) instance
from sys.v$thread t,
sys.v$parameter p
where p.name = 'thread'
and t.thread# = to_number(decode(p.value,'0','1',p.value));
col sort0 noprint
col sort1 noprint
col sort2 noprint
col sort3 noprint
spool run_pin_&&V_INSTANCE..sql
prompt whenever sqlerror exit failure
prompt set echo on feedback on timing on pagesize 100
prompt spool run_pin_&&V_INSTANCE
select decode(kept, 'YES', 'unkeep', 'keep') sort0,
type sort1,
owner sort2,
name sort3,
'exec dbms_shared_pool.' ||
decode(kept, 'YES', 'unkeep', 'keep') || '(''' ||
owner || '.' || name || ''',''' ||
decode(type, 'TYPE', 'T',
'TRIGGER', 'R',
'SEQUENCE', 'Q', 'P') || ''');' text
from sys.v$db_object_cache
where ((executions >= 100 and kept = 'NO')
or (executions < 100 and kept = 'YES'))
and type in ('PACKAGE','PACKAGE BODY','PROCEDURE','FUNCTION','TYPE',
'TRIGGER','SEQUENCE')
union
select distinct decode(o.kept, 'YES', 'unkeep', 'keep') sort0,
o.type sort1,
o.owner sort2,
o.name sort3,
'exec dbms_shared_pool.' ||
decode(o.kept, 'YES', 'unkeep', 'keep') || '(''' ||
a.address || ', ' || a.hash_value || ''');' text
from sys.v$db_object_cache o,
sys.v$sqlarea a
where ((o.executions >= 100 and o.kept = 'NO')
or (o.executions < 100 and o.kept = 'YES'))
and o.type in ('CURSOR', 'INVALID TYPE')
and a.sql_text = o.name
order by 1 desc, 2 asc, 3 asc, 4 asc;
prompt spool off
spool off
--/*REM start run_pin_&&V_INSTANCE*/
Thanks,
Madhu
-----Original Message-----
Sent: Wednesday, May 22, 2002 9:34 AM
To: Multiple recipients of list ORACLE-L
Rafiq,
I ran your query as we have been in the process of tuning our shared pool,
and I have a question. When you see many more loads than executions for a
given table, is it a safe bet that the application in question is executing
queries that have that table in the from clause, but it is not being used by
that query ? For example, a table has 33 loads and 5 executions. Could I say
that 28 loads were caused by a query that had that table referenced, but not
used (and causing a full table scan, because that's what Oracle does when
you reference, but do not use, a table (in the from clause) ?
Thank you,
Paul Sherman
DBA Elcom, Inc.
email - [EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Sherman, Paul R.
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Reddy, Madhusudana
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
