SQL Access Advisor and SQL Tuning Advisor in 11gR2

Posted on , updated on by Yannick Jaquier
Oracle , ,

Table of contents

Preamble

To help a colleague tuning a complex ad hoc query coming from BO I have decided to use SQL Tuning Advisor and SQL Access Advisor that I have already tested in 10gR2. While doing this I have discovered in official documentation that my previous post was using deprecated procedures so decided to write a new one to refresh the process and also to train me a bit on this wonderful tools that I do not use often. Please note they are including in non-free Enterprise edition tuning pack.

Testing has been done with Oracle 11.2.0.4 running on HPUX 11.31 Itanium…

I have tried to use the simplest and fastest method… This method imply you have already executed once the query, if not then it is still possible but you would proceed differently…

SQL Tuning Set (STS)

First I have executed the query putting a comment to find it easily in library cache with:

SELECT sql_id,child_number,a.plan_hash_value
FROM v$sql a
WHERE sql_text LIKE 'SELECT /* Yannick */%'
AND a.parsing_schema_name='MARS_DBA';
 
SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE
------------- ------------ ---------------
f397tskua83ky            0       170285335

The (complex) production plan can be seen with:

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('f397tskua83ky',0,'ALL'));

I create the SQL Tuning Set (STS) called STS01:

SQL> EXEC DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => 'STS01', description => 'STS for sql_id f397tskua83ky');
 
PL/SQL PROCEDURE successfully completed.

I load inside the SQL Tuning Set (STS) my sql_id with:

SQL> DECLARE
  cursor1 DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
  OPEN cursor1 FOR SELECT VALUE(p)
  FROM TABLE(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''f397tskua83ky'' and plan_hash_value=''170285335''')) p;
 
  DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'STS01', populate_cursor => cursor1);
  CLOSE cursor1;
END;
/
 
PL/SQL PROCEDURE successfully completed.

You can control all is fine with:

SQL> SELECT sql_id, plan_hash_value FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET('STS01'));
 
SQL_ID        PLAN_HASH_VALUE
------------- ---------------
f397tskua83ky       170285335

SQl Tuning Advisor (STA)

I create the SQL Tuning Advisor task (STA01) starting from the STS we have just created, multiple options are possible please refer to official documentation:

SQL> DECLARE
  stmt_task VARCHAR2(64);
BEGIN
  stmt_task:=dbms_sqltune.create_tuning_task(sqlset_name => 'STS01', time_limit => 3600, task_name => 'STA01', description => 'Task to tune sql_id f397tskua83ky');
END;
/
 
PL/SQL PROCEDURE successfully completed.

Let’s execute the task:

SQL> EXECUTE dbms_sqltune.execute_tuning_task('STA01');
 
PL/SQL PROCEDURE successfully completed.

To see the result, use set clobsize 999999 to display result in SQL Developer using F5 function key:

SQL> SET linesize 150 LONG 999999 pages 1000 longchunksize 999999
SQL> SELECT dbms_sqltune.report_tuning_task('STA01') FROM dual;
 
DBMS_SQLTUNE.REPORT_TUNING_TASK('STA01')
------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : STA01
Tuning Task Owner                 : YJAQUIER
Workload TYPE                     : SQL Tuning SET
Scope                             : COMPREHENSIVE
TIME Limit(seconds)               : 3600
Completion Status                 : COMPLETED
Started AT                        : 04/30/2015 17:21:52
Completed AT                      : 04/30/2015 17:22:28
SQL Tuning SET (STS) Name         : STS01
SQL Tuning SET Owner              : YJAQUIER
NUMBER OF Statements IN the STS   : 1
 
-------------------------------------------------------------------------------
There are no recommendations TO improve the statements IN the workload.
 
-------------------------------------------------------------------------------

Okay a bit disappointed, let’s move to SQL Access Advisor.

SQL Access Advisor (SAA)

You may, as well, use the DBMS_ADVISOR.QUICK_TUNE procedure. The part has been completely re-branded as all procedures working with workload have been deprecated so now it is much simpler if you start from a SQL Tuning Set (STS):

SQL> DECLARE
  task_id NUMBER;
  task_name VARCHAR2(30);
  sts_name VARCHAR2(30);
BEGIN
  task_name := 'SAA01';
  sts_name:='STS01';
 
  DBMS_ADVISOR.CREATE_TASK('SQL Access Advisor', task_id, task_name, 'Task for sql_id f397tskua83ky');
  DBMS_ADVISOR.ADD_STS_REF(task_name, 'YJAQUIER', sts_name);
  DBMS_ADVISOR.EXECUTE_TASK(task_name);
END;
/
DECLARE
*
ERROR AT line 1:
ORA-32342: The EXPLAIN_MVIEW facility failed TO EXPLAIN the MATERIALIZED VIEW statement
ORA-01031: insufficient PRIVILEGES
ORA-06512: AT "SYS.DBMS_ADVISOR", line 201
ORA-06512: AT line 13

As I have used my personal DBA account I have retried with SYS user but still same error. Not being able to find a single reference on this on Google or MOS so decided to deactivate the materialized view part with:

SQL> EXEC DBMS_ADVISOR.RESET_TASK('SAA01');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC DBMS_ADVISOR.SET_TASK_PARAMETER('SAA01','ANALYSIS_SCOPE','INDEX, TABLE, PARTITION');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC DBMS_ADVISOR.EXECUTE_TASK('SAA01');
 
PL/SQL PROCEDURE successfully completed.

Nothing new in 11gR2 the DBMS_ADVISOR.GET_TASK_REPORT is still not working so you may use the DBMS_ADVISOR.GET_TASK_SCRIPT procedure to generate a script or the show_recm procedure found in official documentation:

SQL> SET serveroutput ON SIZE 999999
SQL> EXEC show_recm('SAA01');
=========================================
Task_name = SAA01
Action ID: 1
Command : RETAIN INDEX
Attr1 (name)      : "SA_WREF"."ACUST_SHIP_TO_IDX_C
Attr2 (tablespace):
Attr3             : "SA_WREF"."ACUSTOMER_SHIP_TO"
Attr4             : BTREE
Attr5             :
----------------------------------------
Action ID: 2
Command : RETAIN INDEX
Attr1 (name)      : "E2DWH"."AFINISHED_GOOD_NEW_PK
Attr2 (TABLESPACE):
Attr3             : "E2DWH"."AFINISHED_GOOD_NEW"
Attr4             : BTREE
Attr5             :
----------------------------------------
Action ID: 3
Command : RETAIN INDEX
Attr1 (name)      : "E2DWH"."AFINISHED_GOOD_IDX_CP
Attr2 (tablespace):
Attr3             : "E2DWH"."AFINISHED_GOOD_NEW"
Attr4             : BTREE
Attr5             :
----------------------------------------
Action ID: 4
Command : RETAIN INDEX
Attr1 (name)      : "SA_WREF"."FG_ACUST_ASSOCN_IDX
Attr2 (TABLESPACE):
Attr3             : "SA_WREF"."FG_ACUST_ASSOCN"
Attr4             : BTREE
Attr5             :
----------------------------------------
Action ID: 5
Command : RETAIN INDEX
Attr1 (name)      : "SA_WREF"."FG_ACUST_ASSOCN_IDX
Attr2 (tablespace):
Attr3             : "SA_WREF"."FG_ACUST_ASSOCN"
Attr4             : BTREE
Attr5             :
----------------------------------------
Action ID: 6
Command : RETAIN INDEX
Attr1 (name)      : "MARSDBA"."DIMX_TIME_IDX_MM"
Attr2 (tablespace):
Attr3             : "MARSDBA"."DIMX_TIME"
Attr4             : BTREE
Attr5             :
----------------------------------------
Action ID: 7
Command : RETAIN INDEX
Attr1 (name)      : "MARSDBA"."DIMX_TIME_QQ"
Attr2 (tablespace):
Attr3             : "MARSDBA"."DIMX_TIME"
Attr4             : BTREE
Attr5             :
----------------------------------------
Action ID: 8
Command : RETAIN INDEX
Attr1 (name)      : "SA_WREF"."WACUST_SHIP_TO_IDX_
Attr2 (TABLESPACE):
Attr3             : "SA_WREF"."WACUSTOMER_SHIP_TO"
Attr4             : BTREE
Attr5             :
----------------------------------------
Action ID: 9
Command : RETAIN INDEX
Attr1 (name)      : "MARSDBA"."MRS_WLY_IDX_WEEK"
Attr2 (TABLESPACE):
Attr3             : "MARSDBA"."MRS_WLY"
Attr4             : BTREE
Attr5             :
----------------------------------------
Action ID: 10
Command : RETAIN INDEX
Attr1 (name)      : "DWHDBA"."USER_X_ORG_IDX_NAME"
Attr2 (TABLESPACE):
Attr3             : "DWHDBA"."USER_X_ORG"
Attr4             : BTREE
Attr5             :
----------------------------------------
Action ID: 11
Command : CREATE INDEX
Attr1 (name)      : "MARSDBA"."DIM_WEEK_IDX$$_D936
Attr2 (tablespace):
Attr3             : "MARSDBA"."DIM_WEEK"
Attr4             : BTREE
Attr5             :
----------------------------------------
Action ID: 12
Command : CREATE INDEX
Attr1 (name)      : "MARSDBA"."DIM_WEEK_IDX$$_D936
Attr2 (TABLESPACE):
Attr3             : "MARSDBA"."DIM_WEEK"
Attr4             : BTREE
Attr5             :
----------------------------------------
=========END RECOMMENDATIONS============
 
PL/SQL PROCEDURE successfully completed.

Creating the two recommended indexes on small dimension DIM_WEEK table is obviously not helping as Oracle was already doing a Full Table Scan (FTS) on it…

Conclusion

After deeper investigation I have discovered that a AFTER LOGON ON DATABASE trigger is doing:

ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='9.2.0';

Using an ALTER Session to revert back to 11gR2 optimizer has move the query execution time from 1 hour and 45 minutes to 2 minutes…

To clean everything you may use:

SQL> EXEC DBMS_SQLTUNE.DROP_TUNING_TASK('STA01');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC DBMS_ADVISOR.DELETE_TASK('SAA01');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC DBMS_SQLTUNE.DROP_SQLSET('STS01');
 
PL/SQL PROCEDURE successfully completed.

About Post Author

Yannick Jaquier

administrator

Find more about me on social media.

See author's posts

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>