Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Archive for the ‘Index’ Category

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