前言：一直以来SQL调优都是DBA比较费力的技术活，而且很多DBA如果没有从事过开发的工作，那么调优更是一项头疼的工作，即使是SQL调优很厉害的高手，在SQL调优的过程中也要不停的分析执行计划、加HINT、分析统计信息等等。从ORACLE 10G开始，数据库采取了很多智能化的管理工作，其中SQL优化器(SQL Tuning Advisor：STA)，大大的提高了DBA进行SQL优化的效率；
准备测试环境（创建两个表并插入数据）
create table big_tab as select rownum as id,a.* from sys.all_objects a ;create table small_tab as select rownum as id,a.* from sys.all_tables a ;insert into big_tab select * from big_tab;select count(*) from big_tab;SQL> select count(*) from big_tab;COUNT(*)----------2657440select count(*) from small_tab;SQL> select count(*) from small_tab;COUNT(*)----------2584
查询测试，查看最初执行计划
SQL> set timing onSQL> set autotrace onSQL> select count(*) from big_tab a,small_tab b where a.object_name=b.table_name;COUNT(*)----------98112Execution Plan----------------------------------------------------------Plan hash value: 2505897097---------------------------------------------------------------------------------| Id | Operation | Name | Rows| Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------| 0 | SELECT STATEMENT | |1 | 34 | 8902 (1)| 00:01:47 || 1 | SORT AGGREGATE| |1 | 34 | | ||* 2 | HASH JOIN | | 1439K| 46M| 8902 (1)| 00:01:47 || 3 | TABLE ACCESS FULL| SMALL_TAB | 2464 | 41888 | 24 (0)| 00:00:01 || 4 | TABLE ACCESS FULL| BIG_TAB| 2653K| 43M| 8872 (1)| 00:01:47 |---------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")Note------ dynamic sampling used for this statement (level=2)Statistics----------------------------------------------------------139 recursive calls0 db block gets40009 consistent gets40767 physical reads0 redo size528 bytes sent via SQL*Net to client523 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client4 sorts (memory)0 sorts (disk)1 rows processedSQL>
创建调优任务CREATE_TUNING_TASK
SQL> set autot offSQL> set timing offSQL>DECLAREmy_task_name VARCHAR2(30);my_sqltext CLOB;BEGINmy_sqltext := 'select count(*) from big_tab a,small_tab b where a.object_name=b.table_name';my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => my_sqltext,user_name => 'CHAICHENG',scope => 'COMPREHENSIVE',time_limit => 180,task_name => 'tuning_sql_test',description => 'Task to tune a query on a specified table');DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tuning_sql_test');END;/
执行创建的调优任务
SQL> exec dbms_sqltune.execute_tuning_task('tuning_sql_test');PL/SQL procedure successfully completed.
查看调优任务
SQL> SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name='tuning_sql_test';TASK_NAME STATUS------------------------------ -----------tuning_sql_test COMPLETED
获取调优报告
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tuning_sql_test') from DUAL;GENERAL INFORMATION SECTION-------------------------------------------------------------------------------Tuning Task Name : tuning_sql_testTuning Task Owner : CHAICHENGWorkload Type: Single SQL StatementExecution Count : 2Current Execution : EXEC_5930Execution Type: TUNE SQLScope : COMPREHENSIVETime Limit(seconds): 180Completion Status : COMPLETEDStarted at : 12/11/ 15:11:16Completed at : 12/11/ 15:11:43-------------------------------------------------------------------------------Schema Name: CHAICHENGSQL ID: 30tv0wjwd7ntnSQL Text : select count(*) from big_tab a,small_tab b wherea.object_name=b.table_name-------------------------------------------------------------------------------FINDINGS SECTION (3 findings)-------------------------------------------------------------------------------1- Statistics Finding---------------------未分析表格 "CHAICHENG"."SMALL_TAB".Recommendation--------------- 考虑收集此表格的最佳化处理程序统计信息.execute dbms_stats.gather_table_stats(ownname => 'CHAICHENG', tabname =>'SMALL_TAB', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO');Rationale---------最佳化处理需要表格的最新统计信息, 才能选取合适的执行计划.2- Statistics Finding---------------------未分析表格 "CHAICHENG"."BIG_TAB".Recommendation--------------- 考虑收集此表格的最佳化处理程序统计信息.execute dbms_stats.gather_table_stats(ownname => 'CHAICHENG', tabname =>'BIG_TAB', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO');Rationale---------最佳化处理需要表格的最新统计信息, 才能选取合适的执行计划.3- Index Finding (see explain plans section below)--------------------------------------------------此叙述语句的执行计划可以藉由建立一个或多个索引来改进.Recommendation (estimated benefit: 98.8%)------------------------------------------ 考虑执行「存取建议程序」来改进实际纲要设计, 或建立建议的索引.create index CHAICHENG.IDX$$_17490001 on CHAICHENG.SMALL_TAB("TABLE_NAME");- 考虑执行「存取建议程序」来改进实际纲要设计, 或建立建议的索引.create index CHAICHENG.IDX$$_17490002 on CHAICHENG.BIG_TAB("OBJECT_NAME");Rationale---------建立建议的索引可大幅改进此叙述语句的执行计划. 然而, 最好是使用代表性的 SQL 工作负载 (相对于单一叙述语句) 来执行「存取建议程序」.这将可获得广泛的索引建议(将索引维护成本和其他空间使用纳入考量).-------------------------------------------------------------------------------EXPLAIN PLANS SECTION-------------------------------------------------------------------------------1- Original-----------Plan hash value: 2505897097---------------------------------------------------------------------------------| Id | Operation | Name| Rows | Bytes | Cost (%CPU)| Time|---------------------------------------------------------------------------------| 0 | SELECT STATEMENT | |1 | 34 | 8902 (1)| 00:01:47 || 1 | SORT AGGREGATE| |1 | 34 | |||* 2 | HASH JOIN | | 1439K| 46M| 8902 (1)| 00:01:47 || 3 | TABLE ACCESS FULL| SMALL_TAB | 2464 | 41888 | 24 (0)| 00:00:01 || 4 | TABLE ACCESS FULL| BIG_TAB | 2653K| 43M| 8872 (1)| 00:01:47 |---------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")2- Using New Indices--------------------Plan hash value: 4182513400-----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|-----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT ||1 | 34 | 106 (6)| 00:00:02 || 1 | SORT AGGREGATE ||1 | 34 | |||* 2 | HASH JOIN || 1439K| 46M| 106 (6)| 00:00:02 || 3 | INDEX FAST FULL SCAN| IDX$$_17490001 | 2464 | 41888 |9 (0)| 00:00:01 || 4 | INDEX FAST FULL SCAN| IDX$$_17490002 | 2653K| 43M| 91 (0)| 00:00:02 |-----------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")-------------------------------------------------------------------------------
删除优化任务
通过调用dbms_sqltuen.drop_tuning_task可以删除已经存在的优化任务
SQL>exec dbms_sqltune.drop_tuning_task(‘tuning_sql_test’);
PL/SQL procedure successfully completed.
![[原创]ORACLE SQL TUNING ADVISOR 使用方法](https://300zi.50zi.cn/uploadfile/img/15/567/f8e5ccb768e3335e9794d31088055762.jpg)
![[terry笔记]Oracle SQL 优化之sql tuning advisor (STA)](https://300zi.50zi.cn/uploadfile/img/15/859/4bab4dc757e0965647db500d32ff3f60.jpg)
