固定执行计划-使用SQL Tuning Advisor

发表于 2016 年 01 月 13 日 由 Lunar

联系：QQ(5163721)

标题：固定执行计划-使用SQL Tuning Advisor

作者：Lunar©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]

测试目的，当有hint时，并且hint跟需要绑定的执行计划有冲突，谁的优先级高。
.
固定执行计划-使用SQL Tuning Advisor
固定执行计划-手工指定PLAN OUTLINE
固定执行计划-手工指定索引名称的方式
固定执行计划-使用coe_xfr_sql_profile固定执行计划
固定执行计划-使用SPM（Sql Plan Management）固定执行计划
.
这里是第一个测试，使用SQL Tuning Advisor来测试：
创建测试用例：

LUNAR@lunardb>create table lunartest1 (n number );

Table created.

Elapsed: 00:00:00.08
LUNAR@lunardb>begin
  2         for i in 1 .. 10000 loop
  3             insert into lunartest1 values(i);
  4             commit;
  5         end loop;
  6        end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.05
LUNAR@lunardb>create index idx_lunartest1_n on lunartest1(n);

Index created.

Elapsed: 00:00:00.04

执行查询，我们看到sql按照hint的方式没有使用索引，而是全表扫描，这是我们预期的结果：

LUNAR@lunardb>set autotrace on
LUNAR@lunardb>select /*+ no_index(lunartest1 idx_lunartest1_n) */ * from lunartest1 where n=1;

               N
----------------
               1

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1172089107

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |     1 |    13 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| LUNARTEST1 |     1 |    13 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("N"=1)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         49  consistent gets
          0  physical reads
          0  redo size
        519  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

LUNAR@lunardb>

下面我们运行SQL Tuning Advisor来生成建议报告：

LUNAR@lunardb>DECLARE
  2   my_task_name VARCHAR2(50);
  3  BEGIN
  4    my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
  5           SQL_ID      => 'bjgduva68mbqm',
  6           scope       => 'COMPREHENSIVE',
  7           time_limit  => 60,
  8           task_name   => 'Lunar_tunning_bjgduva68mbqm', 
  9           description => 'Task to tune a query on bjgduva68mbqm by Lunar');
 10    dbms_sqltune.execute_tuning_task(my_task_name);  
 11    dbms_output.put_line(my_task_name);  
 12  END;
 13  / 
Lunar_tunning_bjgduva68mbqm

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.20
LUNAR@lunardb>

查看生成的报告内容：

LUNAR@lunardb>SELECT dbms_sqltune.report_tuning_task('&task_name') FROM dual;  
Enter value for task_name: Lunar_tunning_bjgduva68mbqm
old   1: SELECT dbms_sqltune.report_tuning_task('&task_name') FROM dual
new   1: SELECT dbms_sqltune.report_tuning_task('Lunar_tunning_bjgduva68mbqm') FROM dual

DBMS_SQLTUNE.REPORT_TUNING_TASK('LUNAR_TUNNING_BJGDUVA68MBQM')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : Lunar_tunning_bjgduva68mbqm
Tuning Task Owner  : LUNAR
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 01/12/2016 10:09:54
Completed at       : 01/12/2016 10:09:54

-------------------------------------------------------------------------------
Schema Name: LUNAR
SQL ID     : bjgduva68mbqm
SQL Text   : select /*+ no_index(lunartest1 idx_lunartest1_n) */ * from
             lunartest1 where n=1

-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------

1- Statistics Finding
---------------------
  Table "LUNAR"."LUNARTEST1" was not analyzed.

  Recommendation
  --------------
  - Consider collecting optimizer statistics for this table.
    execute dbms_stats.gather_table_stats(ownname => 'LUNAR', tabname =>
            'LUNARTEST1', 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- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 91.31%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name =>
            'Lunar_tunning_bjgduva68mbqm', task_owner => 'LUNAR', replace =>
            TRUE);

  Validation results
  ------------------
  The SQL profile was tested by executing both its plan and the original plan
  and measuring their respective execution statistics. A plan may have been
  only partially executed if the other could be run to completion in less time.

                           Original Plan  With SQL Profile  % Improved
                           -------------  ----------------  ----------
  Completion Status:            COMPLETE          COMPLETE
  Elapsed Time (s):             .000196            .00002      89.79 %
  CPU Time (s):                   .0002                 0        100 %
  User I/O Time (s):                  0                 0
  Buffer Gets:                       23                 2       91.3 %
  Physical Read Requests:             0                 0
  Physical Write Requests:            0                 0
  Physical Read Bytes:                0                 0
  Physical Write Bytes:               0                 0
  Rows Processed:                     1                 1
  Fetches:                            1                 1
  Executions:                         1                 1

  Notes
  -----
  1. Statistics for the original plan were averaged over 10 executions.
  2. Statistics for the SQL profile plan were averaged over 10 executions.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 1172089107

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |     1 |     3 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| LUNARTEST1 |     1 |     3 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("N"=1)

2- Using SQL Profile
--------------------
Plan hash value: 3241900148

-------------------------------------------------------------------------------------
| Id  | Operation        | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                  |     1 |     3 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_LUNARTEST1_N |     1 |     3 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("N"=1)

-------------------------------------------------------------------------------


Elapsed: 00:00:00.06
LUNAR@lunardb>

这里我们看到SQL Tuning Advisor提示了两个建议：
1，收集统计信息：

    execute dbms_stats.gather_table_stats(ownname => 'LUNAR', tabname =>
            'LUNARTEST1', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
            method_opt => 'FOR ALL COLUMNS SIZE AUTO');
1
.
2，提供了一个执行计划建议：
1
    execute dbms_sqltune.accept_sql_profile(task_name =>
            'Lunar_tunning_bjgduva68mbqm', task_owner => 'LUNAR', replace =>
            TRUE);

并且给出了这个执行计划和原始执行计划的对比，可以看到 执行效率提高了89%以上，逻辑读从23降低为2，减少了91.3%。
.
下面我们按照建议执行。
首先收集统计信息：

LUNAR@lunardb>execute dbms_stats.gather_table_stats(ownname => 'LUNAR', tabname =>'LUNARTEST1', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.12
LUNAR@lunardb>

然后我们接受建议中的执行计划：

LUNAR@lunardb>execute dbms_sqltune.accept_sql_profile(task_name => 'Lunar_tunning_bjgduva68mbqm', task_owner => 'LUNAR', replace =>TRUE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.33
LUNAR@lunardb>

现在，再次查询看看效果：

LUNAR@lunardb>select /*+ no_index(lunartest1 idx_lunartest1_n) */ * from lunartest1 where n=1;

Elapsed: 00:00:00.08

Execution Plan
----------------------------------------------------------
Plan hash value: 3241900148

-------------------------------------------------------------------------------------
| Id  | Operation        | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                  |     1 |     3 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_LUNARTEST1_N |     1 |     3 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("N"=1)

Note
-----
   - SQL profile "SYS_SQLPROF_015236655fb80000" used for this statement


Statistics
----------------------------------------------------------
         37  recursive calls
          0  db block gets
         14  consistent gets
          1  physical reads
          0  redo size
        519  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

这里我们看到，这个执行计划中已经使用了索引，并且逻辑读从49降低为14,
但是这里还有物理读，因此，我们再次执行看看：

LUNAR@lunardb>/

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3241900148

-------------------------------------------------------------------------------------
| Id  | Operation        | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                  |     1 |     3 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_LUNARTEST1_N |     1 |     3 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("N"=1)

Note
-----
   - SQL profile "SYS_SQLPROF_015236655fb80000" used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        519  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

LUNAR@lunardb>

逻辑读从14降低为3，这个执行计划已经是我们需要的。
现在我们查看一下这个SQL Profile的OUTLINE：

LUNAR@lunardb>SELECT extractValue(value(h),'.') AS hint
  2  FROM sys.sqlobj$data od, sys.sqlobj$ so,
  3  table(xmlsequence(extract(xmltype(od.comp_data),'/outline_data/hint'))) h
  4  WHERE so.name = 'SYS_SQLPROF_015236655fb80000'
  5  AND so.signature = od.signature
  6  AND so.category = od.category
  7  AND so.obj_type = od.obj_type
  8  AND so.plan_id = od.plan_id;

HINT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COLUMN_STATS("LUNAR"."LUNARTEST1", "N", scale, length=3 distinct=10000 nulls=0 min=1 max=10000)
TABLE_STATS("LUNAR"."LUNARTEST1", scale, blocks=20 rows=10000)
OPTIMIZER_FEATURES_ENABLE(default)
IGNORE_OPTIM_EMBEDDED_HINTS

Elapsed: 00:00:00.05
LUNAR@lunardb>

这里我们看到该SQP Profile中提供了详细的表和列的统计信息
并且有“IGNORE_OPTIM_EMBEDDED_HINTS”，也就是忽略嵌入到SQL中的hint
结论：
虽然这个SQL的hint中指定了no index，即不使用索引，但是sql语句仍然按照SYS_SQLPROF_015236655fb80000指定的profile使用了index。
说明dbms_sqltune.accept_sql_profile方式绑定的执行计划优先级高于hint指定是否使用索引的方式。
不过不代表所有hint的优先级都低，还需要测试更多其他hint……

此条目发表在 未分类 分类目录。将固定链接加入收藏夹。

评论功能已关闭。