Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Loading index to keep buffer pool

with one comment

Keep buffer pool is designed to keep objects that are frequently accessed through multi block I/O.

But I’ve seen several cases that people want to load index(not table) into the keep buffer pool. To alleviate the random access I/O on index.

Some people are skeptical on this. When the index is accessed through single bock I/O, the visited blocks are loaded to the mid point of LRU list. When more sessions visit them, they would got promoted to hot area.  Yes, theoretically, there would be no reason to keep frequently accessed index. Frequentl accessed index would survive anyway!

But in real life, I’ve seen many people who want to gurantee that the index never gets flushed out from SGA(but who can guarantee?). And in one speical case, I had a customer who wanted to preload the entire index to keep buffer just after the database starts up.

The best way is to read the index by fast full scan from after-startup trigger. Like this:

create index t1_n1 on t1(c1) storage ( buffer_pool keep);

create or replace trigger trg_startup after startup on database
    for idx in (select /*+ index_ffs(t1 t1_n1) */ count(c1) from t1 where c1 is not null) loop
   end loop;

Written by Dion Cho

February 10, 2009 at 9:02 am

Posted in I/O, Index

Tagged with

One Response

Subscribe to comments with RSS.

  1. Thank you.
    It’s awesome idea! :-)


    February 18, 2009 at 5:19 am

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: