前言：经常可以碰到优化sql的需求，开发人员直接扔过来一个SQL让DBA优化，然后怎么办？
当然，经验丰富的DBA可以从各种方向下手，有时通过建立正确索引即可获得很好的优化效果，但是那些复杂SQL错综复杂的表关联，却让DBA们满头大汗。
如下特别介绍一种oracle官方提供的科学优化方法STA，经过实践，不敢说此特性绝对有效，但是可以开阔思路，并且从中学到许多知识，不再用“猜”的方式去创建索引了。
SQL优化器SQL Tuning Advisor （STA)，是oracle的sql优化补助工具。
其实优化sql主要有两个方案，其一是改写sql本身，改写sql需要对sql语法、数据库的执行方式都要有较好地理解。
其二就是这个STA，它属于DBMS_SQLTUNE包，它的主要作用是对于sql使用到的表创建正确的索引。
使用STA前提：
要保证优化器是CBO模式下。
show parameter OPTIMIZER_MODE all_rows /*CBO，sql所有返回行都采用基于成本的方式运行*/ first_rows /*CBO，使用成本和试探法相结合的方法，查找一种可以最快返回前面少数行*/ first_rows_n /*CBO，全部采用基于成本的优化方法CBO，并以最快的速度，返回前N行记录*/ choose /*如果有统计信息，采用CBO，否则采用RBO*/ rule /*RBO*/
执行DBMS_SQLTUNE包进行sql优化需要有advisor的权限：
grant advisor to scott;
如下是STA使用例子：
1.首先创建两个练习表obj与ind，仅创建表，无需创建索引：
SQL> create table obj as select * from dba_objects; Table created SQL> create table ind as select * from dba_indexes; Table created SQL> insert into obj select * from obj; 76714 rows inserted SQL> insert into obj select * from obj; 153428 rows inserted SQL> insert into obj select * from obj; 306856 rows inserted SQL> insert into ind select * from ind; 5513 rows inserted SQL> insert into ind select * from ind; 11026 rows inserted SQL> insert into ind select * from ind; 22052 rows inserted
SQL>
2.然后对这两个表，obj与ind进行联合查询，并通过autotrace查看其执行计划：
SQL> set timing on
SQL> set autot trace
SQL> select count(*) from obj o, ind i where o.object_name=i.index_name;
Elapsed: 00:00:00.72
Execution Plan
----------------------------------------------------------
Plan hash value: 380737209
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 83 | 2884 (1)| 00:00:35 |
| 1 | SORT AGGREGATE | | 1 | 83 | | |
|* 2 | HASH JOIN | | 93489 | 7577K| 2884 (1)| 00:00:35 |
| 3 | TABLE ACCESS FULL| IND | 8037 | 133K| 413 (1)| 00:00:05 |
| 4 | TABLE ACCESS FULL| OBJ | 93486 | 6025K| 2471 (1)| 00:00:30 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("O"."OBJECT_NAME"="I"."INDEX_NAME")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
9 recursive calls
4 db block gets
36518 consistent gets
0 physical reads
576684 redo size
527 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
通过执行计划，可以清晰的看到，在执行以上两个表的联合查询的时候，两张表走的全表扫和hash join。
正式使用STA进行优化：
第一步：创建优化任务
通过调用函数DBMS_SQLTUNE.CREATE_TUNING_TASK来创建优化任务，调用存储过程DBMS_SQLTUNE.EXECUTE_TUNING_TASK执行该任务：
SQL> set autot off SQL> set timing off DECLARE my_task_name VARCHAR2(30); my_sqltext CLOB; BEGIN my_sqltext := 'select count(*) from obj o, ind i where o.object_name=i.index_name'; my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_text => my_sqltext, user_name => 'SCOTT', scope => 'COMPREHENSIVE', time_limit => 30, task_name => 'tuning_sql_test', description => 'tuning'); DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tuning_sql_test'); END; / PL/SQL 过程已成功完成。
如下是参数解释：
函数CREATE_TUNING_TASK，
sql_text是需要优化的语句，
user_name是该语句通过哪个用户执行，用户名大写，
scope是优化范围（limited或comprehensive），
time_limit优化过程的时间限制，
task_name优化任务名称，
description优化任务描述。
第二步： 执行优化任务
通过调用dbms_sqltune.execute_tuning_task过程来执行前面创建好的优化任务。
SQL> exec dbms_sqltune.execute_tuning_task('tuning_sql_test');
PL/SQL 过程已成功完成。
第三步：检查优化任务的状态
通过查看user_advisor_tasks/dba_advisor_tasks视图可以查看优化任务的当前状态。
SQL> SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name ='tuning_sql_test'; TASK_NAME STATUS ------------------------------ ----------- tuning_sql_test COMPLETED
第四步：查看优化结果
通过dbms_sqltune.report_tning_task函数可以获得优化任务的结果。
SQL> set long 999999
SQL> set serveroutput on size 999999
SQL> set line 120
SQL> select DBMS_SQLTUNE.REPORT_TUNING_TASK( 'tuning_sql_test') from dual;
如下是显示优化的结果：
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : tuning_sql_test
Tuning Task Owner : SCOTT
Workload Type : Single SQL Statement
Execution Count : 2
Current Execution : EXEC_788
Execution Type : TUNE SQL
Scope : COMPREHENSIVE
Time Limit(seconds): 30
Completion Status : COMPLETED
Started at : 04/19/2019 10:45:32
Completed at : 04/19/2019 10:45:38
-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID : 6wruu2mxyu8g3
SQL Text : select count(*) from obj o, ind i where
o.object_name=i.index_name
-------------------------------------------------------------------------------
FINDINGS SECTION (3 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Table "SCOTT"."IND" was not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
'IND', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
2- Statistics Finding
---------------------
Table "SCOTT"."OBJ" was not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
'OBJ', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
3- 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: 89.48%)
------------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index SCOTT.IDX$$_02F40001 on SCOTT.IND("INDEX_NAME");
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index SCOTT.IDX$$_02F40002 on SCOTT.OBJ("OBJECT_NAME");
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.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 380737209
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 83 | 2910 (2)| 00:00:35 |
| 1 | SORT AGGREGATE | | 1 | 83 | | |
|* 2 | HASH JOIN | | 4421K| 350M| 2910 (2)| 00:00:35 |
| 3 | TABLE ACCESS FULL| IND | 46033 | 764K| 413 (1)| 00:00:05 |
| 4 | TABLE ACCESS FULL| OBJ | 620K| 39M| 2475 (1)| 00:00:30 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("O"."OBJECT_NAME"="I"."INDEX_NAME")
2- Using New Indices
--------------------
Plan hash value: 2653760187
--------------------------------------------------------------------------------
---------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
ime |
--------------------------------------------------------------------------------
---------
| 0 | SELECT STATEMENT | | 1 | 83 | 306 (8)| 0
0:00:04 |
| 1 | SORT AGGREGATE | | 1 | 83 | |
|
|* 2 | HASH JOIN | | 4421K| 350M| 306 (8)| 0
0:00:04 |
| 3 | INDEX FAST FULL SCAN| IDX$$_02F40001 | 46033 | 764K| 19 (0)| 0
0:00:01 |
| 4 | INDEX FAST FULL SCAN| IDX$$_02F40002 | 620K| 39M| 265 (1)| 0
0:00:04 |
--------------------------------------------------------------------------------
---------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("O"."OBJECT_NAME"="I"."INDEX_NAME")
-------------------------------------------------------------------------------
根据优化结果可知问题：
a.Table "SCOTT"."IND" was not analyzed.
b.Table "SCOTT"."OBJ" was not analyzed.
c.索引未创建
对应的解决方案：
a.execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>'IND', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO');
b.execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>'OBJ', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO');
c.create index SCOTT.IDX$$_02F40001 on SCOTT.IND("INDEX_NAME");
create index SCOTT.IDX$$_02F40002 on SCOTT.OBJ("OBJECT_NAME");
创建推荐的索引可以显著地改进此语句的执行计划。但是, 使用典型的 SQL 工作量运行 "访问指导"
可能比单个语句更可取。通过这种方法可以获得全面的索引建议案, 包括计算索引维护的开销和附加的空间消耗