Pavan DBA's Blog

The DBA Knowledge Store

Script to get KEEP & RECYCLE Cache advice

Posted by Pavan DBA on February 23, 2012


 

********************************

Keep and Recycle cache advices

********************************

SELECT SIZE_FOR_ESTIMATE, BUFFERS_FOR_ESTIMATE, ESTD_PHYSICAL_READ_FACTOR, ESTD_PHYSICAL_READS

FROM V$DB_CACHE_ADVICE

WHERE NAME = ‘KEEP’

AND BLOCK_SIZE = (SELECT VALUE FROM V$PARAMETER WHERE NAME = ‘db_block_size’)

AND ADVICE_STATUS = ‘ON’;

 

 

SELECT ds.BUFFER_POOL,

Substr(do.object_name,1,9) object_name,

ds.blocks object_blocks,

Count(* ) cached_blocks

FROM dba_objects do,

dba_segments ds,

v$bh v

WHERE do.data_object_id = v.objd

AND do.owner = ds.owner (+)

AND do.object_name = ds.segment_name (+)

AND do.object_type = ds.segment_type (+)

AND ds.BUFFER_POOL IN (‘KEEP’,’RECYCLE’)

GROUP BY ds.BUFFER_POOL,

do.object_name,

ds.blocks

ORDER BY do.object_name,

ds.BUFFER_POOL;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: