通过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:
http://www.oracle-base.com/articles/10g/AutomaticSQLTuning10g.php
http://www.laoxiong.net/sql-profiles-part.html

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/789833/viewspace-1056855/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/789833/viewspace-1056855/