Fixing Execution Plan by SQL Profile

After reading Kerry Osborne’s presentation Controlling Execution Plans, I decide to sum up what I learned about SQL Profile and to make some experiments of my own.

To tune a sql in case that we CAN NOT change the code, some tuning methods are available, but keep in mind they’re not specific solution comparing with sql profile:

• gathering or updating objects’ statistics
• adding or removing accessing paths (e.g. indexes, mv, etc.)
• adjust init parameters (e.g. in session level)

SQL Profile will take advantage of other tuning methods in such conditions:

• to fix a bad execution plan of one particular SQL (it is not a general systematic problem affecting a lot of others)
• we can’t change the code ( we don’t own the code; we’re in emergency and have no time to change the code; we need to follow a company rule to make changes in production, etc.)
• the same sql executes well in test environment but has very bad performance in production
• the same sql executes well in the past but has very bad performance today

Description about SQL Profile by Tom Kyte:

So, a SQL profile is sort of like gathering statistics on A QUERY which involves many tables, columns and the like….

In fact – it is just like gathering statistics for a query, it stores additional information in the dictionary which the optimizer uses at optimization time to determine the correct plan. The SQL Profile is not “locking a plan in place”, but rather giving the optimizer yet more bits of information it can use to get the right plan.

There are several ways to create SQL Profiles

• Using SQL Tuning Adviser from EM or via DBMS_SQLTUNE package
• Attaching a SQL Profile to a different SQL (e.g. update existing execution plan and attach it to a different SQL using KO’s scripts, see demo)
• Moving a SQL Profile from one database to another database ( using script )

Create SQL Profile by SQL Tuning Adviser

1. find sql with

Using Sql Tuning Advisor – Sql Profile

Automatic Sql Tuning is a new capability of query optimizer introduced in 10g. In  10g, the enhanced Query Optimizer can be run in 2 different ways: normal mode and tuning mode.

Enhanced Query Optimizer in 10g

• in normal mode, Optimizer takes a short time to make a decision on a good execution plan, this is how usually a statement is executed, sometimes it may not be an optimal execution plan
• in tuning mode, Optimizer performs some extra tuning work to see if the execution plan can be tuned further. This is where Autimatic Sql Tuning is carrying on. Running in tuning mode, Optimizer performs time and resource intensive tasks so it’s meant for complex and heavy load SQL.

Automatic Sql Tuning Tasks

Automatic Sql Tuning automates the following tuning tasks:

• statistics analysis

it recommends to gather stale or missing statistics; some additional statistics are saved in sql profile;

• sql profiling

Optimizer produces estimate on selectivity, cardinality based on statistics. Sometimes it can be far way from reality. Sql Profiling addresses this problem by collecting more information by sampling or partially executing methods. It saves the additional information related to a specific statement is a SQL Profile.

The output of sql profiling is recommendation of acceptation of the SQL Profile. Once accepted, the profile is saved in DD which can be viewed from DBA_SQL_PROFILES.

The SQL Profile can be controlled by CATALOG. By default, it’s created in DEFAULT catalog. When altering the category of a SQL profile, you can determine which sessions are affected by the creation of a profile. For example, by setting the category of a SQL Profile to DEV, only those users sessions where the SQLTUNE_CATEGORY initialization parameter is set to DEV can use the profile.

• access path analysis

Optimizer will check new or modified indexes on access paths. It will also recommend to run SQL Access Adviser to check the impact of new indexes with some workload.

• structure analysis

Optimizer recommends new sql statement structure, it requires human’s validation.

Automatic Sql Tuning is performed by the utility called Sql Tuning Adviser, which is accessible from EM on “Adviser Center” page or by package DBMS_SQLTUNE. This page demonstrates usage of DBMS_SQLTUNE.

Sql Tuning Advisor

Step 0) In order to execute Sql Tuning Adviser API, one must be granted with “ADVISER” role.

grant adviser to <user>;

Step 1) The first step using SQL Tuning Adviser is to create a tuning task using DBMS_SQLTUNE.CREATE_TUNING_TASK.

— for a specific statement from AWR

SET SERVEROUTPUT ON

-- Tuning task created for specific a statement from the AWR.
DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          begin_snap  => 764,
                          end_snap    => 938,
                          sql_id      => '19v5guvsgcd1v',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => '19v5guvsgcd1v_AWR_tuning_task',
                          description => 'Tuning task for statement 19v5guvsgcd1v in AWR.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;

— or for a specific statement from Shared Library Cache

DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => 'fhahkc71k304u',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'fhahkc71k304u_tuning_task',
                          description => 'Tuning task for statement fhahkc71k304u.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

— or for a specific statement given manually

During the execution phase you may wish to pause and restart the task, cancel it or reset the task to allow it to be re-executed:

</pre>
<blockquote>
<pre>-- Interrupt and resume a tuning task.
EXEC DBMS_SQLTUNE.interrupt_tuning_task (task_name => 'emp_dept_tuning_task');
EXEC DBMS_SQLTUNE.resume_tuning_task (task_name => 'emp_dept_tuning_task');

-- Cancel a tuning task.
EXEC DBMS_SQLTUNE.cancel_tuning_task (task_name => 'emp_dept_tuning_task');

-- Reset a tuning task allowing it to be re-executed.
EXEC DBMS_SQLTUNE.reset_tuning_task (task_name => 'emp_dept_tuning_task');

——————————————————————————————
6 create a tuning task
——————————————————————————————
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => ‘fhahkc71k304u’,
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => ‘fhahkc71k304u_tuning_task’,
description => ‘Tuning task for statement fhahkc71k304u.’);
DBMS_OUTPUT.put_line(‘l_sql_tune_task_id: ‘ || l_sql_tune_task_id);
END;
/

ERROR at line 1:
ORA-13780: SQL statement does not exist.
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 79
ORA-06512: at “SYS.DBMS_SQLTUNE”, line 795
ORA-06512: at line 4

——————————————————————————————
6 create a tuning task from AWR
——————————————————————————————
get snap ids of today

sys@goldprod> select SNAP_ID, BEGIN_INTERVAL_TIME from dba_hist_snapshot where BEGIN_INTERVAL_TIME > trunc(sysdate) order by snap_id;

   SNAP_ID BEGIN_INTERVAL_TIME
---------- ---------------------------------------------------------------------------
      4042 28-APR-11 12.00.29.410 AM
      4043 28-APR-11 01.01.01.094 AM
      4044 28-APR-11 02.00.32.909 AM
      4045 28-APR-11 03.00.07.558 AM
      4046 28-APR-11 04.00.40.121 AM
      4047 28-APR-11 05.00.14.894 AM
      4048 28-APR-11 06.00.59.123 AM
      4049 28-APR-11 07.00.23.056 AM
      4050 28-APR-11 08.00.51.205 AM
      4051 28-APR-11 09.00.19.892 AM
      4052 28-APR-11 10.00.35.227 AM
      4053 28-APR-11 11.00.02.168 AM
      4054 28-APR-11 12.00.37.690 PM
      4055 28-APR-11 01.00.09.106 PM

DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          begin_snap  => 4042,
                          end_snap    => 4055,
                          sql_id      => 'fhahkc71k304u',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'fhahkc71k304u_AWR_tuning_task',
                          description => 'Tuning task for statement fhahkc71k304u in AWR.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

PL/SQL procedure successfully completed.

7) execute the tuning task

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'fhahkc71k304u_AWR_tuning_task');

8) report tuning task findings

SET LONG 100000;
SET PAGESIZE 1000
SET LINESIZE 300
SELECT DBMS_SQLTUNE.report_tuning_task('fhahkc71k304u_AWR_tuning_task') AS recommendations FROM dual;
SET PAGESIZE 24

9) accept recommendations

execute dbms_sqltune.accept_sql_profile(task_name =>'fhahkc71k304u_AWR_tuning_task', replace => TRUE);

10) verify if the sql profile is used

Useful Views

Useful views related to automatic SQL tuning include:

• DBA_ADVISOR_TASKS
• DBA_ADVISOR_FINDINGS
• DBA_ADVISOR_RECOMMENDATIONS
• DBA_ADVISOR_RATIONALE
• DBA_SQLTUNE_STATISTICS
• DBA_SQLTUNE_BINDS
• DBA_SQLTUNE_PLANS
• DBA_SQLSET
• DBA_SQLSET_BINDS
• DBA_SQLSET_STATEMENTS
• DBA_SQLSET_REFERENCES
• DBA_SQL_PROFILES
• V$SQL
• V$SQLAREA
• V$ACTIVE_SESSION_HISTORY

PROCEDURE COMPILE_SCHEMA
Argument Name                  Type                    In/Out Default?
—————————— ———————– —— ——–
SCHEMA                         VARCHAR2                IN
COMPILE_ALL                    BOOLEAN                 IN     DEFAULT
REUSE_SETTINGS                 BOOLEAN                 IN     DEFAULT