11g SQL Performance Analyzer Using PL/SQL Oracle 11g New Features Tips by Donald BurlesonJune 28, 2015

Oracle 11g New Features Tips

The built in packages DBMS_SQLTUNE and DBMS_SQLPA contain all procedures and functions needed to perform an SPA tuning session. This is a part of Oracle's top-down tuning approach, whereby we tune the workload and instance before tuning individual SQL statements:

The following is a closer look at the steps of the workflow:

Step 1:

To capture the workload for testing, a SQL Tuning Set (STS) must be created. The package DBMS_SQLTUNE is used for this. The STS is the unit which will be packed into a staging table and shipped to the testing system. An STS is a schema object owned by a user. It is possible to use the actual workload from the cursor cache or, as an alternative source, choose a historical workload retrieved from the Automatic Workload Repository by using an AWR snapshot or an AWR baseline.  

A SQL Tuning Set contains:                                                                                 

• The text of the captured SQL statements

• The name of the parsing schema and used bind variable values

• Performance statistics for the execution of the statements such as average elapsed time and execution count

• The execution plans the optimizer has created

• Row source statistics (number of rows processed for each step of execution)

It is possible to filter out unwanted statements from a workload. Incremental capturing can be used to populate SQL statements over a period of time from the cursor cache into an STS.

First, create the STS with the built in package dbms_sqltune:

Lutz @ orcl11g SQL> EXEC DBMS_SQLTUNE.CREATE_SQLSET(-
  sqlset_name => 'my_workld_sts', -
  description => 'my test workload');

Then capture the workload into the STS. Here is a simple example for capturing SQL statements from the cursor cache into an STS:

Lutz @ orcl11g SQL> EXEC DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET( -
                                        sqlset_name     => 'my_workld_sts', -
                                        time_limit      =>  300); -- seconds

It is possible to specify a repeat interval and add filters for the capture process as well as options for how to handle repeating SQL.

Step 2:

After creating the STS which now contains the workload data, it must be loaded (packed) into a staging table which then can be shipped to the testing system using expdp / impdp or a database link.

Create the staging table:

Lutz @ orcl11g SQL> EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(table_name => 'MY_STAGETABLE'); -- the name of the table is case sensitive!

Then load the STS into the staging table:

Lutz @ orcl11g SQL> EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLSET(-
                           sqlset_name - => 'my_workld_sts', -
                           staging_table_name => 'MY_STAGETABLE');

Now the workload is ready for shipping to the test system.

After importing the staging table with the STS into the test system, unload it from the imported staging table (unpack) and then perform the first test run of the workload. This unloads STS from the staging table into the data dictionary:

Lutz @ orcl_test SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(-
                           sqlset_name - => 'my_workld_sts', -
                           staging_table_name => 'MY_STAGETABLE');

Step 3:

In this and the next steps, use the built-in 11g package dbms_sqlpa. Then run the imported workload two times on the testing system. The before change run is used to capture the first set of performance statistics for the captured workload in the test environment. These can then be compared to the performance statistics which will be taken for the second run which will be called the after change run.

First, create a tuning task on the test system:

Lutz @ orcl_test SQL> var task_name varachar2(30)
                       exec :task_name:= dbms_sqlpa.create_analysis_task( -
                            sqlset_name=>'my_workld_sts', task_type => 'sqlpa'); 

Next, perform the before change run:

Lutz @ orcl_test SQL> exec dbms_sqlpa.execute_analysis_task(:task_name, -
                     execution_type=>'test execute' ?
                      execution_name=> ?before_changes?);

% The parameter task_type with value TEST EXECUTE puts all SQL texts into    the report. This parameter also accepts the value EXPLAIN PLAN which   creates a report that holds the explain plans for all statements of the workload. 

This creates the first set of performance statistics for the test workload on the test system. Look at the performance report of the first test run. Here is the syntax:

Lutz @ orcl_test SQL> SELECT dbms_sqlpa.report_analysis_task(-     
                                task_name=>:task_name,-
                                type=>'text',-
                                section=>'summary')
                      FROM dual;

The function report_analysis_task returns a CLOB (Character Large Object).

Step 4:

Make the changes to the test system to see the impact on the workload.

Step 5:

After changing the system, perform the after change run using exactly the same syntax as for the before change run. The overloaded procedure SET_ANALYSIS_TASK_PARAMETER can be used to adjust the analysis task.

DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(
  task_name         IN VARCHAR2,
  parameter         IN VARCHAR2,
  value             IN VARCHAR2);

DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(
  task_name         IN VARCHAR2,
  parameter         IN VARCHAR2,
  value             IN NUMBER);

This is a list of the parameters which can be adjusted:

APPLY_CAPTURED_COMPILENV
COMPARISON_METRIC
DAYS_TO_EXPIRE
DEFAULT_EXECUTION_TYPE
EXECUTION_DAYS_TO_EXPIRE
EXECUTION_NAME1
EXECUTION_NAME2
LOCAL_TIME_LIMIT
BASIC_FILTER
PLAN_FILTER
RANK_MEASURE1
RANK_MEASURE2
RANK_MEASURE3
RESUME_FILTER
BASIC_FILTER
SQL_IMPACT_THRESHOLD
SQL_LIMIT
SQL_PERCENTAGE
TIME_LIMIT
WORKLOAD_IMPACT_THRESHOLD

Run the workload for the second time:

 Lutz @ orcl_test SQL> exec dbms_sqlpa.execute_analysis_task(:task_name, -
                     execution_type=>'test execute' ?
                     Execution_name => ?after_changes?);

Then review the task report summary:

Lutz @ orcl_test SQL> SELECT dbms_sqlpa.report_analysis_task(-     
                                task_name=>:task_name,-
                                type=>'text',-
                                section=>'summary')
                      FROM dual;  

Step 6:

In the next step, analyze the performance for the two runs and compare the results:

Lutz @ orcl_test SQL> exec dbms_sqlpa.execute_analysis_task(:task_name,
              execution_type => ?compare performance?,
                              execution_name => ?analysis_results?,
                              execution_params => dbms_advisor.arglist(-
                                              ?execution_name1?, ?before_changes?,
                                              ?execution_name2?, ?after_changes?,
                                              ?comparison_metric?, ?buffer_gets?)); 

Lutz @ orcl_test SQL> SELECT dbms_sqlpa.report_analysis_task(-
                              task_name=>:task_name,-
                              type=>'text',-
                              section=>'summary')
                      FROM dual;

The value compare [performance] for the parameter excution_type can only be used after two runs when either test_execute or explain_plan have been performed.

% By default, the COMPARISON_METRIC parameter value  elapsed_time is   used for the comparison.

% It is also possible to choose one of the following metrics for the analysis task:   buffer_gets, cpu_time, direct_writes, optimizer_cost or disk_reads.

% Use an arithmetic expression for the metric. An example is cpu_time +   buffer_gets * 1.5

Step 7:

Now it is possible to identify the SQL statements which have encountered performance regression after the changes have been made to the testing system. There are a number of data dictionary views which can be used to monitor the SQL Performance Analyzer:

1.      DBA?USER_ADVISOR_SQLPLANS: list of all?user's SQL execution plans

2.      DBA?USER _ADVISOR_SQLSTATS: list of all?user's SQL compilation and execution statistics

3.      DBA?USER _ADVISOR_TASKS: details about the advisor task

4.      DBA?USER _ADVISOR_EXECUTIONS: list of metadata for the execution of the task.

5.      DBA?USER _ADVISOR_FINDINGS: list of analysis findings.

Step 8:

In the last step,  the regressing statements are handed over to the SQL Tuning Advisor or the SQL Access Advisor to see if it can find better execution plans. The output of this tuning task could be recommendations to implement SQL profiles for the regressing statements or other implementations. 

The exact impact of the changes to the system on the original workload have been reviewed and after having tested for improvements and regressions, one could apply the tested changes to the production as well. The SQL profiles could be shipped back to the production system and imported there.

As a final step, feed these improved plans into the SQL Plan Baselines in the SQL Management Base (SMB) of the production system in its SYSAUX tablespace. These concepts will be covered in a later section of this chapter.

Next, force the optimizer in the production database to use the better execution plans which the SQL Performance Advisor has found on the testing system.