Oracle Logbook

August 16, 2007

Objects to put in keep buffer

Filed under: Uncategorized — heliodias @ 1:21 pm

This  is a cook book that i created yesterday, so objects with high val_block is a candidate to this area.

But be in mind that the objects must be SMALL

SELECT * FROM (

SELECT OBJECT_NAME,VALUE,blocks,value/blocks val_block FROM
V$SEGMENT_STATISTICS t1,
dba_segments t2
WHERE t1.OWNER=’&schema’ and t2.OWNER=t1.owner AND
STATISTIC_NAME=’logical reads’
and segment_name=object_name
and blocks<1280
and value>100000
order by 4 desc)

For more information about buffer pool : http://jonathanlewis.wordpress.com/2007/09/05/hit-ratios-2/

About these ads

1 Comment »

  1. Adjust to fit partitioning objects:
    SELECT COUNT(*) FROM (
    SELECT * FROM (
    SELECT OBJECT_NAME,VALUE,blocks,value/blocks val_block FROM
    V$SEGMENT_STATISTICS t1,
    dba_segments t2
    WHERE t1.OWNER not in (‘SYS’,’SYSTEM’ ) and t2.OWNER=t1.owner AND
    STATISTIC_NAME=’logical reads’
    and segment_name=object_name
    AND PARTITION_NAME=SUBOBJECT_NAME
    and blocks100000
    order by 4 desc)
    );

    Comment by heliodias — July 31, 2013 @ 8:35 pm | Reply


RSS feed for comments on this post. TrackBack URI

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

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: