Automatically Generate KEEP Syntax

Extrapolating from the above script, we can write another script that automatically identifies candidates for the KEEP pool  and generates the syntax to move the tables into the pool.

The placement criteria for tables and indexes into the KEEP buffer are straightforward:

·         Small tables – Parameters may be adjusted in the script based upon needs.

·         Experiences full-table scans – Oracle designates the table as small and chooses a full-table scan over an index access.  

·         Frequently-accessed tables – The threshold for access can be adjusted in the script.  

·         High buffer residency – Any table that has more than 80% of its blocks in the data buffer should be cached in the KEEP pool .

There are two approaches to identifying tables for the KEEP pool

1.       Tables (and associated indexes) that are small and have frequent full-table scans

2.       Objects that have more than 80% of their data blocks in the buffer 

Here are scripts for each method.
 

Caching Small-table Full-table Scans in the KEEP Pool

Here is the first method that examines all execution plans, searching for small-table full-table scans.  This is the script to automatically generate the KEEP syntax for any small table (you adjust the table size threshold) for tables that have many full-table scans.

 See code depot for full scripts

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

-- Create KEEP Pool syntax for small,

-- frequently-references tables & indexes

--

-- Copyright (c) 2003 By Donald K. Burleson - All Rights reserved.

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

 

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

-- Generate KEEP pool  syntax for appropriate tables & indexes

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

 

set pages 999;

set heading off;

set feedback off;

ttitle off;

 

spool keep_syntax.sql

 

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

-- First, get the table list

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

select

. . .

from

   dba_tables   t,

   dba_segments s,

   v$sqlarea    a,

   (select distinct

 

. . .

   from

      v$sql_plan

   where

. . .

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

   dba_tables   t,

   dba_segments s,

   v$sqlarea    a,

. . .

having

   s.blocks < 50

group by

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

)

;

 

spool off;

Run the simple script below, and Oracle will generate the KEEP syntax.

alter index PUBS.BITMAP_BOOK_TYPE storage (buffer_pool keep);                  

alter index PUBS.BTREE_TITLE_TYPE storage (buffer_pool keep);                  

alter index PUBS.PK_BOOK storage (buffer_pool keep);                           

alter table PUBS.BOOK storage (buffer_pool keep);                              

alter table PUBS.BOOK_AUTHOR storage (buffer_pool keep);                       

alter table PUBS.PUBLISHER storage (buffer_pool keep);                         

alter table PUBS.SALES storage (buffer_pool keep);  

  

---

Order now from the publisher and get 30% off the retail price!

"Oracle Tuning: The Definitive Reference" You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.