Oracle SQL Tuning Advisor 测试
如果面对一个需要优化的SQL语句，没有很好的想法，可以先试试Oracle的SQL Tuning Advisor。
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for 64-bit Windows: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production --创建测试表 SQL> create table test_sql_tuning(id_ number); Table created. SQL> declare 2 begin 3 for i in 1..100000 loop 4 insert into test_sql_tuning values(i) 5 end loop; 6 commit; 7 end; 8 / PL/SQL procedure successfully completed. SQL> set autotrace on exp SQL> select * from test_sql_tuning t where t.id_=333; ID_ ---------- 333 Execution Plan ---------------------------------------------------------- Plan hash value: 3855182387 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 39 | 69 (2)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TEST_SQL_TUNING | 3 | 39 | 69 (2)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("T"."ID_"=333) Note ----- - dynamic sampling used for this statement (level=2) SQL> declare 2 my_task_name varchar2(50); 3 begin 4 my_task_name := dbms_sqltune.create_tuning_task( 5 SQL_ID => 'g72kdvcacxvtf', 6 scope => 'COMPREHENSIVE', 7 task_name => 'ZEN_TEST_SQLTUNE'); 8 DBMS_SQLTUNE.execute_tuning_task(my_task_name); 9 end; 10 / PL/SQL procedure successfully completed. SQL> select dbms_sqltune.report_tuning_task('ZEN_TEST_SQLTUNE') from dual; DBMS_SQLTUNE.REPORT_TUNING_TASK('ZEN_TEST_SQLTUNE') -------------------------------------------------------------------------------- GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name : ZEN_TEST_SQLTUNE Tuning Task Owner : HR Workload Type : Single SQL Statement Scope : COMPREHENSIVE Time Limit(seconds): 1800 Completion Status : COMPLETED Started at : 07/02/2017 16:31:20 Completed at : 07/02/2017 16:31:20 DBMS_SQLTUNE.REPORT_TUNING_TASK('ZEN_TEST_SQLTUNE') -------------------------------------------------------------------------------- ------------------------------------------------------------------------------- Schema Name: HR SQL ID : cnvs1fb15pqb4 SQL Text : select * from test_sql_tuning t where t.id_=333 ------------------------------------------------------------------------------- FINDINGS SECTION (2 findings) ------------------------------------------------------------------------------- 1- Statistics Finding --------------------- DBMS_SQLTUNE.REPORT_TUNING_TASK('ZEN_TEST_SQLTUNE') -------------------------------------------------------------------------------- Table "HR"."TEST_SQL_TUNING" was not analyzed. Recommendation -------------- - Consider collecting optimizer statistics for this table. execute dbms_stats.gather_table_stats(ownname => 'HR', tabname => 'TEST_SQL_TUNING', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO'); Rationale DBMS_SQLTUNE.REPORT_TUNING_TASK('ZEN_TEST_SQLTUNE') -------------------------------------------------------------------------------- --------- 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: 98.55%) ------------------------------------------ DBMS_SQLTUNE.REPORT_TUNING_TASK('ZEN_TEST_SQLTUNE') -------------------------------------------------------------------------------- - Consider running the Access Advisor to improve the physical schema design or creating the recommended index. create index HR.IDX$$_06650001 on HR.TEST_SQL_TUNING("ID_"); 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('ZEN_TEST_SQLTUNE') -------------------------------------------------------------------------------- ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3855182387 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | DBMS_SQLTUNE.REPORT_TUNING_TASK('ZEN_TEST_SQLTUNE') -------------------------------------------------------------------------------- ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 39 | 69 (2)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TEST_SQL_TUNING | 3 | 39 | 69 (2)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("T"."ID_"=333) DBMS_SQLTUNE.REPORT_TUNING_TASK('ZEN_TEST_SQLTUNE') -------------------------------------------------------------------------------- 2- Using New Indices -------------------- Plan hash value: 1603088386 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| IDX$$_06650001 | 1 | 13 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------- DBMS_SQLTUNE.REPORT_TUNING_TASK('ZEN_TEST_SQLTUNE') -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T"."ID_"=333) ------------------------------------------------------------------------------- 1 row selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1388734953 ----------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ----------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 | | 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 | ----------------------------------------------------------------- SQL>
上面的report总共分为3个部分，
分别是SQL调优的基本信息、SQL调优的建议findings、以及SQL对应的执行计划部分
在基本信息部分包含了SQL调优的任务名称，状态，执行，完成时间，对应的SQL完整语句等
在finding部分则给出本次调优所得到的成果，如本次是提示缺少统计信息，可以及创建索引。
在执行计划部分则给出了当前SQL语句的执行计划以及谓词信息
