7 Replies Latest reply: Jan 4, 2013 3:40 AM by kuljeet singh - RSS

    Automatic Statistics gathering vs manual Statistics gathering

    Ankit Ashok Aggarwal
      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
          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
            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
              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
                Ankit Ashok Aggarwal
                this is the version of database Release 10.2.0.4.0.
                • 5. Re: Automatic Statistics gathering vs manual Statistics gathering
                  Ankit Ashok Aggarwal
                  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
                    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
                      kuljeet singh -
                      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