RE: space used by pinned objects

From: Mohammad Rafiq <rafiq9857_at_hotmail.com>
Date: Wed, 05 Dec 2001 07:14:35 -0800
Message-ID: <F001.003D5C93.20011205063531@fatcity.com>

You can try following query
for ver 7.3.4
select substr(c.owner,1,5)"OWNER",substr(c.name,1,30)"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 c.executions > 1000 and kept = 'NO' and u.name = c.owner
and u.user# = o.owner#select
substr(c.owner,1,5)"OWNER",substr(c.name,1,30)"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 c.executions > 1000 and 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
/

for ver 8I

select substr(c.owner,1,5)"OWNER",substr(c.name,1,30)"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 c.executions > 1000 and 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
/

You may modify it according to your requirement for loads instead of execution, which I personally prefer.

MOHAMMAD RAFIQ Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: Wed, 05 Dec 2001 01:50:19 -0800

Hi,

You can select the information from V$DB_OBJECT_CACHE.

Best Regards,
K Gopalakrishnan
Bangalore, INDIA

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

Sent: Tuesday, December 04, 2001 8:55 PM To: Multiple recipients of list ORACLE-L

I've pinned some procs that were being loaded very frequently. How can I determine how much space they are using in the shared pool and that I
have plenty of space left in the shared pool? I like pinning objects, but I don't want to end up with a shared pool contiguous memory problem.

Thanks.
Doug
--

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

Author: Doug C

INET: dcowles_at_i84.net

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).

Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

--

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

Author: K Gopalakrishnan

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

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