Re: Buffer pool query

"Ryan S" <rshevchi_at_vt.edu> wrote in message news:1122910677.938247.202180_at_o13g2000cwo.googlegroups.com...
> Oracle 9i on Solaris -- I'm running a query that shows the object and
> how many blocks each object has in the buffer pool. Is it possible or
> does anyone have a query that differentiates between the recycle,
> default, and keep pool?
>
> Thanks
>

Hi,

I found this query in the 10gR2 performance tuning guide: COLUMN OBJECT_NAME FORMAT A40
COLUMN NUMBER_OF_BLOCKS FORMAT 999,999,999,999 SELECT o.OBJECT_NAME, COUNT(*) NUMBER_OF_BLOCKS

     FROM DBA_OBJECTS o, V$BH bh
    WHERE o.DATA_OBJECT_ID = bh.OBJD
      AND o.OWNER != 'SYS'
    GROUP BY o.OBJECT_NAME
    ORDER BY COUNT(*); http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/memory.htm#sthref537

Also consider the column, BUFFER_POOL, in dba_segments. This tells in which pool the segment's blocks are to be loaded. To my knowledge, if using multiple buffer pools, Oracle will always load blocks into the buffer pool assigned for the segment.

You can do a join on OBJECT_NAME = SEGMENT_NAME and a group by expression to find how many blocks are in the individual buffer pool and which segments has data in these blocks.

This may only apply to tables, however. A different methodology may have to be used to find which segments were pinned using the DBMS_SHARED_POOL.KEEP procedure, but upon initial research, the previous methodology should give you a start.

Here is a view that should help with other object types: v$db_object_cache
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_1083.htm#sthref3309

Of particular interest is the column, KEPT, which is described as: (YES|NO) Depends on whether this object has been "kept" (permanently pinned in memory) with the PL/SQL procedure DBMS_SHARED_POOL.KEEP.

Note,
I won't write the query for you, though. :-D

Hope this helps.

-- 
Andreas
Oracle 9i Certified Professional
Oracle 10g Certified Professional
Oracle 9i Certified PL/SQL Developer


"If you don't eat your meat, you cannot have any pudding.
"How can you have any pudding if you don't eat your meat?!?!"
---

WARNING:
DO NOT REPLY TO THIS EMAIL
Reply to me only on this newsgroup