Recording Access Path Information Prior to an upgrade to 10g or 11g (Doc ID 466350.1)

Last updated on SEPTEMBER 15, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 to 11.1.0.6 [Release 9.2 to 11.1]
Information in this document applies to any platform.
Checked for relevance on 09-SEP-2010


Goal

Major release upgrades like going from 9i to 10g/11g or from 10g to 11g, include several and important improvements to the Cost-based Optimizer (CBO). The CBO is the code responsible for the generation of execution plans (typically captured using the EXPLAIN PLAN command).

After an upgrade, the CBO is expected to generate the same or a better performing execution plan for most SQL statements. Still it is inevitable that the CBO will generate a sub-optimal plan for some SQL statements on the new release compared to the prior. In cases like this, having the execution plan for the SQL from the prior release is crucial. It becomes an invaluable resource to expedite the diagnostics of the sub-optimal plan that is generated under the new release.

Preserving the execution plan of every SQL statement from the pre-upgrade instance is not practical when the application using the database generates millions of distinct SQL statements (complex applications do). Therefore, it is important to somehow reduce the footprint of the SQL statements and their corresponding explain plans to be preserved.

If a SQL statement requires many resources on the pre-upgrade instance, chances are that if this SQL generates a sub-optimal plan in the post-release instance, its impact in the overall system performance will be more relevant than that of a SQL that required few resources initially.

This note provides guidance to use Statspack (or AWR) as a repository to collect on the pre-upgrade instance, a subset of expensive SQL and their corresponding explain plans. This repository can be latter used as a diagnostic tool during the root cause analysis of sub-optimal plans generated in the post-upgrade instance.

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms