Tuesday 2 September 2014

12C SQL TUNING SETS

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

12C SQL TUNING SETS

August 2014

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

SQL Tuning Set (STS) is like a container which can hold several SQL statements with execution statistics and lot other information inside it, Just like other database objects STS is also a database object, it is basically a database object that hold multiple sql statements and their respective statistics and some other information relative to a sql statement like buffer gets, disk reads, CPU time and so on... more importantly associated execution plans are also stored in sql tuning sets. It is different from other objects in that it is used for tuning and testing purposes like for example bulk loading sql queries in an STS with their plans, compilation environment, execution statistics and other information relative to a SQL Statement and give it to the SQL Tuning Advisor for recommendations if we have the tuning pack available and enabled. Not only sql tuning advisor but we can also use STS for sql access advisor, sql performance analyzer (part of Real Application Testing (RAT) License) and for transportation purposes.

With the command line we can use the DBMS_SQLTUNE package to manage SQL tuning sets. Though we can also use EM DB Console at anytime.

How to create an SQL Tuning Set

SQL> BEGIN

  DBMS_SQLTUNE.create_sqlset (

    sqlset_name  => 'STS_KD',

    description  => 'An SQL tuning set for some testing');

END;

/

PL/SQL procedure successfully completed.

SQL> DECLARE

  v_cur  DBMS_SQLTUNE.sqlset_cursor;

BEGIN

  OPEN v_cur FOR

     SELECT VALUE(my)

     FROM   TABLE(

              DBMS_SQLTUNE.select_cursor_cache(

                basic_filter   => 'parsing_schema_name = ''SCOTT''',

                attribute_list => 'ALL')) my;

         DBMS_SQLTUNE.load_sqlset(sqlset_name => 'STS_KD', populate_cursor => v_cur);

END;

/

PL/SQL procedure successfully completed.

Privileges needed to create and manage sql tuning sets are ADMINISTER SQL TUNING SET and ADMINISTER ANY SQL TUNING SET

_______________________________________

Lets see some description of sql tuning sets from data dictionary

SQL Tuning Set Subprograms

A subprogram in plsql is either a function or a procedure and in context of the package dbms_sqltune, there are few subprograms put inside this package and the code for them is inside their package body.

Subprograms and their Descriptions

ADD_SQLSET_REFERENCE Function := Adds a new reference to an existing SQL tuning set to indicate its use by a client

CAPTURE_CURSOR_CACHE_SQLSET Procedure := Over a specified time interval incrementally captures a workload from the cursor cache into a SQL tuning set

CREATE_SQLSET Procedure and Function := Creates a SQL tuning set object in the database

CREATE_STGTAB_SQLSET Procedure := Creates a staging table through which SQL Tuning Sets are imported and exported

DELETE_SQLSET Procedure := Deletes a set of SQL statements from a SQL tuning set

DROP_SQLSET Procedure := Drops a SQL tuning set if it is not active

LOAD_SQLSET Procedure := Populates the SQL tuning set with a set of selected SQL

PACK_STGTAB_SQLSET Procedure := Copies tuning sets out of the SYS schema into the staging table

REMOVE_SQLSET_REFERENCE Procedure := Deactivates a SQL tuning set to indicate it is no longer used by the client

SELECT_CURSOR_CACHE Function := Collects SQL statements from the cursor cache

SELECT_SQL_TRACE Function := Reads the content of one or more trace files and returns the SQL statements it finds in the format of sqlset_row

SELECT_SQLSET Function := Collects SQL statements from an existing SQL tuning set

SELECT_WORKLOAD_REPOSITORY Functions := Collects SQL statements from the workload repository

UNPACK_STGTAB_SQLSET Procedure := Copies one or more SQL tuning sets from the staging table

UPDATE_SQLSET Procedures := Updates whether selected string fields for a SQL statement in a SQL tuning set or the set numerical attributes of a SQL in a SQL tuning set

_______________________________________

How to run SQL Tuning advisor with SQL Tuning Sets

SQL> set serveroutput on

    declare

    a varchar2(200);

    begin

    a := dbms_sqltune.create_tuning_Task(

    sqlset_name=>'STS_KD',

    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 as well like for example

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


## to display the report we will use the following


SET linesize 200

SET longchunksize 200

SET pagesize 1000

SET long 1000000

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

### There were lot of statements in the sql tuning set so i have trimmed some of them and i have deliberately queried the sys schema object WRH$_ACTIVE_SESSION_HISTORY table which is used in active session history to see if oracle recommends us to improve it further and displayed Statistics Finding section to show that how good sql tuning advisor as it recommends us the solution in order to improve the performance of the statements.


FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------

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

  Recommendation
  --------------
  - Consider collecting optimizer statistics for this table.
    execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname =>
            'WRH$_ACTIVE_SESSION_HISTORY', 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- Statistics Finding
---------------------
  Optimizer statistics for table "SYS"."WRM$_SNAPSHOT" and its indices are
  stale.

  Recommendation
  --------------
  - Consider collecting optimizer statistics for this table.
    execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname =>
            'WRM$_SNAPSHOT', 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.

_______________________________________

Transferring SQL Tuning Sets


First let us create a staging table


BEGIN

  DBMS_SQLTUNE.create_stgtab_sqlset(table_name      => 'TAB_STS',

                                    schema_name     => 'SYSTEM',

                                    tablespace_name => 'USERS');

END;

/
PL/SQL procedure successfully completed.

Now lets pack the staging table with the data of sql tuning set using the  PACK_STGTAB_SQLSET procedure



BEGIN

  DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name          => 'STS_KD',

                                  sqlset_owner         => 'SYS',

                                  staging_table_name   => 'TAB_STS',

                                  staging_schema_owner => 'SYSTEM');

END;

/
PL/SQL procedure successfully completed.


Now we will transport the table with the help of datapump to the destination database.

## Lets export the TAB_STS table at source database

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6.125 MB
.....................................
..........................................
Dump file set for SYSTEM.SYS_EXPORT_TABLE_02 is:
  /u01/app/dirs/d1/expdat.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_02" successfully completed at Sun Aug 31 01:30:12 2014 elapsed 0 00:00:35


## Now lets import the TAB_STS table at destination with datapump

Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=d1
...........
.............
........Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SYSTEM"."TAB_STS"                          2.328 MB    4727 rows
...........................
......................................
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Sun Aug 31 01:33:01 2014 elapsed 0 00:00:41


## Once we import then we can unpack the sql tuning set with the following code



BEGIN

  DBMS_SQLTUNE.unpack_stgtab_sqlset(sqlset_name          => '%',

                                    sqlset_owner         => 'SYS',

                                    replace              => TRUE,

                                    staging_table_name   => 'TAB_STS',

                                    staging_schema_owner => 'SYSTEM');

END;

/

PL/SQL procedure successfully completed.

_______________________________________

When we are upgrading the database from 10g to 11g we usually populate the 10g sql tuning sets with sql statements and then we load the same to 11g sql plan baseline to preserve the performance of the oracle database , how we do it let us see

DECLARE

my_plans pls_integer;

BEGIN

  my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(

    sqlset_name => 'STS_KD');

END;

/
PL/SQL procedure successfully completed.
_______________________________________


To display the contents of an STS:

SQL>COLUMN SQL_TEXT FORMAT a30

SQL>COLUMN SCH FORMAT a3

SQL>COLUMN ELAPSED FORMAT 999999999



SQL> SELECT SQL_ID, PARSING_SCHEMA_NAME AS "SCH", SQL_TEXT,

       ELAPSED_TIME AS "ELAPSED", BUFFER_GETS

FROM   TABLE( DBMS_SQLTUNE.SELECT_SQLSET( 'STS_KD' ) );



SQL_ID        SCH SQL_TEXT                          ELAPSED BUFFER_GETS

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

14pxsqq5qw6kb SCO select * from sales where obje    1048699      315093

              TT  ct_id=1000



2xtm4g9sfzyw0 SCO select * from sales where obje     241277       44436

              TT  ct_id<1000



4d45hfmk7khhp SCO select * from sales where obje      47424        2120

              TT  ct_id>1000



946ddvxj22pnd SCO SELECT /* OPT_DYN_SAMP */ /*+        4498          74

              TT  ALL_ROWS IGNORE_WHERE_CLAUSE N



SQL_ID        SCH SQL_TEXT                          ELAPSED BUFFER_GETS

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

                  O_PARALLEL(SAMPLESUB



agn7ru0spffyj SCO SELECT /* OPT_DYN_SAMP */ /*+        4983          74

              TT  ALL_ROWS IGNORE_WHERE_CLAUSE N

                  O_PARALLEL(SAMPLESUB



cdtjaangp5x17 SCO create table sales as select      2180424        5025

              TT  * from sys.dba_objects



cx0ttf4y4ahxn SCO SELECT /* OPT_DYN_SAMP */ /*+     4472526        4067

              TT  ALL_ROWS IGNORE_WHERE_CLAUSE N



SQL_ID        SCH SQL_TEXT                          ELAPSED BUFFER_GETS

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

                  O_PARALLEL(SAMPLESUB



d6qnn0vpu8xmx SCO SELECT /* OPT_DYN_SAMP */ /*+        8149         122

              TT  ALL_ROWS IGNORE_WHERE_CLAUSE N

                  O_PARALLEL(SAMPLESUB



fcaxkvxh5h4g6 SCO insert into sales select * fro   50204574      370599

              TT  m sales



gwkz0aduy1w41 SCO select * from sales              14774553       65032

              TT



SQL_ID        SCH SQL_TEXT                          ELAPSED BUFFER_GETS

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

10 rows selected.

_______________________________________

Modifying a SQL Tuning Set

Use the basic_filter predicate to filter the SQL from the STS defined on attributes of the SQLSET_ROW. The following example deletes all statements in the STS with fetch counts over 100:


BEGIN

  DBMS_SQLTUNE.DELETE_SQLSET (

      sqlset_name  => 'STS_KD'

,     basic_filter => 'fetches > 100'

);

END;

/

PL/SQL procedure successfully completed.

Set attribute values for SQL statements.


The following example sets the priority of statement 7nr1kb47kw04j to 1:


BEGIN

  DBMS_SQLTUNE.UPDATE_SQLSET (

      sqlset_name     => 'STS_KD'  

,     sql_id          => '7nr1kb47kw04j'  

,     attribute_name  => 'PRIORITY'      

,     attribute_value =>  1

);

END;

/

PL/SQL procedure successfully completed.

_______________________________________


In the case of multitenant architecture Oracle 12C we can transport SQL Tuning Sets from a Non-CDB to a CDB

Whenever we are shipping sql tuning sets from a non-CDB to a CDB database, we must remap the con_dbid of each SQL statement in the STS to a con_dbid within the destination CDB. The steps to do it are as follows:

Firstly pack the STS into a staging table using DBMS_SQLTUNE.PACK_STGTAB_SQLSET, Secondly remap each con_dbid in the staging table using DBMS_SQLTUNE.REMAP_STGTAB_SQLSET, Thirdly export the STS, Fourthly unpack the STS in the destination CDB.


Lets see what is the PL/SQL code and how we can do it, in this example we re remapping con_dbid 1031 to 1032:

BEGIN

  DBMS_SQLTUNE.REMAP_STGTAB_SQLSET (

    staging_table_name   => 'sts_kd1'

,   staging_schema_owner => 'STAGE_USER'

,   old_con_dbid         => 1031

,   new_con_dbid         => 1032

);

END;

/

_______________________________________

In the past versions before 12C there have been some bugs, problems so let us take a brief look at them and see what solution did we implement to solve those bugs and problems.

SQL>  BEGIN

         DBMS_SQLTUNE.drop_sqlset (sqlset_name => 'STS_KD');

      END;

      /

ORA-13757: "SQL Tuning Set" "STS_KD" is active.

ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 2912

ORA-06512: at "SYS.DBMS_SQLTUNE", line 482 ORA-06512: at line 1

#### This is an nnpublished bug 5742773 - SQLTUNE TASKS ARE NOT DROPPED DUE TO PHANTOM REFERENCES.

Make sure that you delete all the Advisor Tasks :

Unpublished bug 5742773 - (SQLTUNE TASKS ARE NOT DROPPED DUE TO PHANTOM REFERENCES)

The above bug is fixed in 11g.

We can also do a workaround of it by doing the following


SQL> delete from wri$_sqlset_references where sqlset_id in (select id from wri$_sqlset_definitions where name ='<name of the SQL Tuning Set>');

SQL>commit;


SQL> BEGIN

     DBMS_SQLTUNE.drop_sqlset (sqlset_name => 'STS_KD');

     END;

/

## The best practise is to upgrade your database to atleast 11.2.0.2 or higher.


Another problem faced by some DBA's for the below code


DBMS_SQLPA.EXECUTE_ANALYSIS_TASK was run with execution_type set to 'COMPARE PERFORMANCE'.


SQL> declare

   ename  varchar2(512);

BEGIN

   ename := dbms_sqlpa.execute_analysis_task(

         task_name => '10gr2_11gr2_spa',

         execution_type => 'COMPARE PERFORMANCE',

         execution_name => 'compare_102_112_io',

         execution_params =>

             dbms_advisor.arglist('COMPARISON_METRIC','USER_IO_TIME','EXECUTION_NAME1','10gr2_STS_trial','EXECUTION_NAME2','11gr2_STS_trial'),

         execution_desc => 'comp_user_io_time');

END;

/


ERROR at line 1:

ORA-13793: invalid comparison metric

ORA-06512: at "SYS.PRVT_ADVISOR", line 2758

ORA-06512: at "SYS.DBMS_ADVISOR", line 241

ORA-06512: at "SYS.DBMS_SQLPA", line 342

ORA-06512: at "SYS.DBMS_SQLPA", line 359

ORA-06512: at line 37


Comparison_metric being compared is: USER_IO_TIME, PARSE_TIME or IO_INTERCONNECT_BYTES SQL Tuning Set was previously converted for the analysis task using 'CONVERT SQLSET'


Following error occurs:

11.2.0.4 and later:

ORA-13793: invalid comparison metric

11.2.0.3 and earlier:

ORA-904: "USER_IO_TIME": invalid identifier


Oracle says this is expected behavior.


At the time of DBMS_SQLPA.EXECUTE_ANALYSIS_TASK  being executed, the information for the metrics above are not yet available in the SQL Tuning Set (ie  DBA_SQLSET_STATEMENTS). An unpublished internal bug has been raised with development who are working on enhancing the statistics for the SQL Tuning Sets: Bug 13946904 MISSING METRICS STATS FOR PARSE AND USER_IO_TIME FOR CONVERTED STS TRIAL

Until then you can workaround the issue by comparing a "TEST EXECUTE" trial with a second "TEST EXECUTE" trial in order to get reliable values for these statistics.

________________________

# Below are the dictionary views relative to sql tuning set

SQL> desc dba_sqlset

 Name                                      Null?    Type

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

 ID                                        NOT NULL NUMBER

 NAME                                      NOT NULL VARCHAR2(30)

 OWNER                                              VARCHAR2(30)

 DESCRIPTION                                        VARCHAR2(256)

 CREATED                                            DATE

 LAST_MODIFIED                                      DATE

 STATEMENT_COUNT                                    NUMBER



SQL> desc  dba_sqlset_statements

 Name                                      Null?    Type

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

 SQLSET_NAME                               NOT NULL VARCHAR2(30)

 SQLSET_OWNER                                       VARCHAR2(30)

 SQLSET_ID                                 NOT NULL NUMBER

 SQL_ID                                    NOT NULL VARCHAR2(13)

 FORCE_MATCHING_SIGNATURE                  NOT NULL NUMBER

 SQL_TEXT                                           CLOB

 PARSING_SCHEMA_NAME                                VARCHAR2(30)

 PARSING_SCHEMA_ID                                  NUMBER

 PLAN_HASH_VALUE                           NOT NULL NUMBER

 BIND_DATA                                          RAW(2000)

 BINDS_CAPTURED                                     CHAR(1)

 MODULE                                             VARCHAR2(48)

 ACTION                                             VARCHAR2(32)

 ELAPSED_TIME                                       NUMBER

 CPU_TIME                                           NUMBER

 BUFFER_GETS                                        NUMBER

 DISK_READS                                         NUMBER

 DIRECT_WRITES                                      NUMBER

 ROWS_PROCESSED                                     NUMBER

 FETCHES                                            NUMBER

 EXECUTIONS                                         NUMBER

 END_OF_FETCH_COUNT                                 NUMBER

 OPTIMIZER_COST                                     NUMBER

 OPTIMIZER_ENV                                      RAW(2000)

 PRIORITY                                           NUMBER

 COMMAND_TYPE                                       NUMBER

 FIRST_LOAD_TIME                                    VARCHAR2(19)

 STAT_PERIOD                                        NUMBER

 ACTIVE_STAT_PERIOD                                 NUMBER

 OTHER                                              CLOB

 PLAN_TIMESTAMP                                     DATE

 SQL_SEQ                                   NOT NULL NUMBER


__________________________________

## References

http://docs.oracle.com/database/121/LNPLS/toc.htm

http://docs.oracle.com/database/121/ADMIN/toc.htm

http://docs.oracle.com/database/121/TGSQL/toc.htm



No comments:

Post a Comment