Using quick_tune to generates recommendations

When we need to analyze and generate recommendations for a single SQL statement, that can use dbms_advisor.quick_tune to help:

dbms_advisor.quick_tune(
advisor_name IN VARCHAR2,
task_name IN VARCHAR2,
attr1 IN CLOB := NULL,
attr2 IN VARCHAR2 := NULL,
attr3 IN NUMBER := NULL,
template IN VARCHAR2 := NULL,
implement IN BOOLEAN := FALSE,
description IN VARCHAR2 := NULL);

Example to use dbms_advisor.quick_tune :

- create table for testing.

CREATE TABLE TEMP01 NOLOGGING AS SELECT * FROM ALL_OBJECTS;

ANALYZE TABLE TEMP01 COMPUTE STATISTICS;

A SQL Statement need to analyze : select object_name from temp01 where object_id=1234

- Begin analyze and get recommendation.

desc user_advisor_templates

set linesize 100
col task_name format a40
col description format a50
SELECT task_name, description FROM user_advisor_templates;

TASK_NAME DESCRIPTION
---------------------------------------- -----------------------
SQLACCESS_GENERAL General purpose database template
SQLACCESS_OLTP OLTP database template
SQLACCESS_WAREHOUSE Data Warehouse database template
SQLACCESS_EMTASK Default Enterprise Manager task template


desc user_advisor_journal

SELECT COUNT(*) FROM user_advisor_journal;

COUNT(*)
----------
0

DECLARE
task_name VARCHAR2(30) := 'TEST01';
BEGIN
dbms_advisor.quick_tune(dbms_advisor.sqlaccess_advisor, task_name,
'select object_name from temp01 where object_id=1234');
END;
/

SELECT COUNT(*) FROM user_advisor_journal;

COUNT(*)
----------
19


col task_name format a10
col journal_entry_type format a15
col journal_entry format a65

SELECT task_name, journal_entry_type, journal_entry
FROM user_advisor_journal WHERE task_name = 'TEST01';

TASK_NAME JOURNAL_ENTRY_T JOURNAL_ENTRY
---------- --------------- -----------------------
TEST01 INFORMATION Preparing workload for analysis
TEST01 INFORMATION Filter Summary: Valid username: Unused
TEST01 INFORMATION Filter Summary: Invalid username: Unused
TEST01 INFORMATION Filter Summary: Valid module: Unused
TEST01 INFORMATION Filter Summary: Invalid module: Unused
TEST01 INFORMATION Filter Summary: Valid action: Unused
TEST01 INFORMATION Filter Summary: Invalid action: Unused
TEST01 INFORMATION Filter Summary: Valid SQL String: Unused
TEST01 INFORMATION Filter Summary: Invalid SQL String: Statements discarded: 0
TEST01 INFORMATION Filter Summary: Invalid start time: Unused
TEST01 INFORMATION Filter Summary: Invalid end time: Unused
TEST01 INFORMATION Filter Summary: Invalid table: Unused
TEST01 INFORMATION Filter Summary: Valid table: Unused
TEST01 INFORMATION Filter Summary: Invalid table reference:
Statements discarded: 0
TEST01 INFORMATION Filter Summary: SQL Syntax or Semantic
Error: Statements discarded: 0

TEST01 INFORMATION Filter Summary: Invalid comment: Statements discarded: 0
TEST01 INFORMATION Filter Summary: Missing statistics: Statements discarded: 0
TEST01 INFORMATION Workload processing completed: 1 scanned 1 accepted 0 discarded
TEST01 INFORMATION No existing materialized views were found

desc user_advisor_recommendations
- Grid Control recommends the actions be accepted

SELECT type, rank, benefit, annotation_status
FROM user_advisor_recommendations
WHERE task_name = 'TEST01';

TYPE RANK BENEFIT ANNOTATION_
------------------------------ ----------
ACTIONS 1 4521 ACCEPT

desc user_advisor_actions
- View the recommended actions

col command format a30
col attr1 format a25
col attr3 format a25
col attr4 format a20
col attr5 format a100
col attr6 format a20
col error_message format a15

SELECT command, attr1, attr3, attr4
FROM user_advisor_actions
WHERE task_name = 'TEST01';

COMMAND ATTR1 ATTR3 ATTR4
------------------------------ ------------------------- ---------------
CREATE MATERIALIZED VIEW LOG "SCOTT"."TEMP01" ROWID

CREATE MATERIALIZED VIEW "SCOTT"."MV$$_07540000" REFRESH FAST WITH ROWID ENABLE QUERY REWRITE

GATHER TABLE STATISTICS "SCOTT"."MV$$_07540000" -1


set long 100000
SELECT attr5 FROM user_advisor_actions
WHERE task_name = 'TEST01';

ATTR5
--------------------------------------------------------

SELECT "SCOTT"."TEMP01"."OBJECT_NAME" M1
FROM SCOTT.TEMP01 WHERE
(SCOTT.TEMP01.OBJECT_ID = 1234)


desc user_advisor_log

SELECT execution_start, execution_end, status, error_message
FROM user_advisor_log WHERE task_name = 'TEST01';

EXECUTION EXECUTION STATUS ERROR_MESSAGE
--------- --------- ----------- ---------------
12-NOV-08 12-NOV-08 COMPLETED


desc user_advisor_tasks

col description format a15
col advisor_name format a20
col source format a20
col recommendation_count format 99
col how_created format a20

SELECT description, advisor_name, created, status,
recommendation_count, source, how_created
FROM user_advisor_tasks WHERE task_name = 'TEST01';

DESCRIPTION ADVISOR_NAME CREATED STATUS RECOMMENDATION_COUNT
--------------- -------------------- --------- ----------- --------------------
Quick Tune SQL Access Advisor 12-NOV-08 COMPLETED 1

SOURCE HOW_CREATED
---------- -------------------
SQLACCESS_GENERAL CMD


- Delete Task name

exec dbms_advisor.delete_task('TEST01');


From steps, we get recommendations and use them to improve performance about that sql statement.