Problem

You have a poorly performing query, and you want to get advice from the SQL Tuning Advisor. If the SQL Tuning Advisor recommends a SQL profile, then you want to accept the SQL profile (for the poorly performing query).

Solution

Run the SQL Tuning Advisor for the problem query. Keep in mind that the SQL Tuning Advisor may or may not recommend a SQL profile as a solution for performance issues. To run the SQL Tuning Advisor manually, perform the following steps:

1. Use DBMS_SQLTUNE to create a tuning task.
2. Execute the tuning task.
3. Generate the tuning advice report.
4. If SQL profile is part of the tuning advice output, then create and accept it.

The following example follows the prior steps. In this scenario, the SQL Tuning Advisor recommends that a SQL profile be applied to the given query.

DECLARE
  tune_sql  CLOB;
  tune_task VARCHAR2(30);
BEGIN
  tune_sql := 'select count(*) from emp';
  tune_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_text   => tune_sql
   ,user_name  => 'MV_MAINT'
   ,scope      => 'COMPREHENSIVE'
   ,time_limit => 60
   ,task_name  => 'TUNE1'
   ,description => 'Calling SQL Tuning Advisor for one statement'
);
END;
/

SQL> @sqltune.sql

SQL> exec dbms_sqltune.execute_tuning_task(task_name=>'TUNE1'),

set long 10000
set longchunksize 10000
set lines 132
set pages 200
select dbms_sqltune.report_tuning_task('TUNE1') from dual;

Recommendation (estimated benefit: 86.11%)
 ------------------------------------------
  - Consider accepting the recommended SQL profile to use parallel execution
    for this statement.
    execute dbms_sqltune.accept_sql_profile(task_name => 'TUNE1', task_owner
            => 'SYS', replace => TRUE, profile_type =>
            DBMS_SQLTUNE.PX_PROFILE);
-------------------------------------------
  Executing this query parallel with DOP 8 will improve its response time
  86.11% over the original plan. However, there is some cost in enabling
  parallel execution...

begin
-- This is the code from the SQL Tuning Advisor
dbms_sqltune.accept_sql_profile(
    task_name => 'TUNE1',
    task_owner => 'SYS',
    replace => TRUE,
    profile_type => DBMS_SQLTUNE.PX_PROFILE);
--
end;
/

SQL> exec DBMS_SQLTUNE.DROP_TUNING_TASK(task_name=>'TUNE1'),

How It Works

The only Oracle-supported method for creating a SQL profile is to run the SQL Tuning Advisor and, if recommended, create a SQL profile using the Tuning Advisor’s output. In other words, the SQL Tuning Advisor determines if a SQL profile will help and, if so, generates the code required to create a SQL profile for a given query.

Keep in mind that a SQL profile is not a silver bullet for improving query performance. It may be that the SQL Tuning Advisor recommends a SQL profile for a query and it does nothing to improve performance.  We recommend that you observe the performance characteristics of the query before and after the SQL profile has been created. If there is a significant performance gain, then keep the SQL profile in place, otherwise drop it (see Recipe 12-8 for details on how to drop a SQL profile).

The “Solution” section detailed how to manually run the SQL Tuning Advisor. Keep in mind that with Oracle Database 11g and higher, this tuning task job automatically runs on a regularly scheduled basis. The automatic tuning task will oftentimes recommend the application of a SQL profile for a poorly performing query. See Chapter 11 for details on automatic SQL tuning features. You can review the output of the automatic tuning job via this query:

SQL> SELECT DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK FROM DUAL;

We recommend that you review the output of the automatic tuning job on a regular basis. If a SQL profile is recommended then consider creating it with the code provided in the output from the tuning task.

As noted in the “Solution” section, a SQL profile is created and accepted via the DBMS_SQLTUNE.ACCEPT_SQL_PROFILE procedure. There are many options available when using this procedure (see Table 12-2 for details).

The FORCE_MATCH parameter of ACCEPT_SQL_PROFILE requires further explanation. Recall that a SQL profile is associated with a SQL statement. The SQL statement is identified via a hash function (SQL signature). The hash function is generated after converting the SQL text and removing extra white space. When setting FORCE_MATCH to TRUE, this additionally normalizes literal values into bind values. This is similar to the algorithm generated via the FORCE option of the CURSOR_SHARING database initialization parameter.

For example, with FORCE_MATCH set to TRUE, the following two SQL statements will generate the same SQL signature:

SQL> select value from my_table where value = 'AA';
SQL> select value from my_table where value = 'bb';

This allows SQL statements that use literal values to share the same SQL profile. If there is a combination of literal values and bind variables in a SQL statement, then literal values are not normalized.

Problem

You’ve created a SQL profile for a query and wonder if the SQL profile is being used by the optimizer when the query executes.

Solution

Query the SQL_PROFILE column of V$SQL to display any SQL queries currently in memory that have used a SQL profile. For example:

select sql_id, child_number, sql_profile
from v$sql
where sql_profile is not null;

To view historical high resource-consuming SQL statements that have used a SQL profile, then query the DBA_HIST_SQLSTAT view:

select sql_id, sql_profile
from dba_hist_sqlstat
where sql_profile is not null;

How It Works

When a SQL query executes, the query optimizer will detect whether there is an associated SQL profile. If so, the optimizer can choose to use the SQL profile to adjust the execution plan for the query. If the optimizer does use a SQL profile, it will record that in the SQL_PROFILE column in the V$SQL view.

Also recall that the AWR is a historical repository that contains information on high resource-consuming SQL statements. If a high resource usage SQL statement is recorded in the AWR and if it was using a SQL profile, that information is noted in the SQL_PROFILE column of DBA_HIST_SQLSTAT.

If you are manually tuning a query and are using tools such as Autotrace or the DBMS_XPLAN package, then the use of a SQL profile is displayed in the output of these tools. For example:

SQL> set autotrace trace explain
SQL> <run the query in question>

If a SQL profile is being used, then you should see this in the output:

Note
-----
   - SQL profile "SYS_SQLPROF_013f199ac5990000" used for this statement

The same type of SQL profile information is displayed in the output of DBMS_XPLAN.DISPLAY_CURSOR and DBMS_XPLAN.DISPLAY.

Problem

You realize that the Automatic SQL Tuning job runs on a daily basis (in Oracle Database 11g or higher). You determine that the automatic tuning job generates reasonable SQL profiles for problematic queries and now want to enable the automatic acceptance of SQL profiles generated by the automatic tuning job.

Solution

Use the DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER procedure to enable the automatic acceptance of SQL profiles recommended by the Automatic SQL Tuning task—for example:

BEGIN
DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(
  parameter => 'ACCEPT_SQL_PROFILES', value => 'TRUE'),
END;
/

If you want to disable the automatic acceptance of SQL profiles, then do so as follows (using the FALSE parameter):

BEGIN
DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(
  parameter => 'ACCEPT_SQL_PROFILES', value => 'FALSE'),
END;
/

How It Works

In Oracle Database 11g or higher, an automatically configured job runs the SQL Tuning Advisor on a periodic basis (determined by a configured maintenance window). This job identifies high resource-consuming SQL statements from performance metrics contained in the AWR. When the automatic tuning job runs, it will occasionally recommend that a SQL profile be implemented for a poorly performing SQL statement. If you have configured the automatic acceptance via DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER, and if the SQL Tuning Advisor deems the query’s performance will significantly improve with the SQL Profile, then it will be automatically implemented. You can report on the details of the automatic tuning task configuration via this query:

SELECT
 parameter_name
,parameter_value
FROM dba_advisor_parameters
WHERE task_name = 'SYS_AUTO_SQL_TUNING_TASK'
AND  parameter_name
  IN ('ACCEPT_SQL_PROFILES',
      'MAX_SQL_PROFILES_PER_EXEC',
      'MAX_AUTO_SQL_PROFILES',
      'EXECUTION_DAYS_TO_EXPIRE'),

Here is some sample output:

PARAMETER_NAME            PARAMETER_VALUE
------------------------- --------------------
EXECUTION_DAYS_TO_EXPIRE  30
ACCEPT_SQL_PROFILES       TRUE
MAX_SQL_PROFILES_PER_EXEC 20
MAX_AUTO_SQL_PROFILES     10000

You can also use Enterprise Manager to configure the automatic acceptance of SQL profiles. From the Performance tab, click on Advisors Home, then select SQL Advisors. Next click on Automatic SQL Tuning Results. You should see a page similar to Figure 12-2.

From this screen, you can configure features such as the automatic implementation of SQL profiles, maximum time for a tuning session, and so on.

Problem

You have created and accepted several SQL profiles and now want to view information related to these database objects.

Solution

Use the DBA_SQL_PROFILES view to display information about SQL profiles. Here’s an example that selects the most interesting columns:

SQL> select name, type, status, sql_text from dba_sql_profiles;

Here is a snippet of the output:

NAME                           TYPE    STATUS   SQL_TEXT
------------------------------ ------- -------- ------------------------------
SYS_SQLPROF_012eda58a1be0001   MANUAL  ENABLED  SELECT ecm_snapshot_id AS id...
SYS_SQLPROF_012ea20305980000   MANUAL  ENABLED  SELECT *  FROM inv_maint...
SYS_SQLPROF_012edf0316930003   MANUAL  ENABLED  SELECT /* + parallel(mgmt_db_f...

For this database, there are several manually enabled SQL profiles (as shown in the prior output).

How It Works

Recall that a SQL profile contains improvements to existing statistics. The DBA_SQL_PROFILES view is the best source for viewing the SQL profile name, attributes, and associated SQL text.

To view the internal SQL profile hint-related information, you can additionally query the DBMSHSXP_SQL_PROFILE_ATTR view—for example:

SELECT
 a.name
,b.comp_data
FROM dba_sql_profiles          a
    ,dbmshsxp_sql_profile_attr b
WHERE a.name = b.profile_name;

Here is some sample output:

SYS_SQLPROF_0130520c90dc0002
<outline_data><hint><![CDATA[OPT_ESTIMATE(@"SEL$2",
NLJ_INDEX_SCAN, "FS"@"SEL$2", ("MAP"@"SEL$2"), "DB_FEAT_OPT_112_SUM_MV_IDX3",
SCALE_ROWS=0.3369001041)]]></h

The prior output gives you an indication of the types of hints within a SQL profile. This information is used by the optimizer to better estimate the cardinality of each execution step. This data allows the optimizer to make better decisions when generating an execution plan.

You can also view this internal SQL profile information by querying the SQLOBJ$ and SQLOBJ$DATA views. The data in these views is in XML format, and therefore you must format the output with Oracle XML functions when querying—for example:

SELECT
  extractvalue(value(a), '.') sqlprofile_hints
FROM sqlobj$     o
    ,sqlobj$data d
    ,table(xmlsequence(extract(xmltype(d.comp_data),'/outline_data/hint'))) a
WHERE o.name     = '&profile_name'
AND   o. plan_id = d.plan_id
AND   o.signature = d.signature
AND   o.category = d.category
AND   o.obj_type = d.obj_type;

Here is a small sample of the output:

OPT_ESTIMATE(@"SEL$EF0E05FC", INDEX_SCAN, "MGMT_TARGETS"@"SEL$4",
"MIDX3", SCALE_ROWS=50.68489486)
OPT_ESTIMATE(@"SEL$EF0E05FC", NLJ_INDEX_FILTER,
"MGMT_ECM_GEN_SNAPSHOT"@"SEL$3", ("MGMT_TARGETS"@"SEL$4"),
"IDX$$_1197C0001", SCALE_ROWS=0.4308705)

Again, these profile statistics don’t force the optimizer to use a certain execution plan. Rather, these statistics provide the optimizer with information that enables it to construct a more efficient execution plan.

Problem

You have a SQL profile that the SQL Tuning Advisor has recommended you enable for a SQL query. You want to test to see if the SQL profile helps performance but want to restrict the SQL profile being used to one session before enabling the profile for all sessions in the database. The idea being that you want to first verify the performance impact before allowing the optimizer to consider the SQL profile for all sessions running the given SQL query.

Solution

When you create a SQL profile, its category is assigned a value of DEFAULT (the category can be viewed by querying DBA_SQL_PROFILES). Also, when you start a SQL*Plus session by default the initialization parameter of SQTUNE_CATEOGRY has a value of DEFAULT. When you execute a SQL statement, the optimizer will check to see if there are any SQL profiles for the query, and also it will check to see if the category of the SQL profile matches the session’s (or system level) initialization parameter setting for SQLTUNE_CATEGORY. If those values match, then the optimizer will use the SQL profile as input when generating the execution plan.

Unless manually modified, the SQL profile category (value of DEFAULT) will always match the SQLTUNE_CATEGORY (value of DEFAULT). This means that if you alter a SQL profile’s category to something other than DEFAULT, then the optimizer will not consider using a SQL profile for query unless the session running the query has its SQLTUNE_CATEGORY modified to match the category of the SQL profile.

For example, say you modify the SQL profile to have a category of TEST1:

BEGIN
  DBMS_SQLTUNE.ALTER_SQL_PROFILE(
    name => 'SYS_SQLPROF_012eda58a1be0001',
    attribute_name => 'CATEGORY',
    value => 'TEST1'),
END;
/

Now the optimizer will only consider using the SQL profile for sessions executing the SQL query that have the initialization parameter of SQLTUNE_CATEGORY set to TEST1. You can set SQLTUNE_CATEGORY for a session as follows:

SQL> alter session set sqltune_category=TEST1;

You can verify the session setting of the SQLTUNE_CATEGORY via this statement:

SQL> show parameter sqltune_category

And you can verify the category of a SQL profile via this query:

SQL> select name, category from dba_sql_profiles;

How It Works

Setting a SQL profile’s category to something other than DEFAULT allows you to isolate a SQL profile’s use to only those sessions that have the SQLTUNE_CATEGORY set to match the category of the SQL profile. This allows you to test the impact of implementing a SQL profile and back it out quickly, simply by altering a session’s SQLTUNE_CATEGORY.

Problem

You have a test database and want to extract all of the SQL profiles from the test database and move them to a production database.

Solution

Listed next are the steps involved with transporting a SQL profile from one database to another:

1. Create a staging table.
2. Populate the staging table.
3. Move the table from the source database to the destination database (use Data Pump or a database link).
4. On the destination database, extract information from the staging table to populate the data dictionary with SQL profile information.

These steps are detailed in the following subsections.

BEGIN
  dbms_sqltune.create_stgtab_sqlprof(
    table_name => 'PROF_STAGE',
    schema_name => 'MV_MAINT' );
END;
/

BEGIN
  dbms_sqltune.pack_stgtab_sqlprof(
    profile_name => 'SYS_SQLPROF_012edf84806e0004',
    staging_table_name => 'PROF_STAGE',
    staging_schema_owner => 'MV_MAINT' );
END;
/

create database link source_db
connect to mv_maint
identified by foo
using 'source_db';

SQL> create table PROF_STAGE as select * from PROF_STAGE@source_db;

BEGIN
  DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(
    replace => TRUE,
    staging_table_name => 'PROF_STAGE'),
END;
/

How It Works

It’s fairly easy to copy SQL profiles from one database to another. You simply have to create a special table to hold the profile information, then populate the table, copy the table to the destination database, and lastly unpack the table’s contents. Table 12-3 describes all of the parameters for the profile packing procedure.

The DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF procedure takes the same parameters as the packing procedure with an additional REPLACE parameter. The REPLACE parameter specifies whether to replace profiles if they already exist (can be TRUE or FALSE).

Problem

You created a SQL profile for a query (see Recipe 12-1) and sometime later you added an index to one of the tables used by the query and subsequently noticed that the SQL_PROFILE column of V$SQL is not populated (see Recipe 12-2) when the query is executing. You therefore want to disable the SQL profile and verify that there is no performance decline.

Solution

First verify the name of the SQL profile that you want to disable:

SQL> select name, status from dba_sql_profiles;

Here’s a partial snippet of the output:

NAME                           STATUS
------------------------------ --------
SYS_SQLPROF_013f199ac5990000 ENABLED

Now use the DBMS_SQLTUNE.ALTER_SQL_PROFILE procedure to modify the status of the profile to DISABLED:

BEGIN
  DBMS_SQLTUNE.ALTER_SQL_PROFILE(
    name => 'SYS_SQLPROF_013f199ac5990000',
    attribute_name => 'STATUS',
    value => 'DISABLED'),
END;
/

How It Works

Disabling a SQL profile is fairly easy.  You may want to do this because you have determined the SQL profile is no longer being used by the optimizer for a query. Or you may want to ascertain the impact of a SQL profile by disabling it, running the query, re-enabling it, and running the query. You can enable a disabled SQL profile as shown:

BEGIN
  DBMS_SQLTUNE.ALTER_SQL_PROFILE(
    name => 'SYS_SQLPROF_013f199ac5990000',
    attribute_name => 'STATUS',
    value => 'ENABLED'),
END;
/

You can also disable a SQL profile from Enterprise Manager. From the Performance tab click on the SQL link, then click on SQL Plan Control. You should be presented with a screen similar to Figure 12-3.

From this screen, you can manage features such as enabling, disabling, changing the category, and dropping a SQL profile.

Problem

You have determined that a SQL profile is no longer being used by a SQL query (see Recipe 12-2) and want to drop it.

Solution

Execute the DBMS_SQLTUNE.DROP_SQL_PROFILE procedure to drop a SQL profile. Pass in the name of the SQL profile you want to drop—for example:

SQL> exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_012eda58a1be0001'),

How It Works

It’s fairly easy to drop a SQL profile. You might want to do this if you’re cleaning up a database or if you want to remove profiles from a testing environment. If you’re unsure of the SQL profile name, you can query DBA_SQL_PROFILES for more information.

If you want to drop all profiles in a database, you can use PL/SQL to loop through all profiles and drop them:

declare
  cursor c1 is select name from dba_sql_profiles;
begin
  for r1 in c1 loop
    dbms_sqltune.drop_sql_profile(r1.name);
  end loop;
end;
/

Problem

You’re planning a database upgrade. You know from past experience that sometimes after the database is upgraded SQL queries can perform more poorly. This is caused by the new version of the optimizer choosing a different execution plan that is less efficient than the plan used by the prior version of the optimizer. Therefore, to ensure that the optimizer consistently chooses the same execution plan before and after the upgrade, you want to establish a plan baseline for the query. You want to start by establishing plan baseline for a SQL query in memory.

Solution

The procedure for manually associating a plan baseline with a SQL statement is as follows:

1. Identify the SQL statement for which you want a plan baseline.
2. Provide an identifier such as the SQL_ID as input to the DBMS_SPM package to create a plan baseline for the SQL statement.

For example, suppose you have a SQL statement you’ve been working with such as the following:

SQL> select first_name from emp where emp_id = 100;

Now query the V$SQL view to determine the SQL_ID for the query:

select sql_id, plan_hash_value, sql_text
from v$sql
where sql_text like 'select first_name from emp where emp_id = 100'
and sql_text not like '%v$sql';

Here is a snippet of the output:

SQL_ID        PLAN_HASH_VALUE SQL_TEXT
------------- --------------- ---------------------------------------------------
dv73f7y69ny8z      3956160932 select first_name from emp where emp_id = 100

Now that the SQL_ID has been identified, use it as input to the DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE function to create a plan baseline for the given query—for example:

DECLARE
  plan1 PLS_INTEGER;
BEGIN
  plan1 := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => 'dv73f7y69ny8z'),
END;
/

The query now should have an entry in the DBA_SQL_PLAN_BASELINES view showing that it has an enabled plan baseline associated with it—for example:

SQL> select sql_handle, plan_name, sql_text from dba_sql_plan_baselines;

Here’s a small snippet of the output:

 SQL_HANDLE               PLAN_NAME                           SQL_TEXT
------------------------- ----------------------------------- ---------------
SQL_30c98e5c7bb6b95d      SQL_PLAN_31kcfbjxvdfaxd8a279cc      select first_name...

How It Works

Keep in mind that it’s possible that a single SQL statement can have more than one execution plan associated with it in memory. This can happen when the SQL executes multiple times and something in the environment changes that causes the optimizer to choose a different plan (like updated statistics, use of bind variables, changes with database initialization parameters, adding/deleting a SQL profile, and so on).

You can uniquely identify a single plan via the combination of SQL_ID and the PLAN_HASH_VALUE column of V$SQL and use that as input to DBMS_SPM, for example:

DECLARE
  plan1 PLS_INTEGER;
BEGIN
  plan1 := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => 'dv73f7y69ny8z',
             plan_hash_value => 3956160932);
END;
/

If you don’t specify a PLAN_HASH_VALUE when loading a plan baseline from memory, then Oracle will load all plans available for a SQL query in memory. The next time the query executes, the optimizer will use the lowest cost accepted plan in the plan baseline. If multiple plans exist in the plan baseline for a query, and if you have a specific plan that you want the optimizer to always use, then consider altering the plan to a FIXED state. See Recipe 12-12 for details on altering a given execution plan within the plan baseline to a FIXED state.

The “Solution” section described how to identify a single SQL statement for which you want to create a plan baseline (based on the SQL_ID) using a query in the cursor cache. There are many methods for creating a plan baseline for a query, such as using the SQL text, schema, module, and so on. For example, next a plan baseline is loaded based on a partial SQL string:

DECLARE
  plan1 PLS_INTEGER;
BEGIN
  plan1 := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
             attribute_name => 'sql_text'
            ,attribute_value => 'select emp_id from emp%'),
END;
/

See Table 12-4 for details on input parameters available with the DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE function.

Problem

You have the following scenario:

• You’re upgrading a database to a new version.
• You know from past experience that upgrading to newer versions of Oracle can sometimes cause SQL statements to perform poorly because the optimizer in the upgraded version of the database is choosing a less efficient (worse) execution plan than the optimizer from the prior version of the database.
• You want to ensure that a set of critical SQL statements execute with acceptable performance after the upgrade.

In essence, you are upgrading and would prefer that the optimizer choose the same execution plans (for the given set of SQL queries) both before and after the upgrade. You don’t want the upgrade to result in new plans that risk degrading performance.

Solution

To deal with this problem, use the most resource-intensive SQL queries in the AWR as candidates for the creation of plan baselines. This solution uses the technique of creating an AWR baseline. An AWR baseline is a snapshot of activity in the AWR designated by begin/end snapshot IDs. Listed next are the steps for creating and populating a SQL tuning set with high resource-consuming SQL statements found in an AWR baseline and then creating plan baselines for those queries:

1. Create an AWR baseline.
2. Create a SQL tuning set object.
3. Populate the SQL tuning set with the queries found in the AWR baseline.
4. Use the tuning set as input to DBMS_SPM to create a plan baseline for each query contained in the SQL tuning set.

BEGIN
  DBMS_WORKLOAD_REPOSITORY.create_baseline (
    start_snap_id => 2150,
    end_snap_id   => 2155,
    baseline_name => 'peak_baseline_jun14_13'),
END;
/

SQL> select snap_id, begin_interval_time from dba_hist_snapshot order by 1;

BEGIN
  dbms_sqltune.create_sqlset(
    sqlset_name => 'test1'
   ,description => 'STS from AWR'),
END;
/

DECLARE
  base_cur dbms_sqltune.sqlset_cursor;
BEGIN
  OPEN base_cur FOR
    SELECT value(x)
    FROM table(dbms_sqltune.select_workload_repository(
      'peak_baseline_jun14_13', null, null,'elapsed_time',
      null, null, null, 15)) x;
  dbms_sqltune.load_sqlset(
    sqlset_name => 'test1',
    populate_cursor => base_cur);
END;
/

SELECT sqlset_name, elapsed_time
,cpu_time, buffer_gets, disk_reads, sql_text
FROM dba_sqlset_statements
WHERE sqlset_name = 'test1';

DECLARE
  test_plan1 PLS_INTEGER;
BEGIN
  test_plan1 := dbms_spm.load_plans_from_sqlset(
                  sqlset_name=>'test1'),
END;
/

SQL> select sql_handle, plan_name, sql_text from dba_sql_plan_baselines;

How It Works

The technique shown in the “Solution” section is a very powerful method for creating plan baselines for the most resource-consuming queries running in your database. The key to this recipe is understanding that you can use as input (to the DBMS_SPM package) queries contained in a SQL tuning set. A SQL tuning set can be populated from high resource-consuming statements found in the AWR and memory. This allows you to easily create plan baselines for the most problematic queries.

Having plan baselines in place for resource-intensive queries helps ensure that the same execution plan is used after there are changes to your system, such as a database upgrades, changes in statistics, different data sets, and so on.

Keep in mind that it’s possible to have more than one accepted execution plan within the plan baseline. If you have a specific plan that you want the optimizer to always use, then consider altering the plan to a FIXED state. See Recipe 12-12 for details on altering a plan baseline to a FIXED state.

Problem

You want to automatically create plan baselines for every SQL query that repeatedly executes in your database.

Solution

Listed next are the steps for automatically creating plan baselines for SQL statements that execute more than once:

1. Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter to TRUE (either at the session or system level).
2. Execute two times or more the queries for which you want plan baselines captured.
3. Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to FALSE.

This next example illustrates the process for adding a plan baseline (for a query) using the prior steps. First, set the specified initialization parameter at the session level:

SQL> alter session set optimizer_capture_sql_plan_baselines=true;

Now a query is executed twice. Oracle will automatically create a plan baseline for a query that is run two or more times while the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter is set to TRUE:

SQL> select first_name from emp where emp_id=3000;
SQL> select first_name from emp where emp_id=3000;

Now set the initialization parameter back to FALSE.

SQL> alter session set optimizer_capture_sql_plan_baselines=false;

The query now should have an entry in the DBA_SQL_PLAN_BASELINES view showing that it has an enabled plan baseline associated with it—for example:

SELECT
 sql_handle, plan_name, enabled, accepted,
 created, optimizer_cost, sql_text
FROM dba_sql_plan_baselines;

Here is a partial listing of the output:

SQL_HANDLE           PLAN_NAME                      ENA ACC...
-------------------- ------------------------------ --- ---...
SQL_790bd425fe4a0125 SQL_PLAN_7k2yn4rz4n095d8a279cc YES YES...

How It Works

Enabling OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES allows you to automatically capture plan baselines for queries running repeatedly (more than once) in your database. The “Solution” section described how to use this feature at the session level. You can also set the parameter so that all repeating queries in the database have plan baselines generated—for example:

SQL> alter system set optimizer_capture_sql_plan_baselines=true;

From this point, any query in the database that runs more than once will automatically have a plan baseline created for it. We wouldn’t recommend that you do this in a production environment unless you have first carefully tested this feature and ensured that there will be no adverse side effects (from storing a plan baseline for every query executing more than once). However, you may have a test environment where you want to purposely create a plan baseline for every SQL statement that is repeatedly run.

Problem

You’ve been monitoring the plan baseline of a SQL query and notice there are two execution plans with an accepted and enabled status. You’ve examined at both of the execution plans and are confident that one plan is superior to the other based on the value of OPTIMIZER_COST in DBA_SQL_PLAN_BASELINES. You want to instruct the optimizer to always use the plan with the lowest cost.

Solution

The optimizer will give preference to plan baselines with a FIXED state. Use the DBMS_SPM package and ALTER_SQL_PLAN_BASELINE function to alter a current plan baseline execution plan to FIXED. Here’s an example:

DECLARE
 pf PLS_INTEGER;
BEGIN
  pf := dbms_spm.alter_sql_plan_baseline(
    plan_name => 'SQL_PLAN_1wskqhvrwf8g60e23be79'
   ,attribute_name => 'fixed'
   ,attribute_value => 'YES'),
END;
/

You can query the FIXED column of DBA_SQL_PLAN_BASELINES to verify that it is now fixed within the baseline. Listed next is such a query:

SQL> SELECT sql_handle, plan_name, enabled, accepted, fixed FROM dba_sql_plan_baselines;

Here is some sample output:

SQL_HANDLE           PLAN_NAME                      ENA ACC FIX
-------------------- ------------------------------ --- --- ---
SQL_457bf2f82571bd38 SQL_PLAN_4ayzkz0kr3g9s90e466fd YES YES NO
SQL_790bd425fe4a0125 SQL_PLAN_7k2yn4rz4n095d8a279cc YES YES YES

How It Works

You can think of fixing a plan baseline as a way of establishing a preference hierarchy for how the optimizer chooses an execution plan from within the plan baseline. The optimizer will give first priority to any fixed plans within the plan baseline. If no fixed plan is available, then accepted non-fixed plan baselines are considered. Execution plans that are added to a plan baseline that already contains a fixed plan baseline will be considered secondary (unless you add them as fixed).

Table 12-5 describes the parameters available with ALTER_SQL_PLAN_BASELINE. You can specify either the SQL_HANDLE or PLAN_NAME or both. If the SQL_HANDLE is NULL, then a PLAN_NAME must be specified and vice versa.

The ATTRIBUTE_NAME and ATTRIBUTE_VALUE parameters consist of a name/value pairing that can be used to alter various attributes of a plan baseline. See Table 12-6 for a complete description of the possible pairings.

Problem

You recently implemented a plan baseline for a query. You want to verify the configuration of a plan baseline.

Solution

Run the following query to view details regarding any plan baselines that have been configured:

set pages 100 linesize 150
col sql_handle form a20
col plan_name form a30
col sql_text form a40
col created form a20
--
SELECT sql_handle, plan_name, enabled
,accepted, created, optimizer_cost, sql_text
FROM dba_sql_plan_baselines;

The output from the prior query is very wide and has been modified to fit within the page width:

SQL_HANDLE           PLAN_NAME                      ENA ACC
-------------------- ------------------------------ --- ---
SQL_b98d2ae2145eec3d SQL_PLAN_bm39aw8a5xv1xae72d2f5 YES YES
CREATED              OPTIMIZER_COST SQL_TEXT
-------------------- -------------- --------------------
21-MAR-11 10.53.29.0              2 select last_name from custs...

In the output, there are two key columns: the SQL_HANDLE and PLAN_NAME. Each query has an associated SQL_HANDLE that is an identifier for a query. Each execution plan has a unique PLAN_NAME. The PLAN_NAME is unique within DBA_SQL_PLAN_BASELINES, whereas one query (identified by SQL_HANDLE) can have multiple execution plans (identified by PLAN_NAME).

How It Works

The DBA_SQL_PLAN_BASELINES view provides a quick and easy way to determine if plan baselines exist and are in use. If a plan is enabled and accepted, then the query has a plan baseline in use.

You can also view and manage plan baselines from Enterprise Manager. From the Performance tab, click on SQL, then click on SQL Plan Control. Next, click the SQL Plan Baseline tab. You should see a screen similar to Figure 12-4.

From this screen, you can perform tasks such as enabling, disabling, dropping, and evolving plan baselines.

Problem

You’ve been assigned to investigate performance issues with a database and application.  As a preliminary task of understanding the environment, you want to determine if any SQL statements are using plan baselines.

Solution

You can directly determine if a SQL statement currently in memory has utilized a plan baseline via the following query:

select sql_id, child_number, sql_plan_baseline, sql_text
from v$sql
where sql_plan_baseline is not null
and sql_text like 'select first_name%';

Here is a snippet of the output:

SQL_ID        CHILD_NUMBER SQL_PLAN_BASELINE              SQL_TEXT
------------- ------------ ------------------------------ -------------------------
898c46878bm3f            0 SQL_PLAN_31kcfbjxvdfaxc4b18358 select first_name from ...

How It Works

The technique shown in the “Solution” section only shows SQL statements that are currently in memory (V$SQL). You can manually verify the optimizer’s use of a plan baseline by viewing the output from tools such as Autotrace or DBMS_XPLAN. For example:

SQL> set autotrace trace explain;
SQL> select first_name from emp where emp_id = 3000;

Here is a partial listing of the output indicating that a SQL plan baseline execution plan is used for this query:

Note
-----
   - SQL plan baseline "SQL_PLAN_5jjxkya4jrxv5d8a279cc" used for this statement

Here’s an example of using DBMS_XPLAN:

EXPLAIN PLAN
SET STATEMENT_ID = 'myplan' FOR
select first_name from emp where emp_id=3000;
--
set pagesize 100
set linesize 132
set long 1000000
col xplan format a132
--
SELECT dbms_xplan.display_plan(statement_id => 'myplan') AS XPLAN
FROM dual;

Here’s the output indicating a plan baseline was used by the optimizer to determine the execution plan:

Note
-----
- SQL plan baseline "SQL_PLAN_5jjxkya4jrxv5d8a279cc" used for this statement

Problem

You want to view any execution plans for a given plan baseline.

Solution

If you’re not sure which plan baseline you want to view, then first query the DBA_SQL_PLAN_BASELINES view:

SQL> SELECT plan_name, sql_text FROM dba_sql_plan_baselines;

After you determine the plan name, then use the DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE function to display the execution plan and corresponding plan baseline details. This example reports details for a specific plan:

SELECT *
FROM TABLE(
DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(plan_name=>'SQL_PLAN_5jjxkya4jrxv5d8a279cc'));

Here is a partial listing of the output:

--------------------------------------------------------------------------------
SQL handle: SQL_58c7b2f2891bf765
SQL text:  select first_name from emp where emp_id=3000
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_5jjxkya4jrxv5d8a279cc         Plan id: 3634526668
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     6 |    34   (3)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |     6 |    34   (3)| 00:00:01 |
--------------------------------------------------------------------------

How It Works

The DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE function allows you to display one or more execution plans in a plan baseline. The return type for this function is a PL/SQL table type. This function takes three parameters (described in Table 12-7).

If you want to display all plans for a SQL statement, then use as input the SQL_HANDLE parameter—for example:

SELECT *
FROM TABLE(
DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(sql_handle=>'SQL_b98d2ae2145eec3d'));

If there are multiple plans, then each plan will be displayed in the output.

Problem

You have the following scenario:

• You have an existing plan baseline for the query.
• You have recently added an index that the query can use.
• The optimizer determines a new lower-cost plan is now available for the query and adds the new plan to the plan history in an unaccepted state.
• You notice the new plan either from a recommendation by the SQL Tuning Advisor or by querying the DBA_SQL_PLAN_BASELINES view.
• You have examined the new execution plan, have run the query in a test environment, and are confident that the new plan will result in better performance.

You want to evolve the low-cost plan in the history so that it’s moved to an accepted plan in the baseline. You realize that once the plan is accepted in the baseline, the optimizer will use it (if it’s the lowest-cost plan in the baseline).

Solution

First verify that there are plans in the unaccepted state for the query in question. Here’s a quick example:

SELECT sql_handle, plan_name, enabled, accepted, optimizer_cost
FROM dba_sql_plan_baselines
WHERE sql_text like '%select first_name from emp where emp_id=3000%';

Here is the output indicating there are two plans, one unaccepted but with a much lower cost:

SQL_HANDLE                PLAN_NAME                      ENA ACC OPTIMIZER_COST
------------------------- ------------------------------ --- --- --------------
SQL_58c7b2f2891bf765      SQL_PLAN_5jjxkya4jrxv5c4b18358 YES NO               2
SQL_58c7b2f2891bf765      SQL_PLAN_5jjxkya4jrxv5d8a279cc YES YES             34

Use the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function to move a plan from the history to the baseline (evolve the plan). In this example, the SQL handle (unique SQL string associated with a SQL statement) is used to evolve a plan:

SET SERVEROUT ON SIZE 1000000
SET LONG 100000
DECLARE
  rpt CLOB;
BEGIN
  rpt := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
    sql_handle => 'SQL_58c7b2f2891bf765'),
  DBMS_OUTPUT.PUT_LINE(rpt);
END;
/

If Oracle determines that there is an unaccepted plan with a lower cost, then you’ll see output similar to this indicating that the plan has been moved to the accepted state (evolved):

GENERAL INFORMATION SECTION
---------------------------------------------------------------------------------------------
 
 Task Information:
 
---------------------------------------------
 Task Name            : TASK_3772
 Task Owner           : SYS
 Execution Name       : EXEC_3979
 Execution Type
: SPM EVOLVE
 Scope                : COMPREHENSIVE
 Status               : COMPLETED
 Started              : 06/15/2013 15:41:01
...
FINDINGS
SECTION
---------------------------------------------------------------------------------------------
 
Findings (2):
-----------------------------
 1. The plan was verified in 0.31000 seconds. It
passed the benefit criterion
    because its verified performance was 40.07033 times better than that of the
    baseline plan.
 2.The plan was automatically accepted.

You can quickly verify that the new plan baseline is now in use by setting AUTOTRACE on and running the query—for example:

SQL> set autotrace trace explain;
SQL> select first_name from emp where emp_id=3000;

Here’s a small snippet of the output indicating the new plan baseline is in use:

Note
-----
   - SQL plan baseline "SQL_PLAN_5jjxkya4jrxv5c4b18358" used for this statement

How It Works

One key feature of SQL plan management is that when a new low-cost plan is generated by the query optimizer, if the new low-cost plan has a lower cost than the accepted plan(s) in the plan baseline, the new low-cost plan will automatically be added to the query’s plan history in an unaccepted state.

You can choose to accept this new low-cost plan, which then moves it into the plan baseline as accepted. Moving an unaccepted execution plan from the plan history to the plan baseline (ENABLED and ACCEPTED) is known as evolving the plan baseline.

Why would a new plan ever be generated by the optimizer? There are several factors that would cause the optimizer to create a new execution plan that doesn’t match an existing one in the plan baseline:

• New statistics are available.
• A new SQL profile has been assigned to the query.
• An index has been added or dropped.

This gives you a powerful technique to manage and use new plans as they become available. We should also point out again that starting with Oracle Database 12c, new low-cost execution plans that are added to the plan history will automatically be evolved (added to the plan baseline). See Recipe 12-17 for details on modifying this default behavior.

You can use the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function in the following modes:

• Specify the name of the plan to evolve.
• Provide a list of plans to evolve.
• Run it with no value, meaning that Oracle will evolve all non-accepted plans contained within the plan baseline repository.

Table 12-8 describes the parameters used in the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function.

Problem

In Oracle Database 12c, if a plan baseline exists for a query, and a new lower cost execution plan is generated by the optimizer, the new execution plan will automatically be entered into the plan baseline in an accepted state. You want to change the behavior of Oracle so as to not automatically add new execution plans to the plan baseline.

Solution

You can disable the automatic acceptance of a plan added to the plan baseline via the following code:

BEGIN
  DBMS_SPM.SET_EVOLVE_TASK_PARAMETER('SYS_AUTO_SPM_EVOLVE_TASK',
  'ACCEPT_PLANS', 'false'),
END;
/

How It Works

New in Oracle Database 12c, execution plans will automatically be evolved for you when the daily SYS_AUTO_SPM_EVOLVE_TASK job runs. You can verify that plans will be automatically evaluated and potentially moved to the accepted state via this query:

select parameter_name, parameter_value
from dba_advisor_parameters
where task_name = 'SYS_AUTO_SPM_EVOLVE_TASK'
and parameter_name = 'ACCEPT_PLANS';

You can re-enable the automatic acceptance of plans with this code:

BEGIN
  DBMS_SPM.SET_EVOLVE_TASK_PARAMETER('SYS_AUTO_SPM_EVOLVE_TASK',
  'ACCEPT_PLANS', 'true'),
END;

Problem

You’re working with a test database that has many SQL statements with associated plan baselines. You want to determine what the performance difference would be without the plan baselines enabled and therefore want to temporarily disable the use of plan baselines.

Solution

To disable the use of any SQL plan baselines within the database, set the OPTIMIZER_USE_SQL_PLAN_BASELINES initialization parameter to FALSE:

SQL> alter system set optimizer_use_sql_plan_baselines=false scope=memory;

The prior line disables the use of the plan baselines at the SYSTEM level and records the value in memory (but not in the server parameter file). To re-enable the use of plan baselines, set the value back to TRUE.

You can also set the OPTIMIZER_USE_SQL_PLAN_BASELINES at the session level. This disables the use of plan baselines for the duration of the session for the currently connected user:

SQL> alter session set optimizer_use_sql_plan_baselines=false;

How It Works

The default value for OPTIMIZER_USE_SQL_PLAN_BASELINES is TRUE, which means by default, if plan baselines are available, they will be used. When enabled, the optimizer will look for a valid plan baseline execution plan for the given SQL query and choose the one with the lowest cost. This gives you a quick and easy way to disable/enable the use of plan baselines within your entire database or specific to a session.

If you want to disable the use of one specific plan baseline, then alter its state to DISABLED (by setting the ENABLED attribute to a value of NO):

DECLARE
 pf PLS_INTEGER;
BEGIN
  pf := dbms_spm.alter_sql_plan_baseline(
    plan_name => 'SQL_PLAN_4ayzkz0kr3g9s6afbe2b3'
   ,attribute_name => 'ENABLED'
   ,attribute_value => 'NO'),
END;
/

Problem

You have several plan baselines that you no longer want to use and therefore want to remove them.

Solution

First, determine which plan baselines exist for your database:

SQL> select plan_name, sql_handle, optimizer_cost from dba_sql_plan_baselines;

Once you have either the PLAN_NAME or the SQL_HANDLE, you can drop a single plan baseline. This removes a single execution plan from the plan baseline using the PLAN_NAME parameter:

DECLARE
  plan_name1 PLS_INTEGER;
BEGIN
  plan_name1 := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
                           plan_name => 'SQL_PLAN_bm39aw8a5xv1x519fc7bf'),
END;
/

You can also drop all plans associated with a SQL statement. This example removes all plans associated with a SQL statement using the SQL_HANDLE parameter:

DECLARE
  sql_handle1 PLS_INTEGER;
BEGIN
  sql_handle1 := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
                           sql_handle => 'SQL_b98d2ae2145eec3d'),
END;
/

How It Works

You may occasionally want to remove SQL plan baselines for the following reasons:

• You have old plans that aren’t used anymore because more efficient plans (evolved) are available for a SQL statement (see Recipe 12-14 for determining if a query is using a plan baseline).
• You have plans that were never accepted and now want to remove them.
• You have plans that were created for testing environments that are no longer needed.

As shown in the “Solution” section, you can remove a specific plan baseline via the PLAN_NAME parameter. This will remove one specific plan. If you have several plans associated with one SQL statement, you can remove all plan baselines for that SQL statement via the SQL_HANDLE parameter.

If you have a database where you want to clear out all plans, then you can encapsulate the call DBMS_SPM.DROP_SQL_PLAN_BASELINE within a PL/SQL block that drops all plans by looping through any plan found in DBA_SQL_PLAN_BASELINES:

SET SERVEROUT ON SIZE 1000000
DECLARE
  sql_handle1 PLS_INTEGER;
  CURSOR c1 IS
    SELECT sql_handle
    FROM dba_sql_plan_baselines;
BEGIN
  FOR r1 IN c1 LOOP
    sql_handle1 := DBMS_SPM.DROP_SQL_PLAN_BASELINE(sql_handle => r1.sql_handle);
    DBMS_OUTPUT.PUT_LINE('PB dropped for SH: ' || r1.sql_handle);
  END LOOP;
END;
/

Problem

You have a test environment, and you want to ensure that all of the plan baselines in the test system are moved to a production database.

Solution

Follow these steps to transport plan baselines:

1. Create a table using the DBMS_SPM package and CREATE_STGTAB_BASELINE procedure.
2. Populate the table with plan baselines using the DBMS_SPM.PACK_STGTAB_BASELINE function.
3. Copy the staging table to the destination database using a database link or Data Pump.
4. Import the plan baseline information using the DBMS_SPM.UNPACK_STGTAB_BASELINE function.

This example first uses the DBMS_SPM package to create a table named EXP_PB:

BEGIN
  DBMS_SPM.CREATE_STGTAB_BASELINE(table_name => 'exp_pb'),
END;
/

Next the EXP_PB table is populated with plan baselines created by the database user MV_MAINT:

DECLARE
  pbs NUMBER;
BEGIN
  pbs := DBMS_SPM.PACK_STGTAB_BASELINE(
           table_name => 'exp_pb',
           enabled => 'yes',
           creator => 'MV_MAINT'),
END;
/

The prior code populates the table with all plan baselines created by a user. You can also populate the table by PLAN_NAME, SQL_HANDLE, SQL_TEXT, or various other criteria. The only mandatory parameter is the name of the table to be populated.

Now copy the staging table to the destination database. You can use a database link or Data Pump to accomplish this.

Lastly, on the destination database, use the DBMS_SPM.UNPACK_STGTAB_BASELINE function to take the contents of the EXP_PB table and create plan baselines:

DECLARE
  pbs NUMBER;
BEGIN
  pbs := DBMS_SPM.UNPACK_STGTAB_BASELINE(
           table_name => 'exp_pb',
           enabled => 'yes'),
END;
/

You should now have all of the plan baselines transferred to your target database. You can query DBA_SQL_PLAN_BASELINES to verify this.

How It Works

It’s a fairly easy process to create a table, populate it with plan baseline information, copy the table, and the import its contents into the destination database. As shown in step 2 of the “Solution” section of this recipe, the PACK_STGTAB_BASELINE function is used (see Table 12-9) for details on parameters to this function). This function allows quite a bit of flexibility in what types of plan baselines you want exported. You can limit the plan baselines extracted to a specific user, or enabled, or accepted, and so on.