Ideas how to gather optimizer stats
I'm having an issue with stale optimizer stats for some SQLs that are run in a batch process.
The problem is that the process runs many times during the day - sometimes 20 to 30 times.
And each time, the tables are updated, i.e. rows are inserted or deleted, etc.
So eventually the optimizer stats for those tables become stale and the performance
of the SQLs start to slow down (a lot).
Any ideas how best to gather the optimizer stats on the tables so they
don't become stale when the batch process runs each time?
The problem is that I also can't add/modify the code in the batch process because it is delivered by the vendor as is.
Any suggestions are greatly appreciated.
You can schedule frequent gather stats on that particular table.
What version Oracle?
If you have 10g or higher you can set the STATISTICS_LEVEL to the default setting of TYPICAL which ensures collection of all major statistics required for database self-management functionality and provides best overall performance.
Enable the automated maintenance task (Optimizer Statistics Gathering) to generate optimizer statistics regularly.
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
You can also schedule dbms_stats.gather_schema_stats to run on the particular schema just after the batch job runs. Set options to gather_auto, this option will gather stats on statsless tables as well as in stale ones (tables that had 10%+ changes since last stats collection).
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Click Here to Expand Forum to Full Width