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 - [EMAIL PROTECTED]
-----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: [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).
--
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).
