Oracle-L: RE: shared pool

From: Reddy, Madhusudana <Madhusudana.Reddy_at_bestbuy.com>
Date: Wed, 22 May 2002 08:18:37 -0800
Message-ID: <F001.00467BC6.20020522081837@fatcity.com>

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 - psherman_at_elcom.com

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Sherman, Paul R.
INET: PSherman_at_elcom.com

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: ListGuru_at_fatcity.com (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: Madhusudana.Reddy_at_bestbuy.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------