10 August 2011

Why Upgrade to Oracle 11gR2 now!

I received an email on monday about the question why one should migrate to Oracle Database 11gR2. Well I could think of some reasons, but there was also a short video message from the Oracle Vice President Mister Tom Kyte himself.  although several years younger then myself, lots and lots of more success with selling Oracle and Oracle products. Main point of his selling story why should you migrate to Oracle 11gR2 now are:

1) SQL Plan Management (SPM)
A SQL plan baseline contains one or more accepted plans, each of which contains the
following information:

Set of hints
Plan hash value
Plan-related information

The plan history is the set of plans, both accepted and not accepted, that the optimizer generates for a SQL statement over time. Because only accepted plans are in the SQL plan baseline, the plans in the baseline form a susbset of the plan history. For example, after the optimizer generates the first acceptable plan for a SQL plan baseline, subsequent plans are part of the plan history but not part of the plan baseline. The process of adding plans to a SQL plan baseline is plan evolution. To be eligible to be evolved, a plan must be enabled for use by the optimizer. Figure below shows a single SELECT statement that has two accepted plans in its SQL plan baseline. The SQL plan history includes two other plans for the statement that have not been proven to perform well.

The SQL management base (SMB), which is part of the data dictionary, stores the SQLplan baselines and plan history in the SYSAUX tablespace. The SMB also contains SQL profiles. The SMB uses automatic space management.

Managing SQL plan baselines involves the following phases:

Capturing SQL Plan Baselines
Selecting SQL Plan Baselines
Evolving SQL Plan Baselines

2) Real Application Testing (in short hand RAT, but that sounds insulting that is why we change that to R.A.T.).

This is a replay of your current Production Database workload in the new Production Database Oracle 11gR2. (Please upgrade now).

3) Support for older versions will end eventually for Oracle 9i soon.

See table below for yourself how urgent it is for you to move to Oracle 11gR2, please contact DBA Consulting for a quote for migration of your Database to Oracle 11gR2 (info@dbaconsulting.nl):

4)  The Support workbench in Oracle 11gR2.

The Support Workbench workflow consists of the following steps:

1. Create an incident in the database automatically based on the first occurrence of a
2. Alert the DBA of the failure and run health checks in the areas where the failure was reported.
3. If it is a known issue, then recommend and apply patch to solve the problem.
4. Otherwise, package up incidents and relevant configuration information and upload toOracle Support and run repair advisors to recover from failure.

There are many different kinds of problems that can occur in an Oracle Database and the right remedy for each problem may be different. The Support Workbench has extensive workflows that guide the user to take action that is appropriate for the problem encountered.

I also run in to the following blog entry on an Oracle specialist site, which claims that Stored Outlines no longer exist. This is not entirely true!

Stored outlines (Oracle Database 10g to 11g Change)
posted Sep 24, 2010 9:21 AM by Sachchida Ojha

Stored outlines are deprecated in Oracle Database 11g.  Oracle highly recommends the use of SQL plan baselines instead of the stored outlines.  With Oracle Database 11g using the SQL Plan Management (SPM) the optimizer automatically manages plans and ensures that only verified or known plans are used.   SQL Plan Management allows controlled plan evolution by only using a new plan after it has been verified to perform better than the current plan. You should also use SQL Plan Management as part of your upgrade strategy. Please see the best practices section of this Upgrade Companion for more information.

However Stored Outlines are still here!

They are used in new SQl Plan Management (SPM) and Real Application Testing (R.A.T.) and automatic tuning. The Stored Outlines are used to Migrating Stored Outlines to SQL Plan Baselines

The steps in Migrating Stored Outlines are: 

Overview of Stored Outline Migration
Preparing for Stored Outline Migration
Migrating Outlines to Utilize SQL Plan Management Features
Migrating Outlines to Preserve Stored Outline Behavior
Performing Follow-Up Tasks After Stored Outline Migration

Overview of Stored Outline Migration
A stored outline is a set of hints for a SQL statement. The hints direct the optimizer to choose a specific plan for the statement. A stored outline is a legacy technique for
providing plan stability. Stored outline migration is the user-initiated process of converting stored outlines to SQL plan baselines. A SQL plan baseline is a set of plans proven to provide good performance.

Purpose of Stored Outline Migration
How Stored Outline Migration Works
User Interface for Stored Outline Migration
Basic Steps in Stored Outline Migration

Purpose of Stored Outline Migration
This section assumes that you rely on stored outlines to maintain plan stability and prevent performance regressions. The goal of this section is to provide a convenient method to safely migrate from stored outlines to SQL plan baselines. After the migration, you can maintain the same plan stability that you had using stored outlines while being able to utilize the more advanced features provided by the SQL Plan Management framework.

Specifically, the section explains how to address the following problems:

Stored outlines cannot automatically evolve over time. Consequently, a stored outline may be good when you create it, but become a bad plan after a database change, leading to performance degradation.
Hints in a stored outline can become invalid, for example, an index hint on a dropped index. In such cases, the database still uses the outlines but excludes the invalid hints, producing a plan that is often worse than the original plan or the current best-cost plan generated by the optimizer.
For a SQL statement, the optimizer can only choose the plan defined in the stored outline in the currently specified category. The optimizer cannot choose from other stored outlines in different categories or the current cost-based plan even if they improve performance.
Stored outlines are a reactive tuning technique, which means that you only use a stored outline to address a performance problem after it has occurred. For example, you may implement a stored outline to correct the plan of a SQL statement that became high-load. In this case, you used stored outlines instead of proactively tuning the statement before it became high-load.

The stored outline migration PL/SQL API helps solve the preceding problems in the following ways:
SQL plan baselines enable the optimizer to use the same good plan and allow this plan to evolve over time.
For a specified SQL statement, you can add new plans as SQL plan baselines after they are verified not to cause performance regressions.
SQL plan baselines prevent plans from going bad because of invalid hints. If hints stored in a plan baseline become invalid, then the plan may not be reproducible by the optimizer. In this case, the optimizer selects an alternative reproducible plan baseline or the current best-cost plan generated by optimizer.
For a specific SQL statement, the database can maintain multiple plan baselines. The optimizer can choose from a set of good plans for a specific SQL statement instead of being restricted to a single plan per category, as required by stored outlines.

How Stored Outline Migration Works

This section explains how the database migrates stored outlines to SQL plan baselines. This information is important for performing the task of migrating stored outlines. Stages of Stored Outline Migration The following graphic shows the main stages in stored outline migration:

The migration process has the following stages:

1. The user invokes a function that specifies which outlines should be migrated.

2. The database processes the outlines as follows:
a. The database copies information in the outline needed by the plan baseline.
The database copies it directly or calculates it based on information in the
outline. For example, the text of the SQL statement exists in both schemas, sothe database can copy the text from outline to baseline.
b. The database reparses the hints to obtain information not in the outline.
The plan hash value and plan cost cannot be derived from the existing
information in the outline, which necessitates reparsing the hints.
c. The database creates the baselines.

3. The database obtains missing information when it chooses the SQL plan baselinefor the first time to execute the same SQL statement.

The compilation environment and execution statistics are only available duringexecution when the plan baseline is parsed and compiled. The migration is complete only after the preceding phases complete. Outline Categories and Baseline Modules An outline is a set of hints, whereas a SQL plan baseline is a set of plans. Because they are different technologies, some functionality of outlines does not map exactly to functionality of baselines. For example, a single SQL statement can have multiple outlines, each of which is in a different outline category, but the only category that currently exists for baselines is DEFAULT. The equivalent of a category for an outline is a module for a SQL plan baseline.

Table below explains how outline categories map to modules.

When migrating stored outlines to SQL plan baselines, Oracle Database maps every outline category to a SQL plan baseline module with the same name. As shown in the following diagram, the outline category OLTP is mapped to the baseline module OLTP. After migration, DEFAULT is a super-category that contains all SQL plan baselines.

You can use the DBMS_SPM package to perform the stored outline migration.
The Table below  describes the relevant functions in this package.

You can control stored outline and plan baseline behavior with initialization and session parameters. Table below describes the relevant parameters.

You can use database views to access information relating to stored outline migration. Table below describes the following main views.

Basic Steps in Stored Outline Migration

This section explains the basic steps in using the PL/SQL API to perform stored outline migration. The basic steps are as follows:

1. Prepare for stored outline migration.

2. Do one of the following:
Migrate to baselines to utilize SQL Plan Management features.
Migrate to baselines while exactly preserving the behavior of the stored

3. Perform post-migration confirmation and cleanup.


0 reacties:

Post a Comment