Using the KEEP pool to improve performance

In certain circumstances, pinning segments into memory - using the KEEP pool - can improve performance dramatically. I normally resort to using this feature when administering a database that can't be tuned otherwise. For example, it is often hard to change/tune the segments that are part of a COTS product, such as in a vendor-supplied ERP system.

Of course this option uses, a critical resource, memory; therefore, it's not a viable solution when large tables/indexes are involved.

The KEEP pool can be used in Data Warehouse applications as well, namely to pin aggregate materialized views, etc.

In this example, I show how to set-up and use the KEEP pool. In my example I am pinning LOB data and index segments, but I could have just as easily crafted an example that simply pinned non-LOB table/index segments.

Set-up the KEEP pool

SQL> alter system set db_keep_cache_size=64m scope=spfile;
SQL> startup;

Create a Sample Table

SQL> create table lob_pin_example ( lpe_id number(10), 
                                         lpe_desc varchar2(40),
                                         lpe_blob BLOB) 
          tablespace users 
          LOB(lpe_blob) store as lpe_blob_data
          (tablespace users disable storage in row storage 
          (buffer_pool KEEP) cache
          index lpe_blob_index);

Table created.

Load Sample Data

SQL> declare
          begin
          for i in 1..6000 loop
          insert into lob_pin_example values (i,'Test','01010101010101');
          end loop;
          commit;
          end;
          /

PL/SQL procedure successfully completed.

SQL> select count(*) from lob_pin_example;

  COUNT(*)
----------
      6000

SQL> analyze table lob_pin_example compute statistics;

Table analyzed.

When using the KEEP pool feature, I find it helpful to load the following custom view into the database as SYS. This view allows you to see into the data dictionary to determine how the KEEP pool is being utilized.

Load the objects_in_keep_pool view

CREATE OR REPLACE VIEW objects_in_keep_pool (bp_name,blocks,object_name,pool_used_size_kb,seg_size_kb)
AS
SELECT      kcbwbpd.bp_name, 
            COUNT (*) blocks, 
            ob.name,
            ROUND (bs.block_size * COUNT (*) / 1024) "POOL_USED_SIZE_KB",
            ROUND (ds.BYTES / 1024 ) "SEG_SIZE_KB"
      FROM  x$kcbwds kcbwds,
            x$kcbwbpd kcbwbpd,
            x$bh bh,
            obj$ ob,
            (SELECT block_size
              FROM v$buffer_pool
              WHERE NAME = 'KEEP') bs,
            dba_segments ds
      WHERE kcbwds.set_id >= kcbwbpd.bp_lo_sid
        AND kcbwds.set_id <= kcbwbpd.bp_hi_sid
        AND kcbwbpd.bp_size != 0
        AND kcbwds.addr = bh.set_ds
        AND bh.state != 0
        AND kcbwbpd.bp_name = 'KEEP'
        AND bh.obj = ob.obj#(+)
        AND ob.name = ds.segment_name(+)
  GROUP BY kcbwbpd.bp_name, bh.obj, ob.name, bs.block_size, ds.BYTES;

Query the Sample Table

SQL> set autotrace on explain statistics ;

SQL> select dbms_lob.substr(lpe_blob,1) from lob_pin_example;

As you can see, there are no physical reads.

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      18438  consistent gets
          0  physical reads
          0  redo size
      81128  bytes sent via SQL*Net to client
      4789  bytes received via SQL*Net from client
        401  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      6000  rows processed

Query the objects_in_keep_pool

SQL> select * from objects_in_keep_pool;

BP_NAME        BLOCKS OBJECT_NAME          POOL_USED_SIZE_KB SEG_SIZE_KB
---------- ---------- -------------------- ----------------- -----------
KEEP              48 LPE_BLOB_INDEX                    384        384
KEEP            6112 LPE_BLOB_DATA                    48896      49152

As you can see both LOB segments are in the KEEP pool.

Labels: Tuning