11g How Manual SQL Plan Loading Works Oracle 11g New Features Tips by Donald BurlesonJune 29, 2015

Oracle 11g New Features Tips

By default, Automatic SQL Plan Capture is disabled in 11g. Therefore, feed the SQL Plan Baselines manually if this feature needs to be used. There are multiple ways how to manage SQL Plan Baselines manually. All of them utilize the packages dbms_spm and possibly dbms_sqltune.   Also see dba_sql_plan_baselines tips.

The SQL plans can be loaded manually into SQL Plan Baselines from three different sources:

• SQL tuning sets

• AWR snapshots

• Cursor cache

It has been shown already how the CONFIGURE procedure of the package dbms_spm can be used to adjust the size and retention policy for the SMB. This package is the command line interface for the SMB and, thus the SQL plan Baselines, and it provides full control for Manual SQL Plan Management.

It contains procedures and functions for loading execution plans from the various sources and for packing them into staging tables as well unpacking them again. The package can be used to DROP and ALTER plan baselines.

Up to now, SQL plan baselines were shown which were not fixed. This means that the optimizer uses costing mechanisms to evaluate plans. With Manual SQL Management, plans can be fixed in baselines, thereby forcing the optimizer to consider only those plans from a plan baseline which have been manually marked as possible candidates.

There might be reasons why a guarantee may be desired that states the execution plans for certain SQL do not change, i.e. Plan Stability. For example, a new application has been developed using Oracle database 11g which has been tested thoroughly and is now ready to be deployed to the customers. Since it cannot be known how the application would behave in the environment at the customer's site, the baselines could be fixed for the SQL and ship them together with the application where the well tuned plans could be fed into the customer's system. This way, encountering regression can be avoided in the well tuned application.

Now force the optimizer to use the one and only chosen plan:

PL/SQL procedure successfully completed.

Using dbms_spm.alter_sql_plan_baseline, the plan has been changed to ACCEPTED and, therefore, added it to the plan baseline.  So how can it be achieved by looking at the baseline from above again?

LUTZ AS SYSDBA @ racdb11g1 SQL> SELECT sql_handle,
                                       plan_name,
                                       enabled,
                                       accepted,
                                       fixed
                                 FROM  dba_sql_plan_baselines
                                 WHERE LOWER(sql_text) LIKE '%hr.employees%';

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SYS_SQL_818c1879b000a439       SYS_SQL_PLAN_b000a4397d478871  YES YES NO
SYS_SQL_818c1879b000a439       SYS_SQL_PLAN_b000a439c0e983c6  YES YES NO
SYS_SQL_818c1879b000a439       SYS_SQL_PLAN_b000a439cf314e9e  YES NO  NO

There are three plans in the statement history for the SQL handle SYS_SQL_818c1879b000a439. All three are ENABLED, two are additionally ACCEPTED. Those two are part of the plan baseline for the statement. The optimizer will choose any of those two. None of the plans is FIXED. This means that the plan baseline is NOT FIXED as well and plans can still evolve.

This plan baseline can also be forced to be static by fixing a plan in it:

LUTZ AS SYSDBA@racdb11g1 SQL>  var my_var number

LUTZ AS SYSDBA@racdb11g1 SQL>exec :my_var := dbms_spm.alter_sql_plan_baseline -

> (sql_handle => 'SYS_SQL_818c1879b000a439', -
> plan_name => 'SYS_SQL_PLAN_b000a439c0e983c6', -
> attribute_name => 'FIXED', attribute_value => 'YES')

PL/SQL procedure successfully completed.

The function has changed one plan in the baseline:

LUTZ AS SYSDBA @ racdb11g1 SQL> print my_var

    MY_VAR
----------
         1

LUTZ AS SYSDBA @ racdb11g1 SQL>  SELECT  sql_handle,
                                        plan_name,
                                        enabled,
                                        accepted,
                                        fixed

                                FROM dba_sql_plan_baselines

 WHERE LOWER(sql_text) LIKE '%hr.employees%';

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SYS_SQL_818c1879b000a439       SYS_SQL_PLAN_b000a4397d478871  YES YES NO
SYS_SQL_818c1879b000a439       SYS_SQL_PLAN_b000a439c0e983c6  YES YES YES
SYS_SQL_818c1879b000a439       SYS_SQL_PLAN_b000a439cf314e9e  YES NO  NO

Now this plan is fixed and the baseline is fixed as well. If none of the ACCEPTED plans in the plan baseline is reproducible, the optimizer will pick the best cost plan from the list of the plans with status only ENABLED. The optimizer will now advance this one fixed plan for execution and only choose a non-fixed but accepted plan if none of the fixed plans are reproducible. Plan evolution does not apply for fixed plan baselines. A plan baseline is fixed as soon as it contains at least one plan with the attribute FIXED=YES.

Now pack this one plan from the baseline into a staging table and ship it to another system with export. First, create a staging table MY_STG_TAB in the tablespace USERS owned by the user SYSTEM:

LUTZ AS SYSDBA @ racdb11g1 SQL> exec dbms_spm.create_stgtab_baseline (-

> table_name => 'MY_STG_TAB', table_owner =>'SYSTEM' , tablespace_name => 'USERS')

PL/SQL procedure successfully completed.

Here is the information that will be packed into the staging table:

LUTZ AS SYSDBA @ racdb11g1 SQL> desc system.MY_STG_TAB

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 VERSION                                            NUMBER
 SIGNATURE                                          NUMBER
 SQL_HANDLE                                         VARCHAR2(30)
 OBJ_NAME                                           VARCHAR2(30)
 OBJ_TYPE                                           VARCHAR2(30)
 PLAN_ID                                            NUMBER
 SQL_TEXT                                           CLOB
 CREATOR                                            VARCHAR2(30)
 ORIGIN                                             VARCHAR2(30)
 DESCRIPTION                                        VARCHAR2(500)
 DB_VERSION                                         VARCHAR2(64)
 CREATED                                            TIMESTAMP(6)
 LAST_MODIFIED                                      TIMESTAMP(6)
 LAST_EXECUTED                                      TIMESTAMP(6)
 LAST_VERIFIED                                      TIMESTAMP(6)
 STATUS                                             NUMBER
 OPTIMIZER_COST                                     NUMBER
 MODULE                                             VARCHAR2(48)
 ACTION                                             VARCHAR2(32)
 EXECUTIONS                                         NUMBER
 ELAPSED_TIME                                       NUMBER
 CPU_TIME                                           NUMBER
 BUFFER_GETS                                        NUMBER
 DISK_READS                                         NUMBER
 DIRECT_WRITES                                      NUMBER
 ROWS_PROCESSED                                     NUMBER
 FETCHES                                            NUMBER
 END_OF_FETCH_COUNT                                 NUMBER
 CATEGORY                                           VARCHAR2(30)
 SQLFLAGS                                           NUMBER
 TASK_ID                                            NUMBER
 TASK_EXEC_NAME                                     VARCHAR2(30)
 TASK_OBJ_ID                                        NUMBER
 TASK_FND_ID                                        NUMBER
 TASK_REC_ID                                        NUMBER
 INUSE_FEATURES                                     NUMBER
 PARSE_CPU_TIME                                     NUMBER
 PRIORITY                                           NUMBER
 OPTIMIZER_ENV                                      RAW(2000)
 BIND_DATA                                          RAW(2000)
 PARSING_SCHEMA_NAME                                VARCHAR2(30)
 COMP_DATA                                          CLOB

Now pack the one and only plan into the staging table:

LUTZ AS SYSDBA @ racdb11g1 SQL> exec :my_var:= dbms_spm.pack_stgtab_baseline (-

> table_name => 'MY_STG_TAB', table_owner =>'SYSTEM' , -
> sql_handle => 'SYS_SQL_818c1879b000a439' ,  -
> plan_name => 'SYS_SQL_PLAN_b000a439c0e983c6', fixed => 'YES')

PL/SQL procedure successfully completed.

LUTZ AS SYSDBA @ racdb11g1 SQL> print my_var

    MY_VAR
----------
         1

This is what is in the staging table now:

LUTZ AS SYSDBA @ racdb11g1 SQL> select SQL_HANDLE,  OBJ_NAME,  SQL_TEXT from  system.my_stg_tab;

SQL_HANDLE                OBJ_NAME                      SQL_TEXT
------------------------- ---------- ------------------ -----------------------SYS_SQL_818c1879b000a439 SYS_SQL_PLAN_b000a439c0e983c6  select last_name, salary from hr.employees where employee_id=100

Now import the staging table using a database link with impdp into another database where the SQL plan baseline is unloaded:

SYSTEM  @ prod11g1 SQL> CREATE DATABASE LINK imp_link
  2  CONNECT TO system IDENTIFIED BY oracle1
  3  USING 'racdb11g1';

[oracle@rac11a-pub ~]$ impdp system/oracle1 tables='MY_STG_TAB' network_link=imp_link directory=imp_dir logfile=stg_imp.log

Import: Release 11.1.0.6.0 - Production on Wednesday, 13 February, 2015 0:47:57
Copyright (c) 2003, 2015, Oracle.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/******** tables=MY_STG_TAB network_link=imp_link directory=imp_dir logfile=stg_imp.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "SYSTEM"."MY_STG_TAB"                            1 rows
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at 00:48:52

Look at the imported staging table now:

LUTZ AS SYSDBA @ prod11g1 SQL> SELECT sql_handle,
                                     obj_name,
                                     sql_text,
                                     db_version,

                                     origin
                               FROM  system.my_stg_tab;

SQL_HANDLE                OBJ_NAME                   SQL_TEXT DB_VERSION ORIGIN
-----------------------   -----------------------     ------------------  ------------
SYS_SQL_818c1879b000a439  SYS_SQL_PLAN_b000a439c0e983c6
select last_name, salary from hr.employees where employee_id=100
11.1.0.6.0 AUTO-CAPTURE

Check the SQL plan baseline in the target system:

LUTZ AS SYSDBA @ prod11g1 SQL> SELECT sql_handle,
                                     plan_name,
                                     enabled,
                                     accepted,
                                     fixed
                               FROM dba_sql_plan_baselines
                               WHERE LOWER(sql_text) LIKE '%hr.employees%'

LUTZ AS SYSDBA @ prod11g1 SQL> /
no rows selected

Of course there is nothing yet, so unpack the staging table first:

LUTZ AS SYSDBA @ prod11g1 SQL> var new_var number

SYS AS SYSDBA@prod11g1 SQL> exec :new_var:= dbms_spm.unpack_stgtab_baseline (-
>  table_name => 'MY_STG_TAB', table_owner =>'SYSTEM' , -
>                 sql_handle => 'SYS_SQL_818c1879b000a439',-
>                 plan_name => 'SYS_SQL_PLAN_b000a439c0e983c6', -
>                 fixed => 'YES')

PL/SQL procedure successfully completed.

Check again and here it is!

LUTZ AS SYSDBA @ prod11g1 SQL> select  sql_handle, plan_name, enabled, accepted, fixed

  2  FROM dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SYS_SQL_818c1879b000a439       SYS_SQL_PLAN_b000a439c0e983c6  YES YES YES

This has demonstrated how to export single plans from a SQL plan baseline.

NOTE:  It is also possible to pack all or selected plans sourced from the cursor cache or SQL tuning sets into a staging table for export/import.

       

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.