This discussion is archived
7 Replies Latest reply: Jan 4, 2013 1:40 AM by KuljeetPalSingh RSS

Automatic Statistics gathering vs manual Statistics gathering

AnkitAshokAggarwal Explorer
Currently Being Moderated
We are planning to switch from automatic statistics gathering to manual statistics gathering for our oracle databases.

please share your view?

also do you have any tested script for same where we can implement manual statistics gathering for our oracle databases.

Please help?

thanks
  • 1. Re: Automatic Statistics gathering vs manual Statistics gathering
    sb92075 Guru
    Currently Being Moderated
    Ankit Ashok Aggarwal wrote:
    We are planning to switch from automatic statistics gathering to manual statistics gathering for our oracle databases.

    please share your view?

    also do you have any tested script for same where we can implement manual statistics gathering for our oracle databases.

    Please help?

    thanks
    post SQL & results that lead you to take this course of action.
  • 2. Re: Automatic Statistics gathering vs manual Statistics gathering
    Mark D Powell Guru
    Currently Being Moderated
    What full version of Oracle? On our 10gR2 systems we have found the automatic gathering seems to work fairly well. If you are having problems with specific tables or specific queries against those tables there may be other approaches than taking manual control of statistics generation. With 11g you can override the statistics gathering parameters at the table level if needed for specific tables. A explanation of why you plan to switch to manual statistics gathering may be helpful.

    HTH -- Mark D Powell --
  • 3. Re: Automatic Statistics gathering vs manual Statistics gathering
    Brian Bontrager Expert
    Currently Being Moderated
    please share your view?
    Unless you have a specific situation, such as coordinating stats gathering with batch ETL processes, creating your own manual stats gathering scripts (which you will soon want to schedule/automate) is spending your time and money to rebuild functionality that you already paid for as part of your Oracle license.

    If there is a benefit to writing your own scripts, those scripts will be specific to your data and environment. A script from anyone else that is generic enough to work in your environment will be little different than what the automatic jobs do.

    My recommendation:
    1. Write your manual stats gathering scripts (and post questions here when you need help with specific issues). You will learn a lot about the stats gathering process, and about your own environment.
    2. Compare database performance and explain plans under your scripts to performance and plans under the built-in automatic jobs.
    3. If after all of that you measure better performance with your scripts, then decide to switch; you will have supporting evidence to back up your decision and a set of tested scripts.
  • 4. Re: Automatic Statistics gathering vs manual Statistics gathering
    AnkitAshokAggarwal Explorer
    Currently Being Moderated
    this is the version of database Release 10.2.0.4.0.
  • 5. Re: Automatic Statistics gathering vs manual Statistics gathering
    AnkitAshokAggarwal Explorer
    Currently Being Moderated
    also mates can you please explain with your experience that,

    "What can be the reason which can lead DBA to decide for manual statistics gathering on place of automatic ?".
  • 6. Re: Automatic Statistics gathering vs manual Statistics gathering
    Rob_J Journeyer
    Currently Being Moderated
    From personal experience we've seen plan changes on PROD when we have let automatic stats gathering jobs run. I have removed the automatic jobs and we only change the stats once they have been tested in DEV and QA environments to ensure that there are no SQL regressions. My opinion, and one which is not universally accepted, is that making a stats change in PROD is the same as any other change. It introduces risk. You could come in on a Monday morning after a stats gather job at the weekend and find that your application is really slow bcause of some plan change.

    Having said that, however, have a read of this article from Richard Foote on [url http://richardfoote.wordpress.com/2010/02/16/how-does-an-execution-plan-suddenly-change-when-the-statistics-remain-the-same-in-limbo/]execution plan changes when stats remain unchanged. Good article.

    Here is something on [url http://www.ora00600.com/scripts/statistics/gathering_statistics.html]gathering stats in Oracle and how to [url http://www.ora00600.com/scripts/statistics/restore_schema_stats.html]restore stats if there is an issue.

    In short, you need to decide based upon your application. But whatever you decide to do there needs to be a reason why you are doing it.

    That's my 2 pence worth!
    Rob
  • 7. Re: Automatic Statistics gathering vs manual Statistics gathering
    KuljeetPalSingh Guru
    Currently Being Moderated
    have a look
    http://www.oracle.com/technetwork/database/focus-areas/bi-datawarehousing/twp-bp-optimizer-stats-04042012-1577139.pdf

    MOS- Recommendations for Gathering Optimizer Statistics on 10g Doc ID: 605439.1

Legend

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