Oracle SQL优化之STA(SQL Tuning Advisor)

标签：00 Tuning SQL sql SCOTT Advisor 优化 name

前言：经常可以碰到优化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 工作量运行 "访问指导"
可能比单个语句更可取。通过这种方法可以获得全面的索引建议案, 包括计算索引维护的开销和附加的空间消耗

标签：00,Tuning,SQL,sql,SCOTT,Advisor,优化,name
From： https://www.cnblogs.com/william2019/p/17307173.html

Oracle SQL优化之STA(SQL Tuning Advisor)

相关文章

赞助商

阅读排行