Tuesday, 2 September 2014

12C AUTOMATIC SQL TUNING

To download this white paper click http://goo.gl/gg23o3

12C AUTOMATIC SQL TUNING

September 2014

Author :- KARAN DODWAL
Oracle Certified Master DBA
Senior Oracle Architect
Oracle Solaris Administrator
Oracle PLSQL Developer Expert
Oracle Database Instructor


                                      Automatic SQL Tuning


Automatic SQL Tuning was a new feature introduced in 10G and later in 11g some changes were made as well which we will discuss in this white paper, With 10G version Automatic sql tuning had the idea to run sql tuning advisor on high load sql statements so with the oracle 10g version Oracle introduced sql tuning advisor and with that the tuning experts used to tune the sql statements by manually running sql tuning advisor on the high load sql statements identified by ADDM. ADDM automatically identifies the high load statements by looking into the system (performance statistics in AWR retention in sysaux tablespace). The problem is that users must manually look into into ADDM reports and find the high load statements and run sql tuning advisor on them. 11G and 12C takes this 1 step ahead, these versions run the sql tuning advisor every night automatically on high load SQL statements based on the AWR top SQL identification and view the recommendation reports or even accept the recommendations automatically in the case of accepting sql profiles which must be enabled with accept_sql_profiles to true of dbms_sqltune tuning advisor package. We will discuss in a while what sql profile is and what is the use of it.

The nightly automatic sql tuning task has a tuning process which identifies high load sql statements and runs sql tuning advisor on them , and all recommendations are reported except the acceptance of sql profiles automatically which has to be enabled, otherwise everything is just recommended not implemented. In this discussion of ours we have used 2 major oracle words here which is sql tuning advisor and sql profiles. Let us understand what they are, sql access advisor will be discussed in my next article, so lets proceed with sql tuning advisor and sql profiles for now.

SQL Tuning Advisor

SQL Tuning Advisor is an advisor available in tuning pack and it takes the input the sql statements and performs analysis on them , it does 4 types of analysis on the sql statements and then give its recommendations for the DBA to implement if benefit percentage is good enough based on their requirements and the service level agreements(SLA). This advisor also runs at night during maitenance window and time limit for every statement is 1200 seconds (20 minutes) when this task runs. The 4 analysis done by sql tuning advisor are following :-

Statistics Analsysis := To see if tables involved in the statement have stale statistics or they do not have statistics at all. So if stats are not there or they are now stale oracle updates this information to gather_stats_job.


Access path analysis := How we can create indexes and better tune the statements. But remember only creation of indexes is recommended not the impact of it, that is why sometimes another advisor sql access advisor is recommended to be run by sql tuning advisor, sql access advisor is an expert which recommends on indexes like what kind of indexes should be created, or if we can change an existing type of index like for example changing a b tree index to a bitmap index if there is a low cardinality column. So sometimes this is the reason why sql tuning advisor recommends us to run sql access advisor because sql access advisor is an expert of indexes, materialized views and partitioning.

Sql Structure analysis := To check how we write our queries so it can recommend to change the code lets say for example use union all instead of union because union all avoids sorting or even it can recommend us to use exists operatior instead of IN.


SQL Profiling :- SQL Profiles were introduced in 10G and it continues to be available in 11g and 12C as well as it has lot of advantages. From 11g onwards SQL PLAN MANAGEMENT has taken over and it controls the whole usage of plans now and it takes cares of the sql profiles as well. SQL Profiles had a great impact on the working of oracle database in that you never had to hard code the select statement in order for you to kick in a particular access path. Hard coding was done in previous releases with hints through stored outlines. Optimizer has poor estimates sometimes in absence of accurate statistics thats when sql profile helps with information to the optimizer like database configuration, bind variable values, optimizer statistics, data set, etc. In short SQL profiles are a guide to the optimizer to make a better plan. Remember you dont create them on your own, You are guided to accept the sql profile by oracle with the help of sql tuning advisor which will then be used by oracle database automatically when the statement is parsed if the sql profile category is available.

A very critical thing to know is that SQL Profiles does not have capability to reproduce an execution plan whereas SQL Plan Baselines will have that capability. So SQL Profiles will only correct optimizer cost estimates and an SQL Profiles are only recommended once you run the SQL Tuning Advisor in which you want to tune a bad query so this means its a reactive approach in contrast to SQL PLan baseline's Proactive approach. We will be discussing more deep about sql profiles in another article but let us focus more on sql tuning advisor now.


If you have licensed your tuning pack you have SQL Tuning Advisor which can recommend to accept an sql profile for the following types of statements:


# DML statements (SELECT, INSERT with a SELECT clause, UPDATE, and DELETE)

# CREATE TABLE statements (only with the AS SELECT clause)

# MERGE statements (the update or insert operations)


SQL Tuning advisor can run in limited scope and comprehensive scope, in limited scope it does all analysis except sql profiling, in comprehensive scope it does all analysis including sql profiling so if we have enough time for analysis we should go for comprehensive scope.


When the sql tuning advisor is run in auto task in maitenance window its main focus is to pick top high load sql statements based on the top 4 different time periods :- the past week, any day of past week, any hour of past week, or a single response time. It checks the high load statements based on both cpu time and i/o time together. Both the cpu time and I/O time has to be better, if only 1 of them is better oracle ignores that plan for the sql profile,  When checking whether or not to create an sql profile for the statement it checks if the statment can be improved 3 times better than the original execution, so if the statement is getting better 3 times than the original by looking at the benefit factor of cpu time and i/o time together, it recommends for the acceptance of the sql profile, Lets take an example := if a statement is found with a better plan which uses parallelism which takes less i/o time but more cpu consumption in this case it will be rejected because cpu time is worse and is not better than the original plan.


Auto task has the following parameters which can be configured :-

BEGIN
  DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER('SYS_AUTO_SQL_TUNING_TASK','LOCAL_TIME_LIMIT', 1200);
  DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER('SYS_AUTO_SQL_TUNING_TASK','ACCEPT_SQL_PROFILES', 'true');
  DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER('SYS_AUTO_SQL_TUNING_TASK','MAX_SQL_PROFILES_PER_EXEC', 50);
  DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER('SYS_AUTO_SQL_TUNING_TASK','MAX_AUTO_SQL_PROFILES', 10002);
END;
/

The LOCAL_TIME_LIMIT means maximum time devoted for a single statement (1200 seconds) 20 minutes is the default.

The ACCEPT_SQL_PROFILES means accepting sql profile automatically or not, by default it is FALSE.


The MAX_SQL_PROFILES_PER_EXEC means maximum sql profiles acceptance limit for a single task. 20 is the default value.


The MAX_AUTO_SQL_PROFILES means how many profiles getting accepted overall in the oracle database at any point in time. 10000 is the default value.

________________________________

To use the APIs, the user needs at least the ADVISOR privilege.

To view the Automatic SQL Tuning Report for the nightly task, you need the ADVISOR privilege and SELECT privileges on the DBA_ADVISOR views

## Running the sql tuning advisor for a single SQL Statement

SQL> select sql_id from v$sql where sql_text like 'select * from t1%';

SQL_ID

-------------

27uhu2q2xuu7r

Lets create a tuning task with sql tuning advisor with comprehensive scope which means oracle will also analyze whether statement can benefit from sql profile as well. Had it been normal scope it would have only analyzed statistics, access path and sql structure and not sql profiling.

Let us see the execution plan of a select statement with a full table scan.

SQL> select * from T1;


Execution Plan

----------------------------------------------------------

Plan hash value: 3617692013

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |  1159K|   107M|  4471   (1)| 00:00:54 |

|   1 |  TABLE ACCESS FULL| T1   |  1159K|   107M|  4471   (1)| 00:00:54 |

--------------------------------------------------------------------------

SQL> set serveroutput on

   declare

    a varchar2(200);

    begin

    a := dbms_sqltune.create_tuning_Task(

    sql_id=>'27uhu2q2xuu7r',

    scope=>'comprehensive',

    task_name=>'task3',time_limit=>60);

    dbms_output.put_line('task name := '||a);

   end;

   /

SQL>

task name := task3

PL/SQL procedure successfully completed.

In order to see exactly what went in the background we can enable tracing

ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';

EXEC dbms_sqltune.execute_tuning_task('task3');

SELECT SOFAR, TOTALWORK FROM   V$ADVISOR_PROGRESS WHERE  USER_NAME = 'HR' AND TASK_NAME = 'TASK3';

SET linesize 180

SET longchunksize 180

SET pagesize 900

SET long 1000000

SELECT dbms_sqltune.report_tuning_task('task3') FROM dual;

## Accepting an sql profile

EXEC dbms_sqltune.accept_sql_profile(:task_name);

_______________________________________


## Running sql tuning advisor with sql tuning sets

## Advisor and Administer sql tuning set priviges are very critical to give to the user to run the below code.

SQL> set serveroutput on

    declare

    a varchar2(200);

    begin

    a := dbms_sqltune.create_tuning_Task(

    sqlset_name=>'STS1',

    scope=>'comprehensive',

    task_name=>'task6',time_limit=>60);

    dbms_output.put_line('task name := '||a);

   end;

   /

SQL>

task name := task6

PL/SQL procedure successfully completed.


In order to see exactly what went in the background we can enable tracing

ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';


EXEC dbms_sqltune.execute_tuning_task('task6');


SQL> select OPNAME,ADVISOR_NAME,SOFAR,TOTALWORK from V$ADVISOR_PROGRESS ;


OPNAME     ADVISOR_NAME              SOFAR  TOTALWORK

---------- -------------------- ---------- ----------

Advisor    SQL Tuning Advisor           16         16


SET linesize 180

SET longchunksize 180

SET pagesize 900

SET long 1000000

SELECT dbms_sqltune.report_tuning_task('task6') FROM dual;

## Let us see some recommendations made by oracle for some of the sections

FINDINGS SECTION (3 findings)
-------------------------------------------------------------------------------

1- Statistics Finding
---------------------
  Optimizer statistics for table "SYS"."CLU$" and its indices are stale.

  Recommendation
  --------------
  - Consider collecting optimizer statistics for this table.
    execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname =>
            'CLU$', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
            method_opt => 'FOR ALL COLUMNS SIZE AUTO');

  Rationale
  ---------
    The optimizer requires up-to-date statistics for the table in order to
    select a good execution plan.


2- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 19%)
  ---------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name => 'task6', object_id
            => 10, task_owner => 'SYS', replace => TRUE);

  Validation results
  ------------------
  The SQL profile was tested by executing both its plan and the original plan
  and measuring their respective execution statistics. A plan may have been
  only partially executed if the other could be run to completion in less time.

                           Original Plan  With SQL Profile  % Improved
                           -------------  ----------------  ----------
  Completion Status:            COMPLETE          COMPLETE
  Elapsed Time (s):               .0462           .049297       -6.7 %
  CPU Time (s):                 .044793           .043793       2.23 %
  User I/O Time (s):            .003361           .001033      69.26 %
  Buffer Gets:                    19189             15542         19 %
  Physical Read Requests:           229               229          0 %
  Physical Write Requests:            0                 0
  Physical Read Bytes:          1875968           1875968          0 %
  Physical Write Bytes:               0                 0
  Rows Processed:                  3699              3699
  Fetches:                         3699              3699
  Executions:                         1                 1

  Notes
  -----
  1. Statistics for the original plan were averaged over 10 executions.
  2. Statistics for the SQL profile plan were averaged over 10 executions.

3- Alternative Plan Finding
---------------------------
  Some alternative execution plans for this statement were found by searching
  the system's real-time and historical performance data.

  The following table lists these plans ranked by their average elapsed time.
  See section "ALTERNATIVE PLANS SECTION" for detailed information on each
  plan.

  id plan hash  last seen            elapsed (s)  origin          note
  -- ---------- -------------------- ------------ --------------- ----------------
   1 3607810482  2014-08-31/01:23:10        0.450 STS

  Information
  -----------
  - Because no execution history for the Original Plan was found, the SQL
    Tuning Advisor could not determine if any of these execution plans are
    superior to it.  However, if you know that one alternative plan is better
    than the Original Plan, you can create a SQL plan baseline for it. This
    will instruct the Oracle optimizer to pick it over any other choices in
    the future.
    execute dbms_sqltune.create_sql_plan_baseline(task_name => 'task6',
            object_id => 10, owner_name => 'SYS', plan_hash_value =>
            xxxxxxxx);

-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- The optimizer could not merge the view at line ID 1 of the execution plan.
  The optimizer cannot merge a view that contains a set operator.


## With above recommendations it is clear that statistics are stale for CLU$ table and also an sql profile is recommended to accept and also the plan to be pushed in sql plan baseline, what is sql profile is dicussed in this white paper and what is sql plan baseline is discussed in another white paper of mine which you can find on my blog.


___________________________________________________________________________

To view the Automatic SQL Tuning Report for the nightly task, you need the ADVISOR privilege and SELECT privileges on the DBA_ADVISOR views

Lets run the REPORT_AUTO_TUNING_TASK function in the DBMS_SQLTUNE package, the following code will display a report for all SQL Statements that were analayzed in recent execution and also the recommendations will be shown that were not implemented including several sections with details, like for example GENERAL INFORMATION SECTION which displays general information like time of the task exection and also the STATISTICS ANALYSIS SECTION and so on..

variable my_rept CLOB;

BEGIN
  :my_rept :=DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK(
    begin_exec => NULL,
    end_exec => NULL,
    type => 'TEXT',
    level => 'TYPICAL',
    section => 'ALL',
    object_id => NULL,
    result_limit => NULL);
END;
/

print :my_rept

___________________________________________________________________________



To enable the automated task, execute the following PL/SQL block:


BEGIN

  DBMS_AUTO_TASK_ADMIN.ENABLE (

    client_name => 'sql tuning advisor'

,   operation   => NULL

,   window_name => NULL

);

END;

/


To disable the automated task, execute the following PL/SQL block:

BEGIN

  DBMS_AUTO_TASK_ADMIN.DISABLE (

    client_name => 'sql tuning advisor'

,   operation   => NULL

,   window_name => NULL

);

END;

/
______________________________


Query the data dictionary to confirm the change.

For example, query DBA_AUTOTASK_CLIENT as follows :


COL CLIENT_NAME FORMAT a20


SELECT CLIENT_NAME, STATUS FROM   DBA_AUTOTASK_CLIENT WHERE  CLIENT_NAME = 'sql tuning advisor';



CLIENT_NAME          STATUS

-------------------- --------

sql tuning advisor   ENABLED



___________________________________________________



3 comments:

  1. I enjoyed reading your article :) PLease continue publishing helpful topics like this. Regards, from http://strategyandexecution.com.au/

    I think this article will fully complement your article.

    ReplyDelete
  2. Awesome post. You Post is very informative. Thanks for Sharing.
    Oracle Training in Noida

    ReplyDelete