Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

 
 

Automating KEEP Pool Assignment

The Oracle documentation states:

 

A good candidate for a segment to put into the KEEP pool is a segment that is smaller than 10% of the size of the DEFAULT buffer pool and has incurred at least 1% of the total I/Os in the system.  

 

It is easy to locate segments that are less than 10% of the size of their data buffer, but Oracle only has v$segstat to track I/O at the segment level. To get around this issue, some DBAs place each segment into an isolated tablespace so that the AWR can show the total I/O, but you can also use STATSPACK (with level 7 snapshots) and the AWR both capture and report segment level statistics. These statistics include the physical reads in 9i, and the number of segment scans in 10g.

 

Since the idea of the KEEP is to fully cache the object, the goal is to locate those objects that are small and experience a disproportional amount of I/O activity.  Using this guideline, there are two approaches.  Unlike the recommendation from the Oracle documentation, these approaches can be completely automated:

§       Cache tables & indexes where the table is small (<50 blocks) and the table experiences frequent full-table scans.

§       Cache any objects that consume more than 10% of the size of their data buffer.

The first method that uses v$sql_plan to examine all execution plans, searching for small-table, full-table scans, and is found in get_keep_pool.sql.  This can automatically generate the KEEP syntax for any small table, with the DBA adjusting the table size threshold, for tables that have many full-table scans. 

 

·       get_keep_pool.sql

 

-- *************************************************

-- Copyright © 2010 by Rampant TechPress

-- *************************************************

 

select

   'alter table '||p.owner||'.'||p.name||' storage (buffer_pool keep);'

from

   dba_tables t,

   dba_segments s,

   dba_hist_sqlstat a,

   (select distinct

     pl.sql_id,

     pl.object_owner owner,

     pl.object_name name

   from

      dba_hist_sql_plan pl

   where

      pl.operation = 'TABLE ACCESS'

      and

      pl.options = 'FULL') p

where

   a.sql_id = p.sql_id

   and

   t.owner = s.owner

   and

   t.table_name = s.segment_name

   and

   t.table_name = p.name

   and

   t.owner = p.owner

   and

   t.owner not in ('SYS','SYSTEM')

   and

   t.buffer_pool <> 'KEEP'

having

   s.blocks < 50

group by

   p.owner, p.name, t.num_rows, s.blocks

UNION

-- ***********************************************************

-- Next, get the index names

-- ***********************************************************

select

   'alter index '||owner||'.'||index_name||' storage (buffer_pool keep);'

from

   dba_indexes 

where

   owner||'.'||table_name in

(

select

   p.owner||'.'||p.name

from
SEE CODE DEPOT FOR FULL SCRIPTS

   dba_tables        t,

   dba_segments      s,

   dba_hist_sqlstat    a,

   (select distinct

     pl.sql_id,

     pl.object_owner owner,

     pl.object_name name

   from

      dba_hist_sql_plan pl

   where

      pl.operation = 'TABLE ACCESS'

      and

      pl.options = 'FULL') p

where

   a.sql_id = p.sql_id

   and

   t.owner = s.owner

   and

   t.table_name = s.segment_name

   and

   t.table_name = p.name

   and

   t.owner = p.owner

   and

   t.owner not in ('SYS','SYSTEM')

   and

   t.buffer_pool <> 'KEEP'

having

   s.blocks < 50

group by

   p.owner, p.name, t.num_rows, s.blocks

)


By running this script, the Oracle10g v$ views can be used to generate suggestions for the KEEP syntax, based on the number of blocks in the object.

 

alter index DING.PK_BOOK storage (buffer_pool keep);                           

alter table DING.BOOK storage (buffer_pool keep);                              

alter table DING.BOOK_AUTHOR storage (buffer_pool keep);                       

alter table DING.PUBLISHER storage (buffer_pool keep);                         

alter table DING.SALES storage (buffer_pool keep);    

 

Another method for identifying tables and indexes for the KEEP pool examines the current blocks in the data buffer.   For this query, the rules are simple.  Any object that has more than 80% of its data blocks in the data buffer should probably be fully cached.

It is highly unlikely that an undeserving table or index would meet this criterion.  Of course, this script would need to be run numerous times during the day because the buffer contents change very rapidly.

The script in keep_syn.sql can be run every hour via dbms_job , and automate the monitoring of KEEP pool candidates.  Every time it finds a candidate, the DBA will execute the syntax and adjust the total KEEP pool size to accommodate the new object.

 

·       keep_syn.sql

 

-- *************************************************

-- Copyright © 2010 by Rampant TechPress

--- *************************************************

 

set pages 999

set lines 92

 

 

spool keep_syn.lst

 

drop table t1;

 

create table t1 as

select

   o.owner          owner,

   o.object_name    object_name,

   o.subobject_name subobject_name,

   o.object_type    object_type,

   count(distinct file# || block#)         num_blocks

from
SEE CODE DEPOT FOR FULL SCRIPTS

   dba_objects  o,

   v$bh         bh

where

   o.data_object_id  = bh.objd

and

   o.owner not in ('SYS','SYSTEM')

and

   bh.status != 'free'

group by

   o.owner,

   o.object_name,

   o.subobject_name,

   o.object_type

order by

   count(distinct file# || block#) desc

;

 

select

   'alter '||s.segment_type||' '||t1.owner||'.'||s.segment_name||' storage (buffer_pool keep);'

from
SEE CODE DEPOT FOR FULL SCRIPTS

   t1,

   dba_segments s

where

   s.segment_name = t1.object_name

and

   s.owner = t1.owner

and

   s.segment_type = t1.object_type

and

   nvl(s.partition_name,'-') = nvl(t1.subobject_name,'-')

and

   buffer_pool <> 'KEEP'

and

   object_type in ('TABLE','INDEX')

group by

   s.segment_type,

   t1.owner,

   s.segment_name

having

   (sum(num_blocks)/greatest(sum(blocks), .001))*100 > 80

;

 

The following is a sample of the output from this script:

alter TABLE IS.GL_JE_BATCHES storage (buffer_pool keep);

alter INDEX IS.GL_JE_BATCHES_U2 storage (buffer_pool keep);

alter TABLE IS.GL_JE_HEADERS storage (buffer_pool keep);


Once the segments for assignment to the KEEP pool have been identified, the DBA will need to adjust the
db_keep_cache_size parameter to ensure that it has enough blocks to fully cache all of the segments that are assigned to the pool.
 

Of course, there are many exceptions to this automated approach.  For example, these scripts do not handle table partitions and other object types.  Hence, these scripts should be used as a framework for a KEEP pool caching strategy, and should not be run as is.
 

The next section provides information on scripts that can be used to automate the identification of objects for the RECYCLE pool. The identification of candidates for the RECYCLE pool is very similar to the KEEP pool process.

 


This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference". 

You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm

 

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks