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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: