Database Tuning (MOSC)

MOSC Banner

How do we store Index in the KEEP Buffer pool ?

edited Apr 20, 2012 7:21AM in Database Tuning (MOSC) 5 commentsAnswered
Hi,

In referring to the numerous explain plan outputs, we noticed that one index is being constantly used and "range scanned".  Besides, most of the time was spent on that index.  Hence, I wanted to store this index in the cache to see if that reduces the time.

 I used alter TABLE My_Index storage (buffer_pool keep); Performed the query that used the index again and then I queried the v$bh view and found out that only 55% of the index blocks is being stored in the memory.

So, my questions are:

1.  How can I store the complete index in the db_keep_cache ?  Do I have control over it ?  I had increased the size of db_keep_cache_size to 700 MB.  The index only has around 9000 blocks of 1684 (block size) ~ 15MB.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center