Re: Analyzed Schema

Kirsten,

If the optimizer_mode parameter is set to CHOOSE, then the presence of statistics created by analyzing a table will force cost-based optimization for any SQL statements involving that table (even if other tables in the same SQL statement have not been analyzed).

If you have set optimizer_mode to CHOOSE and there are no statistics present on any of the tables in a SQL statement (none of them have been analyzed), then RULE-based optimization will be used.

Typically, very poor optimization decisions are made by the cost-based optimizer when some tables in a SQL statement have been analyzed and some have not.

An easy way to avoid this problem is either to analyze all of your tables or to analyze none of them. If only some tables are analyzed, then you need to be cognizant of the problems that may arise and test carefully for them.

Also note that stale statistics can cause the cost-based optimizer to make poor decisions. This is particularly true if the data, and especially the distribution of the data, changes a lot. So if you are going to analyze tables, thought needs to be given to how often each table should be analyzed. Maintenance jobs need to be set up to analyze your tables on a periodic basis.

One con of analyzing tables is the amount of processing time required to analyze them. Especially if you are computing statistics. Users may experience delays accessing
tables while they are being
analyzed. This may require an off-hours Received on Thu Oct 19 2000 - 08:26:21 CDT