This discussion is archived
1 2 Previous Next 20 Replies Latest reply: May 2, 2012 6:49 AM by Ishan Go to original post RSS
  • 15. Re: Gather Stats taking time
    AdamMartin Pro
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points