How do we store Index in the KEEP Buffer pool ?
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.