My Oracle Support Banner

How to Load SQL Plans into SQL Plan Management (SPM) from the Automatic Workload Repository (AWR) (Doc ID 789888.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.4 to 12.1.0.1 [Release 10.2 to 12.1]
Information in this document applies to any platform.

Goal

The goal of the document is to provide steps for loading a sql plan into sql plan baseline from AWR.  Please remember that license is required to use Oracle Diagnostics Pack:

 

NOTE: Oracle Diagnostics Pack (and Oracle Tuning Pack) is available with Enterprise Edition ONLY.

For further details of pack licensing see:

Oracle® Database Licensing Information
12c Release 1 (12.1)
Part number E17614-08
Chapter 1 1 Oracle Database Editions
Feature Availability by Edition
http://docs.oracle.com/cd/E16655_01/license.121/e17614/editions.htm#DBLIC116

  

Solution

To view full details, sign in with your My Oracle Support account.

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


In this Document
Goal
Solution
 1. Populate the STS using DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY
 Example 1
 Example 2
 Example 3
 Verify how many sqls got loaded in the STS.
 Verify the sql statements and its sql_id in the STS
 Verify the execution Plan of a SQL_ID in the STS for an user sql
 Verify the Plan baseline to check how many plans before
 2. Load the Sql Plan Baseline from STS
 Verify the Plan baseline to check how many plans in plan baseline
 Patches
References

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