ORACLE SQL Tuning Advisor

前言：一直以来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(*)
----------
   2657440
select count(*) from small_tab;
SQL> select count(*) from small_tab;

  COUNT(*)
----------
      2584

查询测试，查看最初执行计划

SQL> set timing on
SQL> set autotrace on
SQL> select count(*) from big_tab a,small_tab b where a.object_name=b.table_name;

  COUNT(*)
----------
     98112


Execution 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 calls
	  0  db block gets
      40009  consistent gets
      40767  physical reads
	  0  redo size
	528  bytes sent via SQL*Net to client
	523  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  4  sorts (memory)
	  0  sorts (disk)
	  1  rows processed
SQL> 

创建调优任务CREATE_TUNING_TASK

SQL> set autot off
SQL> set timing off
SQL>DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_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_test
Tuning Task Owner  : CHAICHENG
Workload Type      : Single SQL Statement
Execution Count    : 2
Current Execution  : EXEC_5930
Execution Type     : TUNE SQL
Scope              : COMPREHENSIVE
Time Limit(seconds): 180
Completion Status  : COMPLETED
Started at         : 12/11/2020 15:11:16
Completed at       : 12/11/2020 15:11:43

-------------------------------------------------------------------------------
Schema Name: CHAICHENG
SQL ID     : 30tv0wjwd7ntn
SQL Text   : select count(*) from big_tab a,small_tab b where
             a.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.

总结：SQL Tuning Advisor为DBA的调优工作减轻了不少负担，一般情况下我也是通过这种方法进行调优的，这里有点小小的建议：工具毕竟是固定的，一般只会针对单个语句给出建议，不会在整个数据库的整体性能上面去思考该调优方法是否可行，这点必须由DBA把握；DBA还是有必要了解为什么要这么调优，才能更好的根据实际情况给出具体的调优办法，不然调优这种高大上的工作将变得廉价。