SEO

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

"Ryan S" <rshevchi@vt.edu> wrote in message
news:1122910677.938247.202180@o13g2000cwo.googlegr oups.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....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...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

> 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(*);
>
>
Thanks for the response -- The above query is what I'm currently
running. I was just wondering if its possible to see a breakdown of
objects per defined pool(keep, recycle). I agree I think Oracle will
load the blocks into the assigned pool, just trying to prove they are
there. The best I have been able to do is assign a 16M recycle pool
and then see that the tables allocated to it are never going over 2000
blocks(8k block size).

http://groups-beta.google.com/group/...f95e11562e7 4

jg
--
@home.com is bogus.
"Unlike many bobbing 'corks' in technology nowadays, Oracle is not
about 'floating' or 'emerging' this or that. It's a database, not
a fishing implement. " - Noons