SQL Access Advisor versus SQL Tuning Advisor

Preamble

After my example on DBMS_SQLTUNE I wanted to investigate another advisors i.e. SQL Access Advisor (SAA ?). So entered in DBMS_ADVISOR official documentation and found that list of available advisor can be obtained querying DBA_ADVISOR_DEFINITIONS:

SQL> SET lines 150
SQL> SET pages 1000
SQL> SELECT * FROM dba_advisor_definitions;
 
ADVISOR_ID ADVISOR_NAME                     PROPERTY
---------- ------------------------------ ----------
         1 ADDM                                    1
         2 SQL ACCESS Advisor                     15
         3 Undo Advisor                            1
         4 SQL Tuning Advisor                      7
         5 SEGMENT Advisor                         3
         6 SQL Workload Manager                    0
         7 Tune MView                             31
 
7 ROWS selected.

So we see that’s it’s possible to issue a SQL Tuning Advisor (STA) using DBMS_ADVISOR, while *normal* package is DBMS_SQLTUNE. Hum, confused, but looking deeper, DBMS_SQLTUNE has not only been designed for STA and can be used for:

  • SQL Tuning Advisor Subprograms
  • SQL Profile Subprograms
  • SQL Tuning Set Subprograms

Then what’s the difference between STA & SAA, I had to come back to official documentation:

  • SQL Access Advisor recommends proper set of materialized views, materialized view logs, and indexes (bitmap, function-based, and B-tree indexes) for a given workload. SQL Access Advisor also recommends how to optimize materialized views so that they can be fast refreshable and take advantage of general query rewrite.
  • SQL Tuning Advisor recommends collection of statistics on objects, creation of new indexes, restructuring of the SQL statement, or creation of SQL Profile.

Let’s try both on a concrete SQL that is not performing very well to compare their respective recommendations For this example I ll be using my HPUX PARISC 11iv1 server with Oracle Database Enterprise Edition 10.2.0.3.0 (Documentum database).

You can additionally know if you are only one playing with this on your database or not:

SQL> SET lines 150
SQL> SELECT a.*, b.*
     FROM dba_advisor_definitions a, dba_advisor_usage b
     WHERE a.advisor_id=b.advisor_id;
 
ADVISOR_ID ADVISOR_NAME                     PROPERTY ADVISOR_ID LAST_EXEC  NUM_EXECS
---------- ------------------------------ ---------- ---------- --------- ----------
         1 ADDM                                    1          1 27-SEP-11         11
         2 SQL ACCESS Advisor                     15          2 26-OCT-11          0
         3 Undo Advisor                            1          3 18-JUN-09          0
         4 SQL Tuning Advisor                      7          4 20-OCT-11         23
         5 SEGMENT Advisor                         3          5 26-OCT-11        775
         6 SQL Workload Manager                    0          6 18-JUN-09          0
         7 Tune MView                             31          7 18-JUN-09          0
 
7 ROWS selected.

SQL Access Advisor

Advisor is accessible through DBMS_ADVISOR package and the global procedure is:

  1. Create a task.
  2. Create a workload and associate it to task.
  3. Add SQL statement(s) to workload.
  4. Execute the task.
  5. Get task result and script.

SQL Tuning Set

When looking at procedure to add SQL statements (DBMS_ADVISOR.ADD_SQLWKLD_STATEMENT) you will see that Oracle is expecting you to directly provide the SQL text of your problematic query (without bind variables for better quality of recommendation they claim) which is not very convenient if you got the sql_id (and plan_hash_value) from Grid Control top activity tab or from AWR/ADDM. DBMS_ADVISOR.IMPORT_SQLWKLD_SQLCACHE procedure (and other similar ones) does not help too as the DBMS_ADVISOR.SET_SQLWKLD_PARAMETER procedure does not allow to filter on sql_id.

In below example I’ll be using sql_id 45zw5bhb0135a and plan_hash_value 1654789573.

To be able to supply directly a sql_id we need to create a SQL Tuning Set (STS), that can also be used by SQL Tuning Advisor:

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

Then to load SQL statements in your STS you have two options:

  • CAPTURE_CURSOR_CACHE_SQLSET: Over a specified time interval incrementally captures a workload from the cursor cache into a SQL tuning set
  • LOAD_SQLSET: Populates the SQL tuning set with a set of selected SQL

To use LOAD_SQLSET function you can use one of the three below functions:

  • SELECT_CURSOR_CACHE: Collects SQL statements from the cursor cache
  • SELECT_SQLSET: Collects SQL statements from an existing SQL tuning set
  • SELECT_WORKLOAD_REPOSITORY: Collects SQL statements from the workload repository

As I already have my sql_id (still active) I’ll use SELECT_CURSOR_CACHE, the pair sql_id and plan_hash_value represent an unique SQL statement. The basic_filter column is a SQLSET_ROW and do provide a quite exhaustive list of options to filter statement:

CREATE TYPE sqlset_row AS object (
  sql_id                   VARCHAR(13),
  force_matching_signature NUMBER,
  sql_text                 CLOB,
  object_list              sql_objects,
  bind_data                RAW(2000),
  parsing_schema_name      VARCHAR2(30),
  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(1000),
  priority                 NUMBER,
  command_type             NUMBER,
  first_load_time          VARCHAR2(19),
  stat_period              NUMBER,
  active_stat_period       NUMBER,
  other                    CLOB,
  plan_hash_value          NUMBER,
  sql_plan                 sql_plan_table_type,
  bind_list                sql_binds)

So let’s create the STS with our SQL statement uniquely identified with its sql_id and plan_hash_value:

DECLARE
  cursor1 DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
  OPEN cursor1 FOR SELECT VALUE(p)
  FROM TABLE(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''45zw5bhb0135a'' and plan_hash_value=''1654789573''')) p;
 
  DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'STS01', populate_cursor => cursor1);
END;
/

You can crosscheck with:

SQL> col description FOR a30
SQL> SELECT * FROM dba_sqlset;
 
        ID NAME                           OWNER                          DESCRIPTION                    CREATED   LAST_MODI STATEMENT_COUNT
---------- ------------------------------ ------------------------------ ------------------------------ --------- --------- ---------------
         1 STS01                          SYS                            STS FOR sql_id 45zw5bhb0135a   26-OCT-11 26-OCT-11               1
 
SQL> SELECT sqlset_name, sql_id, plan_hash_value FROM dba_sqlset_statements;
 
SQLSET_NAME                    SQL_ID        PLAN_HASH_VALUE
------------------------------ ------------- ---------------
STS01                          45zw5bhb0135a      1654789573
 
SQL> SELECT sql_id, plan_hash_value FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET('STS01'));
 
SQL_ID        PLAN_HASH_VALUE
------------- ---------------
45zw5bhb0135a      1654789573

Workload and task

Now we have our STS we can then create the task, create the SQL Workload and make the link between the task and the SQL Workload. Finally the task can be executed:

DECLARE
  task_id NUMBER;
  task_name VARCHAR2(30);
  workload_name VARCHAR2(30);
  saved NUMBER;
  failed NUMBER;
BEGIN
  task_name := 'Task01';
  workload_name := 'Workload01';
 
  DBMS_ADVISOR.CREATE_TASK('SQL Access Advisor', task_id, task_name, 'Task for sql_id 45zw5bhb0135a');
  DBMS_ADVISOR.CREATE_SQLWKLD(workload_name, 'Workload for sql_id 45zw5bhb0135a');
  DBMS_ADVISOR.ADD_SQLWKLD_REF(task_name, workload_name);
  DBMS_ADVISOR.IMPORT_SQLWKLD_STS(workload_name, 'STS01', 'NEW', 1, saved, failed);
  DBMS_ADVISOR.EXECUTE_TASK(task_name);
END;
/

To see that link between task and workload you have multiple available views.

List of SQL statements of your workload (in line with unique SELECT statement I added):

SQL> SELECT workload_name,num_select_stmt,num_update_stmt,num_delete_stmt,num_insert_stmt,num_merge_stmt
     FROM DBA_ADVISOR_SQLW_SUM
     WHERE workload_name='Workload01';
 
WORKLOAD_NAME                  NUM_SELECT_STMT NUM_UPDATE_STMT NUM_DELETE_STMT NUM_INSERT_STMT NUM_MERGE_STMT
------------------------------ --------------- --------------- --------------- --------------- --------------
Workload01                                   1               0               0               0              0

List of involved tables per statement. Sql_id here is not the real sql_id but just an identifier: confusing. To link this sql identifier and statements you have to use DBA_ADVISOR_SQLA_STMTS.SQL_TEXT column:

SQL> SELECT sql_id,table_owner,table_name
     FROM DBA_ADVISOR_SQLW_TABLES
     WHERE workload_name='Workload01';
 
    SQL_ID TABLE_OWNER                    TABLE_NAME
---------- ------------------------------ ------------------------------
         1 WCMS                           DM_DOCUMENT_SP
         1 WCMS                           DM_DOCUMENT_S
         1 WCMS                           DM_SYSOBJECT_S
         1 WCMS                           DM_SYSOBJECT_R

If you added lots of SQL statements to your workload you can filter them using below parameters and DBMS_ADVISOR.SET_SQLWKLD_PARAMETER or DBMS_ADVISOR.SET_DEFAULT_SQLWKLD_PARAMETER procedures, for a complete list of parameters you can use:

SQL> col PARAMETER_VALUE FOR a20
SQL> col DESCRIPTION FOR a80
SQL> SET pages 100
SQL> SELECT parameter_name,parameter_value,description
     FROM DBA_ADVISOR_SQLW_PARAMETERS
     WHERE workload_name='Workload01';

You can get result of parameters used to filter statements of your workload using (nothing filtered in my case):

SQL> col JOURNAL_ENTRY FOR a100
SQL> SELECT journal_entry_type, journal_entry
     FROM DBA_ADVISOR_SQLW_JOURNAL
     WHERE workload_name='Workload01'
     ORDER BY journal_entry_seq;
 
JOURNAL_ENTR JOURNAL_ENTRY
------------ ----------------------------------------------------------------------------------------------------
INFORMATION  Importing workload FROM SQL Tuning SET STS01
INFORMATION  Filter Summary: Valid username:  Unused
INFORMATION  Filter Summary: Invalid username:  Unused
INFORMATION  Filter Summary: Valid module:  Unused
INFORMATION  Filter Summary: Invalid module:  Unused
INFORMATION  Filter Summary: Valid action:  Unused
INFORMATION  Filter Summary: Invalid action:  Unused
INFORMATION  Filter Summary: Valid SQL String:  Unused
INFORMATION  Filter Summary: Invalid SQL String:  Unused
INFORMATION  Filter Summary: Invalid START TIME:  Unused
INFORMATION  Filter Summary: Invalid END TIME:  Unused
INFORMATION  Filter Summary: Invalid TABLE:  Unused
INFORMATION  Filter Summary: Valid TABLE:  Unused
INFORMATION  Filter Summary: Invalid TABLE reference:  Statements discarded: 0
INFORMATION  Filter Summary: SQL Syntax OR Semantic Error:  Statements discarded: 0
INFORMATION  Filter Summary: Invalid COMMENT:  Statements discarded: 0
INFORMATION  Filter Summary: Missing STATISTICS:  Statements invalidated: 0
INFORMATION  Workload processing completed: 1 scanned  1 accepted  0 discarded
 
18 ROWS selected.

Mapping between task and workload(s):

SQL> SELECT * FROM DBA_ADVISOR_SQLA_WK_MAP;
 
OWNER                             TASK_ID TASK_NAME                      WORKLOAD_ID WORKLOAD_NAME
------------------------------ ---------- ------------------------------ ----------- ------------------------------
SYS                                 58078 Task01                               58079 Workload01

Parameters can also be changed at task level. We can see below complete list of parameters available with graphical Grid Control interface (example MODE can be LIMITED or COMPREHENSIVE). Parameters to be changed with DBMS_ADVISOR.SET_TASK_PARAMETER:

SQL> col PARAMETER_VALUE FOR a20
SQL> col DESCRIPTION FOR a80
SQL> SET pages 100
SQL> SELECT parameter_name,parameter_value,is_default,description
     FROM DBA_ADVISOR_PARAMETERS
     WHERE task_name='Task01';

Those parameters can be set to default values using DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER. Use below query to get their values:

SELECT * FROM DBA_ADVISOR_DEF_PARAMETERS WHERE advisor_name='SQL Access Advisor';

Result

To finally arrive to status of our SAA task:

SQL> col execution_time FOR a50
SQL> col STATUS_MESSAGE FOR a60
SQL> SELECT TO_CHAR(execution_start,'dd-mon-yyyy hh24:mi:ss') || ' -> ' || TO_CHAR(execution_end,'dd-mon-yyyy hh24:mi:ss') AS execution_time,
     status,status_message,pct_completion_time
     FROM DBA_ADVISOR_TASKS
     WHERE task_name='Task01';
 
EXECUTION_TIME                                     STATUS      STATUS_MESSAGE                                               PCT_COMPLETION_TIME
-------------------------------------------------- ----------- ------------------------------------------------------------ -------------------
26-oct-2011 17:36:08 -> 26-oct-2011 17:36:12       COMPLETED   ACCESS advisor execution completed                                           100
 
SQL> SELECT TO_CHAR(execution_start,'dd-mon-yyyy hh24:mi:ss') || ' -> ' || TO_CHAR(execution_end,'dd-mon-yyyy hh24:mi:ss') AS execution_time,
     status,status_message,pct_completion_time
     FROM DBA_ADVISOR_LOG
     WHERE task_name='Task01';
 
EXECUTION_TIME                                     STATUS      STATUS_MESSAGE                                               PCT_COMPLETION_TIME
-------------------------------------------------- ----------- ------------------------------------------------------------ -------------------
26-oct-2011 17:36:08 -> 26-oct-2011 17:36:12       COMPLETED   ACCESS advisor execution completed                                           100

To be honest I do not really see the difference between DBA_ADVISOR_LOG and DBA_ADVISOR_TASKS, except one is suppose to give current status…

Once the workload has been tuned by SAA you can get performance figures (again sql_id here is not the real sql_id and you have to use SQL_TEXT CLOB column to remap with real sql_id: not very convenient):

SQL> SELECT username,sql_id,cpu_time,buffer_gets,disk_reads,elapsed_time,rows_processed,executions
     FROM DBA_ADVISOR_SQLA_WK_STMTS
     WHERE task_name='Task01'
     AND workload_name='Workload01';
 
USERNAME                           SQL_ID   CPU_TIME BUFFER_GETS DISK_READS ELAPSED_TIME ROWS_PROCESSED EXECUTIONS
------------------------------ ---------- ---------- ----------- ---------- ------------ -------------- ----------
WCMS                                    1 3438822609   371124676   11651139   7666170955              0         87

Below query gives the potential improvement if applying the recommendation (close to 19% improvement):

SQL> SELECT sql_id , precost, postcost,ROUND((precost-postcost)*100/precost,1) AS percentage_benefit,importance
     FROM DBA_ADVISOR_SQLA_WK_STMTS
     WHERE task_name='Task01' AND workload_name='Workload01';
 
    SQL_ID    PRECOST   POSTCOST PERCENTAGE_BENEFIT IMPORTANCE
---------- ---------- ---------- ------------------ ----------
        12      20340      16560               18.6        100

In line with what we can get with Grid Control:

sta01
sta01

You can have a first overview of what has been suggested in the task:

SQL> col JOURNAL_ENTRY FOR a100
SQL> SELECT journal_entry_type,journal_entry
     FROM DBA_ADVISOR_JOURNAL
     WHERE task_name='Task01'
     ORDER BY journal_entry_seq;
 
JOURNAL_ENTR JOURNAL_ENTRY
------------ ----------------------------------------------------------------------------------------------------
INFORMATION  Preparing workload FOR analysis
INFORMATION  Filter Summary: Valid username:  Unused
INFORMATION  Filter Summary: Invalid username:  Unused
INFORMATION  Filter Summary: Valid module:  Unused
INFORMATION  Filter Summary: Invalid module:  Unused
INFORMATION  Filter Summary: Valid action:  Unused
INFORMATION  Filter Summary: Invalid action:  Unused
INFORMATION  Filter Summary: Valid SQL String:  Unused
INFORMATION  Filter Summary: Invalid SQL String:  Unused
INFORMATION  Filter Summary: Invalid START TIME:  Unused
INFORMATION  Filter Summary: Invalid END TIME:  Unused
INFORMATION  Filter Summary: Invalid TABLE:  Unused
INFORMATION  Filter Summary: Valid TABLE:  Unused
INFORMATION  Filter Summary: Invalid TABLE reference:  Statements discarded: 0
INFORMATION  Filter Summary: SQL Syntax OR Semantic Error:  Statements discarded: 0
INFORMATION  Filter Summary: Invalid COMMENT:  Statements discarded: 0
INFORMATION  Filter Summary: Missing STATISTICS:  Statements discarded: 0
INFORMATION  Workload processing completed: 1 scanned  1 accepted  0 discarded
INFORMATION  No existing MATERIALIZED views were found
 
19 ROWS selected.

Then to get complete recommendation I started to use:

SQL> SET LONG 100000
SQL> SET PAGESIZE 50000
SQL> SELECT DBMS_ADVISOR.get_task_report('Task01') AS report FROM dual;
ERROR:
ORA-13699: Advisor feature IS NOT currently implemented.
ORA-06512: AT "SYS.PRVT_ADVISOR", line 2043
ORA-06512: AT "SYS.DBMS_ADVISOR", line 560
ORA-06512: AT line 1
 
 
 
no ROWS selected

No references of this error in My Oracle Support (MOS) and few tips while using Google. Apparently we have to use either DBA views or generate files, even confirmed in Oracle Database Performance Tuning Guide…

Problem with this query is the non user-friendly approach:

SELECT command,attr1 || ' ' || attr2 || ' ' || attr3 || ' ' || attr4 || ' ' || attr5 || ' ' || attr6 AS recommendation
FROM DBA_ADVISOR_ACTIONS
WHERE task_name='Task01'
ORDER BY action_id;

Oracle do provide a procedure call show_recm that do a pretty good job (refer to official documentation in Oracle Database Performance Tuning Guide for source code). I personally suggest to slightly modify it to extend the display of v_attrx variables:

SQL> SET serveroutput ON SIZE 99999
SQL> EXECUTE show_recm('Task01');
=========================================
Task_name = Task01
Action ID: 1
Command : CREATE INDEX
Attr1 (name)      : "WCMS"."DM_SYSOBJECT_R_IDX$$_E2DE0001"
Attr2 (TABLESPACE):
Attr3             : "WCMS"."DM_SYSOBJECT_R"
Attr4             : BITMAP
Attr5             :
----------------------------------------
Action ID: 2
Command : CREATE INDEX
Attr1 (name)      : "WCMS"."DM_SYSOBJECT_R_IDX$$_E2DE0002"
Attr2 (TABLESPACE):
Attr3             : "WCMS"."DM_SYSOBJECT_R"
Attr4             : BTREE
Attr5             :
----------------------------------------
Action ID: 3
Command : RETAIN INDEX
Attr1 (name)      : "WCMS"."D_1F00342380000147"
Attr2 (TABLESPACE):
Attr3             : "WCMS"."DM_DOCUMENT_S"
Attr4             : BTREE
Attr5             :
----------------------------------------
Action ID: 4
Command : RETAIN INDEX
Attr1 (name)      : "WCMS"."D_1F0034238000010A"
Attr2 (TABLESPACE):
Attr3             : "WCMS"."DM_SYSOBJECT_S"
Attr4             : BTREE
Attr5             :
----------------------------------------
=========END RECOMMENDATIONS============
 
PL/SQL PROCEDURE successfully completed.

But as suggested by Oracle the best is to generate file in directory. We see that Oracle suggest creation of two indexes and two retain two already existing:

SQL> CREATE DIRECTORY tmp AS '/tmp';
 
DIRECTORY created.
 
SQL> EXEC DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT('Task01'), 'TMP', 'task01.sql');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> !cat /tmp/task01.SQL
Rem  SQL ACCESS Advisor: Version 10.2.0.3.0 - Production
Rem
Rem  Username:        SYS
Rem  Task:            Task01
Rem  Execution DATE:  26/10/2011 17:36
Rem
 
CREATE BITMAP INDEX "WCMS"."DM_SYSOBJECT_R_IDX$$_E2DE0001"
    ON "WCMS"."DM_SYSOBJECT_R"
    ("R_VERSION_LABEL")
    COMPUTE STATISTICS;
 
CREATE INDEX "WCMS"."DM_SYSOBJECT_R_IDX$$_E2DE0002"
    ON "WCMS"."DM_SYSOBJECT_R"
    ("R_OBJECT_ID")
    COMPUTE STATISTICS;
 
/* RETAIN INDEX "WCMS"."D_1F00342380000147" */
 
/* RETAIN INDEX "WCMS"."D_1F0034238000010A" */

Of course implementation of recommendations deserves deep investigation as, for example, creation a bitmap index in an OLTP environment is not free of charge… If you want to generate a script with a restricted list of recommendations you can use DBMS_ADVISOR.MARK_RECOMMENDATION procedure.

Remark:
Strangely the first table you would look in does not have any entry for our task:

SQL> SELECT * FROM DBA_ADVISOR_FINDINGS WHERE task_name='Task01';
 
no ROWS selected

Then you may then wish to delete the task with:

SQL> EXEC DBMS_ADVISOR.DELETE_TASK('Task01');
 
PL/SQL PROCEDURE successfully completed.

SQL Tuning Advisor

Task

As I already have the workload created the PL/SQL code is something like:

SQL> DECLARE
       task_id NUMBER;
       task_name VARCHAR2(30);
       task_name VARCHAR2(30);
       workload_name VARCHAR2(30);
       saved NUMBER;
       failed NUMBER;
     BEGIN
       task_name := 'Task02';
       workload_name := 'Workload01';
       DBMS_ADVISOR.CREATE_TASK('SQL Tuning Advisor', task_id, task_name, 'Task for sql_id 45zw5bhb0135a');
       DBMS_ADVISOR.ADD_SQLWKLD_REF(task_name, workload_name);
       DBMS_ADVISOR.CREATE_TASK('SQL Tuning Advisor', task_id, task_name, 'Task for sql_id 45zw5bhb0135a');
       DBMS_ADVISOR.ADD_SQLWKLD_REF(task_name, workload_name);
       DBMS_ADVISOR.EXECUTE_TASK(task_name);
     END;
     /
DECLARE
*
ERROR AT line 1:
ORA-13785: missing target object FOR tuning task "Task02"
ORA-06512: AT "SYS.PRVT_ADVISOR", line 1624
ORA-06512: AT "SYS.DBMS_ADVISOR", line 186
ORA-06512: AT line 13

I initially thought it was linked to reuse of same workload, so decided to create a second workload (Workload02) based on same SQL Tuning Set (STS01), but got exact same error message…

Wanted to clean Task02 and Workload02 objects, did it same way as MOS note in references section. But deleting a workload with an existing task is not working, deleting the task first does not allow too to delete the workload:

SQL> EXEC DBMS_ADVISOR.DELETE_SQLWKLD_REF('Task02','Workload02');
 
PL/SQL PROCEDURE successfully completed.
 
SQL>  EXEC DBMS_ADVISOR.DELETE_SQLWKLD('Workload02');
BEGIN DBMS_ADVISOR.DELETE_SQLWKLD('Workload02'); END;
 
*
ERROR AT line 1:
ORA-13600: error encountered IN Advisor
QSM-00756: the specified workload Workload02 IS being used BY one OR more advisor tasks
ORA-06512: AT "SYS.PRVT_ADVISOR", line 1386
ORA-06512: AT "SYS.DBMS_ADVISOR", line 1476
ORA-06512: AT line 1
 
 
SQL> EXEC DBMS_ADVISOR.DELETE_TASK('Task02');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC DBMS_ADVISOR.DELETE_SQLWKLD('Workload02');
BEGIN DBMS_ADVISOR.DELETE_SQLWKLD('Workload02'); END;
 
*
ERROR AT line 1:
ORA-13600: error encountered IN Advisor
QSM-00756: the specified workload Workload02 IS being used BY one OR more advisor tasks
ORA-06512: AT "SYS.PRVT_ADVISOR", line 1386
ORA-06512: AT "SYS.DBMS_ADVISOR", line 1476
ORA-06512: AT line 1

I also got many issue with DBMS_ADVISOR.IMPORT_SQLWKLD_STS and DBMS_ADVISOR.DELETE_SQLWKLD_STATEMENT procedures and import_mode used. I initially perform many test with REPLACE that is creating issue, so I strongly suggest keeping default value (NEW) unless you may have many strange error like:

DECLARE
*
ERROR AT line 1:
ORA-01403: no data found
ORA-06512: AT "SYS.PRVT_WORKLOAD", line 1658
ORA-06512: AT "SYS.DBMS_ADVISOR", line 1569
ORA-06512: AT line 13

Apparently Oracle is very strict (buggy I would say) when manipulating workload objects. So better to not re-import the STS in your workload and use it as it is i.e. not even try to delete statement from it:

DECLARE
  task_id NUMBER;
  task_name VARCHAR2(30);
  workload_name VARCHAR2(30);
BEGIN
  task_name := 'Task01';
  workload_name := 'Workload01';
 
  DBMS_ADVISOR.CREATE_TASK('SQL Tuning Advisor', task_id, task_name, 'Task for sql_id 45zw5bhb0135a');
  DBMS_ADVISOR.ADD_SQLWKLD_REF(task_name, workload_name);
  DBMS_ADVISOR.EXECUTE_TASK(task_name);
END;
/

Remark:
I opened a service request for this workload story, so to be updated soon…

But again not possible so it explains why Oracle created DBMS_SQLTUNE package. Let’s use it, going quick as we have already seen it in a previous post:

SQL> DECLARE
       stmt_task VARCHAR2(64);
     BEGIN
       stmt_task:=dbms_sqltune.create_tuning_task(sql_id => '45zw5bhb0135a', plan_hash_value => '1654789573', time_limit => 3600, task_name => 'Task02', description => 'Task to tune 45zw5bhb0135a');
       --Or
       --stmt_task:=dbms_sqltune.create_tuning_task(sqlset_name => 'STS01', time_limit => 3600, task_name => 'Task03', description => 'Task to tune 45zw5bhb0135a');
     END;
     /
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXECUTE dbms_sqltune.execute_tuning_task('Task02');
 
PL/SQL PROCEDURE successfully completed.

Result

You can generate implementation script with:

SQL> SET linesize 200
SQL> SET LONG 999999999
SQL> SET pages 1000
SQL> SET longchunksize 20000
SQL> SELECT dbms_sqltune.script_tuning_task('Task02', 'ALL') FROM dual;
 
DBMS_SQLTUNE.SCRIPT_TUNING_TAS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------
-- Script generated by DBMS_SQLTUNE package, advisor framework --
-- Use this script to implement some of the recommendations    --
-- made by the SQL tuning advisor.                             --
--                                                             --
-- NOTE: this script may need to be edited for your system     --
--       (index names, privileges, etc) before it is executed. --
-----------------------------------------------------------------
EXECUTE dbms_stats.gather_table_stats(ownname => 'WCMS', tabname => 'DM_SYSOBJECT_R', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', CASCADE => TRUE);
EXECUTE dbms_sqltune.accept_sql_profile(task_name => 'Task02', REPLACE => TRUE);
CREATE INDEX WCMS.IDX$$_E32B0001 ON WCMS.DM_SYSOBJECT_R('A_EXPIRATION_DATE');

You can see potential improvement with:

SQL> SELECT attribute,plan_hash_value,bytes,COST,TIME,cpu_cost,io_cost
     FROM DBA_SQLTUNE_PLANS
     WHERE task_id='58155' AND parent_id IS NULL;
 
ATTRIBUTE                   PLAN_HASH_VALUE      BYTES       COST       TIME   CPU_COST    IO_COST
--------------------------- --------------- ---------- ---------- ---------- ---------- ----------
Original                         1654789573       1150         13          1     100671         13
Original WITH adjusted COST      1654789573        112     131361       1577 1136240508     130528
USING SQL PROFILE                  28575671        112       7893         95 3523244908       5310
USING NEW indices                 978933091        112         31          1    3419677         29

Conclusion

So recommendations are quite different, of course SAA is not suppose to provide recommendations on statistics while STA does. But the most interesting part is on their common perimeter: indexes. Again here they are proposing really different recommendations and better than sticking on only one advisor better to use both in parallel and carefully test their recommendations.

If supplying directly the SQL statement to STA is not an issue then you can also use available below high level procedures:

  • DBMS_ADVISOR.QUICK_TUNE
  • DBMS_ADVISOR.TUNE_MVIEW

References

  • Oracle10g: Using SQL Access Advisor (DBMS_ADVISOR) with the Automatic Workload Repository [ID 259188.1]
  • HOW TO LOAD QUERIES INTO A SQL TUNING SET [ID 1271343.1]

About Post Author

Share the knowledge!

6 thoughts on “SQL Access Advisor versus SQL Tuning Advisor

  1. Very nice post!

    The:
    SELECT DBMS_ADVISOR.get_task_report (‘Task01’) AS report
    FROM DUAL;
    does raises an error, but in order to avoid the DIRECTORY and file usage, someone may use something like this:

    DECLARE
    v_task_report CLOB;
    BEGIN
    v_task_report := DBMS_ADVISOR.GET_TASK_SCRIPT (‘Task01’);
    DBMS_OUTPUT.PUT_LINE (v_task_report);
    END;
    /

    Thanks!
    jmavr

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>