How to: Manage CBO Statistics During an Upgrade from 10g or 9i into 11g (文档 ID 465787.1)

In this Document

APPLIES TO:

GOAL

The goal of this note is to provide guidance regarding the management of existing and new CBO stats during an upgrade to 11g from 9i or 10g. The terms pre-upgrade release and post-upgrade release are used accordingly. Pre-upgrade refers then to 9i/10g, and post-upgrade to 11g.

The core idea presented by this note is to continue gathering CBO statistics for application schemas as usual, but create a clean baseline for non-application objects (data dictionary, fixed objects and system performance).

Cost-based Optimizer (CBO) uses statistics when generating execution plans. CBO statistics can be classified into four types: 

• schema objects - In use since the introduction of the CBO
• data dictionary - introduced in 9i and considered optional to gather
• fixed objects - Introduced in 10g
• system (performance) statistics - introduced in 9i but rarely implemented.

For advice on gathering statistics on various versions, see:

Strategy: 

1. For application schema objects, use specific procedure required by the application vendor if they provide one. For example

If there is no procedure provided by the application vendor, or if yours is a home-grown application, use initially the same defaults or settings you were using in your pre-upgrade release. Be aware that some of the defaults in DBMS_STATS package have changed between 9i/10g to 11g, so you may need to use a DBMS_STATS.SET_*_PREFS api to set them back to the pre-upgrade release levels.Pay special attention to estimate_percent and method_opt.

 2. For data dictionary objects, gather full statistics once, without histograms. Do not re-gather until a new major upgrade requires so, or the workload changes. Re-gather if you make massive schema changes to the environment (e.g., add a large number of new database users or drop and create a large number of objects, plug-in new tablespaces into the database, etc).The reason to gather with no histograms is to improve plan stability on SQL which access such objects.

3. For fixed objects, gather once right after normal system load. Do not re-gather until a new major upgrade requires so, or the workload changes. To reduce the possibility of a database hang, only gather fixed object statistics at the end of a work day, or when activity on the database has been reduced. Never gather fixed object statistics when the database is completely cold.

4. For system performance stats (or system statistics), gather once with normal system load. Repeat only if system configuration or load changes significantly.Use workload API, and be sure to cover 2 to 3 hours of normal workload. For example: Start at 9 am and Stop 3 hrs later.

SOLUTION

Before upgrading from 9i/10g to 11g: 

1. Continue gathering CBO statistics as per your current procedures. 

2. You may want to make a full backup of your CBO statistics. Consider reviewing and using scripts coe_create_user_coecbostats.sql and coe_backup_cbo_stats.sql connected as SYS, or use standard DBMS_STATS APIs.

3. If pre-upgrade instance will be destroyed soon after the upgrade, make an export of pre-upgrade CBO statistics (or schema owner COECBOSTATS if coe scripts were used on prior step).

After upgrading from 9i/10g to 11g: 

1. If your application provides its own procedure to gather CBO statistics, discontinue immediately the execution of the job that performs an automatic gathering of CBO statistics:

• Connect as SYS and EXEC DBMS_AUTO_TASK_ADMIN.DISABLE('auto optimizer stats collection', NULL, NULL);

2. Create a fresh baseline of Data Dictionary CBO statistics by using script coe_gather_dictionary_stats.sql connected as SYS. Or use standard DBMS_STATS API. Be sure to specify a 100 sample size and no histograms.

3. Create right after normal system load a baseline of Fixed Objects CBO statistics by using script coe_gather_fixed_objects_stats.sql connected as SYS. Or use standard DBMS_STATS API. Be sure your system is warm.

4. Create a baseline of System statistics for the CBO by using scripts coe_gather_system_stats_start.sql and coe_gather_system_stats_stop.sql connected as SYS. Or use standard DBMS_STATS APIs with workload option and 2-3 hrs interval covering normal system load.

4.1 If using provided scripts, then during normal system load, execute first coe_gather_system_stats_start.sql, then wait two or three hours and execute second coe_gather_system_stats_stop.sql. This set will generate system statistics that depend on the system workload during the start and stop times. If your workload or hardware configuration changes over time, you will have to execute this set in the same manner after the system load/configuration has been implemented and during normal system utilization.After gathering system statistics perform a sanity check on them.

Scripts: (attached to this note)

These scripts are provided as a possible mechanism to perform the actions described in this note. They include a backup of the CBO statistics to be refreshed by the script.

1. coe_create_user_coecbostats.sql creates a new schema owner COECBOSTATS with one object (COE$_STATTAB). Table COE$_STATTAB is a repository to store persistent versions of CBO statistics for any of the four types: schema objects, data dictionary, fixed objects, and system performance. Execute connected as SYS. 

2. coe_backup_cbo_stats.sql can be used on 9i, 10g or 11g. It creates a backup of all four types of CBO statistics when executed on 10g/11g, and all three valid types on 9i (skipping fixed objects). Execute connected as SYS. 

3. coe_gather_dictionary_stats.sql generates corresponding CBO statistics using an estimated percentage of 100% (compute), no histograms, and cascades into all related indexes (gathers statistics on indexes). It makes a backup of these statistics before and after gathering. Execute connected as SYS.  It can be used on 9i, 10g or 11g.

4. coe_gather_fixed_objects_stats.sql generates corresponding CBO statistics. It makes a backup of these statistics before and after gathering. Execute connected as SYS. It can be used on 10g or 11g.

5. coe_gather_system_stats_nw.sql gathers system statistics that are independent of the workload. Execute connected as SYS. It can be used on 10g or 11g.

6. coe_gather_system_stats_start.sql is used in combination with coe_gather_system_stats_stop.sql. They start and stop the gathering of system statistics that are dependent upon the system workload and configuration. They must be executed during a normal system utilization window. Execute connected as SYS. It can be used on 10g or 11g.

7. coe_gather_system_stats_stop.sql is used in combination with coe_gather_system_stats_start.sql. They start and stop the gathering of system statistics that are dependent upon the system workload and configuration. They must be executed during a normal system utilization window. Execute connected as SYS. It can be used on 10g or 11g.

Words of Caution: 

1. If your application does not provide specific instructions regarding CBO statistics gathering and you were using default functionality for DBMS_STATS on your pre-upgrade release (9i/10g), you may want to take the conservative approach of preserving the "pre-upgrade release" functionality initially, and gradually incorporate the new defaults for DBMS_STATS on the post-upgrade release (10g/11g). This is specially important from 9i to 10g or 11g. Please see also:

2. ESTIMATE_PERCENT had a default of 100% for sample size on 9i, while 10g and 11g defaults this parameter to DBMS_STATS.AUTO_SAMPLE_SIZE, which derives a very small estimate percentage (sample size). Small sample sizes are known to produce poor number of distinct values NDV on columns with skewed data (which are common), thus generate sub-optimal plans. Use then an estimate sample size of 100% on 10g if your window maintenance can afford it, even if that means gather statistics less often. If 100% were not feasible, try using at least an estimate of 30%. On 11g the default value of DBMS_STATS.AUTO_SAMPLE_SIZE gathers stats with a large sample size, so using the default value is a better approach. Just be aware of:

3. METHOD_OPT has a default of "FOR ALL COLUMNS SIZE 1" on 9i, which basically meant NO HISTOGRAMS. 10g and 11g default to AUTO, which means DBMS_STATS decides in which columns a histogram may help to produce a better plan. It is known that in some cases, the effect of a histogram is adverse to the generation of a better plan (specially in the presence of bind variables combined with small or AUTO sample sizes). Again, you may want to initially set this parameter to its pre-upgrade release value, and later adjust to your post-upgrade release default value.

4. In summary, to avoid causing changes to the execution plan, try to keep the statistics gathering process as close as possible to your pre-upgrade release, at least until after the upgrade is complete and stable, then adjust gradually to the features provided by the new release.

REFERENCES