Hi All,
We had our application upgraded to Oracle 12C in production from 11G recently that uses Oracle Semantic database and Ontology and since the upgrade we see PCSGM index being used more and suddenly the response time of our application has gone up significantly (unto 40 minutes to process certain update requests). We have about 333M tuples in our database.
No application code or middle tier changes were done except the database was upgraded. We are noticing the index size fluctuating a lot - more than by 100K in sawtooth pattern. The SPCGM index are indicated in dark pink circles, and PCSGM are indicated in blue squares. Also how much of these indexes should be cached (we are seeing some toggling happening between these two indexes in the monitoring reports)? Please see the screenshot of indexes from monitoring report below.
Any hints or suggestions that can help us look into the right place are welcome.
Thanks,
Priya