This content has been marked as final. Show 7 replies
Ankit Ashok Aggarwal wrote:post SQL & results that lead you to take this course of action.
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.
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 --
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.
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.
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!