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: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> 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 - [EMAIL PROTECTED] -----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: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> 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 - [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). 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: [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: 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). 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: [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).