2 Replies Latest reply on Oct 3, 2016 7:32 AM by Chris Antognini

    PCSGM vs SPCGM IDX

    user474398

      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.

       

      IDX.jpg

      Any hints or suggestions that can help us look into the right place are welcome.

       

      Thanks,

      Priya

        • 1. Re: PCSGM vs SPCGM IDX
          alwu-Oracle

          Hi Priya,

           

          After the upgrade, did you rerun a statistics collection? It's a bit hard to say how much of each index is going to be cached. That depends on the workload and also the query execution plans.

           

          An additional thing to try is to set the optimizer back to 11.2 and see if the behavior goes back.

           

          e.g. (the following assumes your old DB was 11.2.0.4)

           

          alter session set optimizer_features_enable='11.2.0.4';

           

          Thanks,

           

          Zhe Wu

          • 2. Re: PCSGM vs SPCGM IDX
            Chris Antognini

            Hi

             

            Not sure at all to understand what do you mean... Hence, I have more questions than answers ;-) Sorry about that.

             

            How did you check that "PCSGM index being used more"? More executions plans are using it? More LIO? More PIO?

             

            Which metric do you use to check "the index size fluctuating a lot"? In other words, what's the y-axis of your Chart?

             

            About caching... How is your buffer cache sized? If it's automatically done by the database engine there are chances that with the upgrade from 11g to 12c less memory is available for the buffer cache...

             

            Cheers,

            Chris