1 2 Previous Next 20 Replies Latest reply: May 2, 2012 8:49 AM by Ishan Go to original post RSS
      • 15. Re: Gather Stats taking time
        AdamMartin
        The partition section itself is taking lot of time to complete and as a result we have to keep it on hold as it doesn't seem to complete.
        It sounds like it hasn't had a chance to finish even once. I believe it needs to full scan the entire table (all partitions) the first time. Then, on the second and subsequent runs, you will benefit from incremental global stats because it should need to only read one partition.
        • 16. Re: Gather Stats taking time
          Ishan
          Adam Martin wrote:
          The partition section itself is taking lot of time to complete and as a result we have to keep it on hold as it doesn't seem to complete.
          It sounds like it hasn't had a chance to finish even once. I believe it needs to full scan the entire table (all partitions) the first time. Then, on the second and subsequent runs, you will benefit from incremental global stats because it should need to only read one partition.
          I knew this would be coming... and I have checked it twice.... It had run successfully earlier.. and still it's taking time...

          PS: We have raised this bug with ORACLE too and they have accepted it as a bug, but no solution from past 6 months.

          Just wanted to check if someone has actually implemented this INCREMENTAL option with huge no. of partitions in the database. Acc. to the documentation, I don't see any issues with the CODE or any other parameters but still its taking time.
          • 17. Re: Gather Stats taking time
            AdamMartin
            Just wanted to check if someone has actually implemented this INCREMENTAL option with huge no. of partitions in the database.
            I sense your confidence is diminishing. :) We use it on a table that only has about 50 partitions, but each partition is about 10GB.

            It seems like the only thing to do here is to create a simple scaled-down test to challenge the assumptions that are being made about incremental stats. That's the best way to prove a concept anyway.
            • 18. Re: Gather Stats taking time
              AdamMartin
              I just ran some tests and I could not reproduce the issue you are facing. Incremental stats always worked. I am on 11.2.0.2. Also, I only tried 20 partitions. I will try again with 5000 partitions.

              Question: you say it is gathering stats for the "empty" partitions. Were they always empty, or did they become empty since last time stats were run?
              • 19. Re: Gather Stats taking time
                AdamMartin
                I could not reproduce the problem with 5000 partitions.

                Consider upgrading to 11.2.

                Have you seen this?
                https://jhdba.wordpress.com/2012/01/04/speeding-up-the-gathering-of-incremental-stats-on-partitioned-tables/
                • 20. Re: Gather Stats taking time
                  Ishan
                  Adam Martin wrote:
                  I could not reproduce the problem with 5000 partitions.

                  Consider upgrading to 11.2.

                  Have you seen this?
                  https://jhdba.wordpress.com/2012/01/04/speeding-up-the-gathering-of-incremental-stats-on-partitioned-tables/
                  This link was the master stroke Adam :)

                  This is exactly what I was looking for. Atleast now I know that there's nothing wrong with the code. We need to make the INCREMENTAL redundant for our current ORACLE version.

                  Nevertheless, thanks a ton Martin for your patience and all the help that you provided. Wish could give you 100 points at a time :)

                  Regards,
                  Ishan
                  1 2 Previous Next