Ideas how to gather optimizer stats

Hi All,

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.

Thanks

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.

:rolleyes:

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).