Table of contents
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:
- Create a task.
- Create a workload and associate it to task.
- Add SQL statement(s) to workload.
- Execute the task.
- 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:
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]
Julio Romano says:
Very very nice post.
Congratz!
Yannick Jaquier says:
Thanks you !
Happy to have helped !
S Hodge says:
SELECT dbms_advisor.get_task_script(‘&&sqlaccess_task_name’) from dual;
jmavr says:
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
Yannick Jaquier says:
Hi,
Thanks for comment and trick !! 🙂