SQL调优工具包DBMS_SQLTUNE的使用方法

oracle 提供了优化建议功能包DBMS_SQLTUNE，该包可以帮助我们分析SQL，并提供优化建议。 原有执行计划 alter session set statistics_level=all; set serveroutput off select * from test.emp where ename='SCOTT' and DEPTNO=20; SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'runstats_last')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ SQL_ID  8k1gbrapm7zpd, child number 0 ------------------------------------- select * from test.emp where ename='SCOTT' and DEPTNO=20 Plan hash value: 3956160932 ------------------------------------------------------------------------------------ | Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | ------------------------------------------------------------------------------------ |   0 | SELECT STATEMENT  |      |      1 |        |      1 |00:00:00.01 |       4 | |*  1 |  TABLE ACCESS FULL| EMP  |      1 |      1 |      1 |00:00:00.01 |       4 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter(("ENAME"='SCOTT' AND "DEPTNO"=20)) 下面就用DBMS_SQLTUNE优化该SQL --1.赋予用户ADVISOR权限 grant ADVISOR  to test; --2.创建sql tuning任务 conn test/test DECLARE  my_task_name VARCHAR2(30);  my_sqltext   CLOB; BEGIN  my_sqltext := 'select * from emp where ename= :name and DEPTNO= :deptno';  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(          sql_text    => my_sqltext, bind_list   => sql_binds(anydata.convertvarchar2(10),anydata.convertnumber(2)),           user_name   => 'TEST',          scope       => 'COMPREHENSIVE',          time_limit  => 60,          task_name   => 'test_sql_tuning',           description => 'Task to tune a query on emp'); END; / 参数说明： bind_list：多个绑定变量以','逗号分隔。参数值一定要根据绑定变量对应的列的类型书写. 如：emp.ename类型是VARCHAR2(10)，那么就要写成  bind_list    =>sql_binds(anydata.convertvarchar2(10)), time_limit：执行的最长时间，默认是60。 scope： LIMITED，用大概1秒时间去优化SQL语句，但是并不进行SQL Profiling分析。 COMPREHENSIVE，进行全面分析，包含SQL Profiling分析；比LIMITED用时更长。 /*2014-4-8日增加 begin */ **也可以用sql_id创建sql tunning任务，比用sql_text方便很多 FUNCTION CREATE_TUNING_TASK RETURNS VARCHAR2  Argument Name                  Type                    In/Out Default?  ------------------------------ ----------------------- ------ --------  SQL_ID                         VARCHAR2                IN  PLAN_HASH_VALUE                NUMBER                  IN     DEFAULT  SCOPE                          VARCHAR2                IN     DEFAULT  TIME_LIMIT                     NUMBER                  IN     DEFAULT  TASK_NAME                      VARCHAR2                IN     DEFAULT  DESCRIPTION                    VARCHAR2                IN     DEFAULT DECLARE  my_task_name VARCHAR2(30); BEGIN   my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(          SQL_ID      => 'ddw7j6yfnw0vz',          scope       => 'COMPREHENSIVE',          time_limit  => 60,          task_name   => 'tunning_task_ddw7j6yfnw0vz',           description => 'Task to tune a query on  ddw7j6yfnw0vz'); END; /  /*2014-4-8日增加 end */ --3.查看任务名   SELECT TASK_NAME  FROM   DBA_ADVISOR_LOG  WHERE  OWNER = 'TEST'; TASK_NAME ------------------------------ test_sql_tuning --4.执行sql tuning任务 BEGIN   DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'test_sql_tuning' ); END; / --5.查看sql tunning任务状态 SELECT status  FROM   USER_ADVISOR_TASKS  WHERE  task_name = 'test_sql_tuning'; STATUS ----------- COMPLETED --6.展示sql tunning结果 SET LONG 10000 SET LONGCHUNKSIZE 1000 SET LINESIZE 100 SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('test_sql_tuning') FROM   DUAL; DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING') ---------------------------------------------------------------------------------------------------- GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name   : test_sql_tuning Tuning Task Owner  : TEST Workload Type      : Single SQL Statement Scope              : COMPREHENSIVE Time Limit(seconds): 60 Completion Status  : COMPLETED Started at         : 04/01/2014 16:45:16 Completed at       : 04/01/2014 16:45:17 DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING') ---------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------- Schema Name: TEST SQL ID     : 95fv6dbj64d0f SQL Text   : select * from emp where ename= :name and DEPTNO= :deptno ------------------------------------------------------------------------------- FINDINGS SECTION (2 findings) ------------------------------------------------------------------------------- 1- Statistics Finding --------------------- DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING') ----------------------------------------------------------------------------------------------------   Table "TEST"."EMP" was not analyzed.   Recommendation   --------------   - Consider collecting optimizer statistics for this table.     execute dbms_stats.gather_table_stats(ownname => 'TEST', tabname =>             'EMP', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,             method_opt => 'FOR ALL COLUMNS SIZE AUTO');   Rationale   --------- DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING') ----------------------------------------------------------------------------------------------------     The optimizer requires up-to-date statistics for the table in order to     select a good execution plan. 2- Index Finding (see explain plans section below) --------------------------------------------------   The execution plan of this statement can be improved by creating one or more   indices.   Recommendation (estimated benefit: 66.67%)   ------------------------------------------   - Consider running the Access Advisor to improve the physical schema design DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING') ----------------------------------------------------------------------------------------------------     or creating the recommended index.     create index TEST.IDX$$_00D80001 on TEST.EMP("ENAME","DEPTNO");   Rationale   ---------     Creating the recommended indices significantly improves the execution plan     of this statement. However, it might be preferable to run "Access Advisor"     using a representative SQL workload as opposed to a single statement. This     will allow to get comprehensive index recommendations which takes into     account index maintenance overhead and additional space consumption. DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING') ---------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING') ---------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |     1 |    87 |     3   (0)| 00:00:01 | |*  1 |  TABLE ACCESS FULL| EMP  |     1 |    87 |     3   (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("ENAME"=:NAME AND "DEPTNO"=:DEPTNO) 2- Using New Indices -------------------- DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING') ---------------------------------------------------------------------------------------------------- Plan hash value: 2106247215 ---------------------------------------------------------------------------------------------- | Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |                |     1 |    87 |     1   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| EMP            |     1 |    87 |     1   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN          | IDX$$_00D80001 |     1 |       |     1   (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING') ---------------------------------------------------------------------------------------------------- ---------------------------------------------------    2 - access("ENAME"=:NAME AND "DEPTNO"=:DEPTNO) ------------------------------------------------------------------------------- 建议报告总结： <1>收集EMP表的统计信息 execute dbms_stats.gather_table_stats(ownname => 'TEST', tabname =>'EMP', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO'); <2>创建索引 create index TEST.IDX$$_00D80001 on TEST.EMP("ENAME","DEPTNO");  优化后执行计划 -------------------------------------------------------------------------------------------------------- | Id  | Operation                   | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | -------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |                |      1 |        |      1 |00:00:00.01 |       3 | |   1 |  TABLE ACCESS BY INDEX ROWID| EMP            |      1 |      1 |      1 |00:00:00.01 |       3 | |*  2 |   INDEX RANGE SCAN          | IDX$$_00D80001 |      1 |      1 |      1 |00:00:00.01 |       2 | -------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("ENAME"='SCOTT' AND "DEPTNO"=20) --7.完成后删除sql tunning任务 EXEC DBMS_SQLTUNE.DROP_TUNING_TASK('test_sql_tuning'); --8.其他 --sql tunning任务创建后，也可以修改参数 BEGIN   DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(     task_name => 'test_sql_tuning',     parameter => 'TIME_LIMIT', value => 300); END; / --查看SQL Tuning Advisor的进展（task执行很久） col opname for a20 col ADVISOR_NAME for a20 SELECT SID,SERIAL#,USERNAME,OPNAME,ADVISOR_NAME,TARGET_DESC,START_TIME SOFAR, TOTALWORK  FROM   V$ADVISOR_PROGRESS  WHERE  USERNAME = 'TEST'; 以上根据oracle doc整理 http://docs.oracle.com/cd/E11882_01/server.112/e41573/sql_tune.htm#PFGRF94856