Donald K. Burleson Oracle Tips

Find indexes not in the KEEP Pool

A common problem occurs when an Oracle professional recognizes the benefit of placing small, frequently referenced tables in the KEEP pool, but forgets to place the associated indexes in the KEEP pool.

Just as you want frequently referenced tables in the KEEP pool, you also want to cache the indexes to minimize disk I/O.

set pages 999

set echo off

spool t.lst

col c1 heading 'Index|Name'   format a30

col c2 heading 'Table|Name'   format a30

col c3 heading 'Index|Blocks' format 999,999,999,999

select

   index_name    c1,

   t.table_name  c2,

   s.blocks      c3

from

   dba_indexes    i,

   dba_tables     t,

   dba_segments   s

where

   t.table_name = i.table_name

and

   i.index_name = s.segment_name

and

   t.buffer_pool = 'KEEP'

and

   i.buffer_pool <> 'KEEP'

;

select

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

from

   dba_indexes    i,

   dba_tables     t,

   dba_segments   s

where

   t.table_name = i.table_name

and

   i.index_name = s.segment_name

and

   t.buffer_pool = 'KEEP'

and

   i.buffer_pool <> 'KEEP'

;

select

   sum(s.blocks) add_to_keep_pool

from

   dba_indexes    i,

   dba_tables     t,

   dba_segments   s

where

   t.table_name = i.table_name

and

   i.index_name = s.segment_name

and

   t.buffer_pool = 'KEEP'

and

   i.buffer_pool <> 'KEEP'

;

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link: