Analyze period ?

[mahoori]

Hi all.
Q1 : What is minimum period for analyze tables&indexes in one OLTP database for CBO(cost based optimization) ?
(2 times in week ? one time in week?each 2 week?)
Q2 : Why DBMA_UTILITY.ANALYZE_SCHEMA doesn't analyzed all tables&indexes in one schema? 25 table of all 856 not analyzed after execute this procedure.

Best Regards.

[julian]

Q1 : What is minimum period for analyze tables&indexes in one OLTP database for CBO(cost based optimization) ?

About once in a month

Q2 : Why DBMA_UTILITY.ANALYZE_SCHEMA doesn't analyzed all tables&indexes in one schema? 25 table of all 856 not analyzed after execute this procedure.

You should ask Oracle about that. I have also noticed that DBMS_UTILITY it does not work properly. I use my own scripts for analyzing DBs.

[mahoori]

Hi julian.
Thank you .

[TimHall]

It really depends on the volatility of your data. Initially you will need to analyze very frequently but as time goes on it's not so important. By that I mean, adding 10000 records to an empty table has a bigger impact than adding 10000 records to a 10 Million row table.

This is what Oracle say:

"Objects are considered stale when 10% of the total rows have been changed."

You may wish to analyze table at different rates:

Static Tables: Monthly
Volatile Tables: Daily
Others: Weekly

As for your other problem, have you tried:

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('MYSCHEMA', cascade => TRUE);

You can also use DBMS_STATS to transfer stats from your production environment to your development environment. That way you will have similar execution plans irrespective of the actual data.