1

We currently have a database with 20GB SGA space. SQL has been optimized but has to statisfy its use cases as well and some queries are very, very slow, specifically when large result sets are returned as the results of a text index search, which are then fetched by db file sequential read. This leads to timeouts in the application and our users complain about them. Our storage is slow and we cannot change that, unfortunatelly (slow as in 15 to 20ms peak for an IO request). The idea is now to put these tables (8GB in total) into the KEEP Pool and providing the database with 16GB additional RAM. Further, we will turn on huge pages on the system.

Do you think this is a good way to tackle the problem? Are there any unforseen pitfalls that we should be aware of? Is it easy to put the tables into the KEEP Pool? Will they stay in the KEEP Pool then or is it possible that Oracle just ignores the storage statement?

Database Version is 11.2.0.3.0

1

You can put the table in KEEP Pool cache and this may have a positive impact on your queries. I have implemented it in my system for a 16 GB partition and we did get good performance gains.

However putting a table in cache does not mean that its data is loaded into memory automatically. It only means that since we are using a separate buffer pool for these tables, the data blocks that are loaded into memory are not aged out from the buffer as frequently as they would have if you were using the default buffer pool.

So in case your queries are fetching same data blocks repeatedly there will be lesser aging out and better performance gains. However, in case your queries are fetching different data blocks which are not present in the cache then you will be having some disk reads happening and old blocks may get aged out of bufer.

Also, have you evaluated RESULT_CACHE hint for yor query, in case you are not using GTTs you may be able to get good performance gains from that also.

  • So if we do a SELECT /*+ FULL */ on the tables will they remain in their KEEP Pool forever if it is sufficiently large? Moreover, I don't think that RESULT_CACHE Hint will have huge effects, basically we cannot predict what the user might search for and all queries are hard parsed by purpose (because result set size varies so much). It will probably age out too soon. As our db file sequential reads are slow, I hope I can just keep the whole tables in memory all of the time. – Falcon May 5 '15 at 13:30
  • Using SELECT * from table should definitly keep them in the KEEP pool. If user queries are dynamic in nature and being hard pased then, yes RESULT_CACHE will not be much helpful. – vishad May 5 '15 at 13:36
  • Just keep in mind that if your table in KEEP Pool is being changed you should allocate some more memory than the table size. More memory is needed for the different versions of data blocks. In extreme cases I had tables which used >3x memory than it's segment size. – Mindaugas Riauba May 5 '15 at 13:36
  • @MindaugasRiauba: Isn't dirty buffer blocks phased out, whenever we do any read followed by a write? – vishad May 5 '15 at 13:37
  • I think you would get more success by optimizing the queries than putting tables in memory – kevinsky May 5 '15 at 19:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.