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 18.104.22.168.0