SQL Tuning Advisor

SQL Tuning Advisor

数据库环境：oracle11gr2

数据库用户OE下面的表prootab数据量150万条记录数，执行全表扫描。

[@more@]

1. 要访问SQL tuning advisor API，必须给一个用户授予 ADVISOR权限。

CONN sys/password AS SYSDBA

GRANT ADVISOR TO dbmaintain;

CONN dbmaintain/dbmaintain

2. 使用dbms_sqltune.create_tunning_task创建优化任务

SET SERVEROUTPUT ON

-- Tuning task created for specific a statement from the AWR.

--从AWR指定一个语句(SQL_ID)为了创建调优任务.

DECLARE

l_sql_tune_task_id VARCHAR2(100);

BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

begin_snap => 104,

end_snap => 105,

sql_id => 'atbfbj5xzm5kn',

scope => DBMS_SQLTUNE.scope_comprehensive,

time_limit => 60,

task_name => 'atbfbj5xzm5kn_AWR_tuning_task1',

description => 'Tuning task for statement atbfbj5xzm5kn in AWR.');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/

l_sql_tune_task_id: atbfbj5xzm5kn_AWR_tuning_task1

PL/SQL 过程已成功完成。

3. 查看生成的调优任务，因为没有执行，所有状态为inital，该调优任务来自AWR.

SELECT TASK_ID,TASK_NAME,DESCRIPTION,ADVISOR_NAME,STATUS FROM USER_ADVISOR_TASKS;

TASK_ID TASK_NAME DESCRIPTION ADVISOR_NAME STATUS

1 228 atbfbj5xzm5kn_AWR_tuning_task1 Tuning task for statement atbfbj5xzm5kn in AWR. SQL Tuning Advisor INITIAL

4. 使用dbms_sqltune.execute_tunning_task执行优化任务

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'atbfbj5xzm5kn_AWR_tuning_task1');

5. 查看生成的调优任务，因为执行之后，所有状态为completed，该调优任务来自AWR.

TASK_ID TASK_NAME DESCRIPTION ADVISOR_NAME STATUS

2 232 atbfbj5xzm5kn_AWR_tuning_task1 Tuning task for statement atbfbj5xzm5kn in AWR. SQL Tuning Advisor COMPLETED

6. 使用dbms_sqltune.report_tunning_task报告优化任务

SET LONG 10000;

SET PAGESIZE 1000

SET LINESIZE 200

SELECT DBMS_SQLTUNE.report_tuning_task('atbfbj5xzm5kn_AWR_tuning_task1') AS recommendations FROM dual;

SET PAGESIZE 24

7. 优化报告输出：

RECOMMENDATIONS

--------------------------------------------------------------------------------

GENERAL INFORMATION SECTION

-------------------------------------------------------------------------------

Tuning Task Name : atbfbj5xzm5kn_AWR_tuning_task1

Tuning Task Owner : DBMAINTAIN

Workload Type : Single SQL Statement

Scope : COMPREHENSIVE

Time Limit(seconds): 60

Completion Status : COMPLETED

Started at : 07/31/2013 16:46:52

Completed at : 07/31/2013 16:47:29

-------------------------------------------------------------------------------

Schema Name: OE

SQL ID : atbfbj5xzm5kn

SQL Text : select * from prootab

-------------------------------------------------------------------------------

FINDINGS SECTION (1 finding)

-------------------------------------------------------------------------------

1- Statistics Finding

---------------------

尚未分析表 "OE"."PROOTAB"。

Recommendation

--------------

- 考虑收集此表的优化程序统计信息。

execute dbms_stats.gather_table_stats(ownname => 'OE', tabname =>

'PROOTAB', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

method_opt => 'FOR ALL COLUMNS SIZE AUTO');

Rationale

---------

为了选择好的执行计划, 优化程序需要此表的最新统计信息。

-------------------------------------------------------------------------------

EXPLAIN PLANS SECTION

-------------------------------------------------------------------------------

1- Original

-----------

Plan hash value: 1767372524

-----------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 50M| 7661M| 7021 (12)| 00:01:25 |

| 1 | TABLE ACCESS FULL| PROOTAB | 50M| 7661M| 7021 (12)| 00:01:25 |

-----------------------------------------------------------------------------

-------------------------------------------------------------------------------

分析结果显示，要分析该表prootab，的确，我没有对该进行分析，但是，我也没有创建索引，为什么sql tunning advisor没有建议呢

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

转载于:http://blog.itpub.net/28227905/viewspace-1060799/