Looking for some solid suggestions on an approach for optimization. We have been running Oracle text for over 10 years and trying to get as much performance possible for our website that relies on it. Our index which gets frequent updates and has synch on commit to meet business requirements consists mainly of data text fragments. The index is 154G and the base table data is 15G. We are in a RAC environment and have 200G of memory on each node in a 12.1.3 environment. Our SGA is 46G and we want to bump is up so we can cache more of the index. We currently have cached the $X and $R tables and would like to bump up the SGA to over 100G and pin $X,$R,$K,$I and Base table data. My main concern...is this the correct approach and what are the drawbacks?
Thanks so much in advance for your input!
Heather, if you'd like to discuss this with me in more detail, please contact me by email. You'll find my email address at the end of the article Barbara refers to.
I'd be happy to help you out as best I can.