Oracle-L: RE: e: shared pool

From: Sherman, Paul R. <PSherman_at_elcom.com>
Date: Wed, 22 May 2002 13:20:53 -0800
Message-ID: <F001.0046851F.20020522132053@fatcity.com>

Rafiq,

It's 8.1.7.2.1 OPS, 32-bit, on an HP-UX 11. Sorry about that. I should always start with that info on any question.

Thank you,

Paul Sherman
DBA Elcom, Inc.
voice - 781-501-4143 (direct #)
fax - 781-278-8341 (secure)
email - psherman_at_elcom.com

-----Original Message-----

Sent: Wednesday, May 22, 2002 3:52 PM
To: Multiple recipients of list ORACLE-L

Paul
1)What version of oracle ? 8i or 7.3.4. This script was for 8i.

I use another script for 7.3.4
select substr(c.owner,1,5)"OWNER",substr(c.name,1,25)"name",

       round(c.sharable_mem / 1024) K,
       c.loads,
       c.executions,
       c.kept
from v$db_object_cache c,
     sys.obj$ o,
     sys.user$ u

where kept = 'NO'
and u.name = c.owner

  and u.user# = o.owner#
  and o.name = c.name
  and o.type in (7, 8, 9)

order by 4,2 desc
/

2)Please check from dba_objects for object_name = 'REQUISITION'(if you think

it is a table) and you will see there may be other objects with same name besides table....

Regards
Rafiq

Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: Wed, 22 May 2002 10:41:30 -0800

Rafiq,

Well, the output sure looks like tables (see below). I do not have any procs/triggers/pkgs, etc. named as seen in the output. Please note that I have changed the owner column for security reasons.

OWNER name                               K      LOADS EXECUTIONS KEP


----- ------------------------- ---------- ---------- ---------- ---

E 	DYNAMIC_OPTIONS_SELECTED           2         34         93 NO
C 	DYNAMIC_OPTIONS_SELECTED           1         33          5 NO
O 	DYNAMIC_OPTIONS_SELECTED           2         33          6 NO
E 	REQUISITION                        2         33         44 NO
C 	DYNAMIC_OPTIONS_SELECTED           1         33         51 NO
K 	DYNAMIC_OPTIONS_SELECTED           2         33        111 NO
K 	PCARD_ACCT_DEFAULT                 2         32          6 NO
U 	DYNAMIC_OPTIONS_SELECTED           1         32         11 NO
C 	DYNAMIC_OPTIONS_SELECTED           1         32         15 NO
T 	UNIQUE_ID_CONTROL                  2         32         32 NO
S 	DYNAMIC_OPTIONS_SELECTED           2         32         50 NO

Thank you,

Paul Sherman
DBA Elcom, Inc.
voice - 781-501-4143 (direct #)
fax - 781-278-8341 (secure)
email - psherman_at_elcom.com

-----Original Message-----

Sent: Wednesday, May 22, 2002 2:09 PM
To: Multiple recipients of list ORACLE-L

Paul,

That query does not give you load or execution of any table but objects like

procedure or package etc. If you want to put any lookup table of smaller size in memory you can use
alter table table_name cache;(no cache is default)

As regard question of loading or reloading of package/package body(specially

if not pinned) , it suggest that size of shared pool is smaller than your requirement and such objects are flushed and reloaded again and again so increase size of shared pool/shared_pool_reserved or start pinning it before any change and monitor it. You may use following query to monitor misses in your shared pool/shared pool reserved.

select
free_space,avg_free_size,used_space,max_used_size,request_failures,REQUEST_M ISSES
from v$shared_pool_reserved
/

There are situation when even after pinning objects, those are reloaded more

than once because of invalidation of objects due dropping of objects like tables , indexes or dependent views as per customized code.

If you have any specific question, please let me know...

Regards
Rafiq

Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: Wed, 22 May 2002 06:33:32 -0800

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

MOHAMMAD RAFIQ

Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp.

--

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

Author: Mohammad Rafiq

INET: rafiq9857_at_hotmail.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: 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

MOHAMMAD RAFIQ

Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp.

--

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

Author: Mohammad Rafiq
INET: rafiq9857_at_hotmail.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

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