透過AWR REPORT 或 ADDM REPORT進行SQLTUNE

1.建立SQL TUNE任務
SET SERVEROUTPUT ON
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => 'aaz58jt1sgjk3',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => 'aaz58jt1sgjk3_tuning_task',
description => 'Tuning task for statement aaz58jt1sgjk3.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

[@more@]

2.執行任務，並檢視執行狀態
EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'aaz58jt1sgjk3_tuning_task' );
SELECT task_name, status FROM dba_advisor_log WHERE owner = 'SYS' and task_name='aaz58jt1sgjk3_tuning_task';

3.檢視SQLTUNE報告
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'aaz58jt1sgjk3_tuning_task') FROM DUAL;

4.根據SQLTUNE結果，選擇透過SQL PROFILE來最佳化執行計劃或透過所給的建議來更改SQL語句

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

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.

Recommendation (estimated benefit<=10%)
---------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name =>
'aaz58jt1sgjk3_tuning_task', replace => TRUE);

2- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
An expensive "UNION" operation was found at line ID 6 of the execution plan.

Recommendation
--------------
- Consider using "UNION ALL" instead of "UNION", if duplicates are allowed
or uniqueness is guaranteed.

Rationale
---------
"UNION" is an expensive and blocking operation because it requires
elimination of duplicate rows. "UNION ALL" is a cheaper alternative,
assuming that duplicates are allowed or uniqueness is guaranteed.

-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- The optimizer could not merge the view at line ID 5 of the execution plan.
The optimizer cannot merge a view that contains a set operator.
- The optimizer could not merge the view at line ID 3 of the execution plan.
The optimizer cannot merge a view that contains an "ORDER BY" clause unless
the statement is a "DELETE" or an "UPDATE" and the parent query is the top
most query in the statement.
- The optimizer could not merge the view at line ID 1 of the execution plan.
The optimizer cannot merge a view that contains a "ROWNUM" pseudo column.

Reference: