How to: Manage CBO Statistics During an Upgrade from 10g or 9i into 11g/12c (Doc ID 465787.1)

Last updated on MAY 02, 2023

Applies to:

Goal

The goal of this document is to provide guidance regarding the management of existing and new CBO stats during an upgrade to 11g/12c 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/12c.

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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.