签到成功

知道了

CNDBA社区CNDBA社区

12c新特性-SQL plan directives (SPD)

2016-10-27 11:47 3131 0 原创 Oracle 12C
作者： Expect-乐
本文链接：http://www.cndba.cn/Expect-le/article/309

1.SQL计划指令介绍

  我们知道统计反馈（SFB）收集的SQL运行时的统计信息会保存在相应的共享游标中，但却不能够持久化，

当数据库重启或者被优化的SQL文从内存中Age-out后，保存的信息就会丢失。下一次执行时还要重新进行一遍自动重新优化。

  为了缓解这个问题Oracle 12c推出了SQL指令计划（SQL Plan Directives 以后简称SPD）功能，保存为了以后生成最优http://www.cndba.cn/Expect-le/article/309

执行计划的一些指令和附加信息到字典表中，达到持久化的目的。http://www.cndba.cn/Expect-le/article/309

  虽然到目前为止，只有一种SQL计划指令就是动态采样指令（DYNAMIC_SAMPLING），用于指示优化器使用动态统计信息，

但相信以后该功能会进一步得到强化。

  SQL计划指令对于优化器来说就像"额外的笔记",提醒优化器现在正在使用一个不是最优的执行计划，典型的就是由于错误

的基数估计，错误的基数估计通常是由于缺少统计信息，足够的数据，复杂的谓词，复杂的操作造成的。

  另外，SQL计划指令（SQL Plan Directives）和sql profile有所不同，spd是基于查询表达式，而不是SQL语句，

所以同一个SQL计划指令（SQL Plan Directives），可以被优化器应用多个SQL文中。

  由12c自动重新优化（Automatic Reoptimization）相关功能，生成相关SQL计划指令（SPD）后，会暂时把信息保存在内存的共享池中。


  Oracle的后台进程会每隔15分钟把SQL计划指令（SPD）相关信息从共享池中保存到SYSAUX表空间内相关的字典表中。 

当然,你也可以通过手动调用DBMSSPD.FLUSHSQLPLANDIRECTIVE函数，实时地把相关信息从共享池中保存到相关的字典表中。

可以通过DBA_SQL_PLAN_DIRECTIVES and DBA_SQL_PLAN_DIR_OBJECTS视图查看SQL计划指令的详细信息。

--手动刷新数据到数据字典

http://www.cndba.cn/Expect-le/article/309

EXEC DBMS_SPD.FLUSH_SQL_PLAN_DIRECTIVE;

  当然这个保留时间也不是无限制的，如果在53周时间内该SQL计划指令没有被使用过，那么就会被自动删除掉。

--查看SQL计划指令（SPD）的保存期间http://www.cndba.cn/Expect-le/article/309


SQL> SELECT DBMS_SPD.GET_PREFS('SPD_RETENTION_WEEKS') FROM dual;
DBMS_SPD.GET_PREFS('SPD_RETENTION_WEEKS')
-----------------------------------------
53


-- 修改保存期间

SQL> exec DBMS_SPD.SET_PREFS('SPD_RETENTION_WEEKS',10)


http://www.cndba.cn/Expect-le/article/309


-- 查看修改后的保存期间

SQL> SELECT DBMS_SPD.GET_PREFS('SPD_RETENTION_WEEKS') FROM dual;
DBMS_SPD.GET_PREFS('SPD_RETENTION_WEEKS')
-----------------------------------------
10


2.测试部分

2.1清空共享池

SQL> alter session set container=zhixin;
Session altered.
SQL> alter system flush shared_pool;
System altered.

2.2测试数据准备

CREATE TABLE tab1 (
  id               NUMBER,
  gender           VARCHAR2(1),
  has_y_chromosome VARCHAR2(1),
  CONSTRAINT tab1_pk PRIMARY KEY (id),
  CONSTRAINT tab1_gender_chk CHECK (gender IN ('M', 'F')),
  CONSTRAINT tab1_has_y_chromosome_chk CHECK (has_y_chromosome IN ('Y', 'N'))
);


INSERT /*+ APPEND */ INTO tab1
SELECT level, 'M', 'Y'
FROM   dual
CONNECT BY level <= 10;
COMMIT;


INSERT /*+ APPEND */ INTO tab1
SELECT 10+level, 'F', 'N'
FROM   dual
CONNECT BY level <= 90;
COMMIT;
CREATE INDEX tab1_gender_idx ON tab1(gender);
CREATE INDEX tab1_has_y_chromosome_idx ON tab1(has_y_chromosome);
EXEC DBMS_STATS.gather_table_stats(USER, 'TAB1');

2.3查看直方图

#尽管数据有倾斜，但是使用默认设置收集数据的时候没有产生直方图。

SQL> COLUMN column_name FORMAT A20
SQL> SELECT column_id,
  2         column_name,
       histogram
FROM   user_tab_columns
  5  WHERE  table_name = 'TAB1'
  6  ORDER BY column_id;
 COLUMN_ID COLUMN_NAME HISTOGRAM
---------- -------------------- ------------------------------
  1     ID      NONE
  2     GENDER      NONE
  3     HAS_Y_CHROMOSOME  NONE

2.4查看执行计划

  常识我们都知道男性是有一个Y染色体的，而女的是没有的。但是优化器是不知道这个关系的，

所以它没有直方图去表示数据的倾斜。所以对于谓词的选择性不会做出最好的估计。假设数据是

均匀的，那么应该一半是男性，而且有Y染色体。

SELECT /*+ GATHER_PLAN_STATISTICS */
  2         *
FROM   tab1
  4  WHERE  gender = 'M'
  5  AND    has_y_chromosome = 'Y';
 ID GE HA
---------- -- --
  1 M  Y
  2 M  Y
  3 M  Y
  4 M  Y
  5 M  Y
  6 M  Y
  7 M  Y
  8 M  Y
  9 M  Y
 10 M  Y
10 rows selected.


SQL> SET LINESIZE 200 PAGESIZE 100
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
SQL_ID  5t8y8p5mpb99j, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */        * FROM   tab1 WHERE  gender
= 'M' AND    has_y_chromosome = 'Y'
Plan hash value: 1552452781
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |      1 |        |     10 |00:00:00.01 |       4 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TAB1            |      1 |     25 |     10 |00:00:00.01 |       4 |
|*  2 |   INDEX RANGE SCAN                  | TAB1_GENDER_IDX |      1 |     50 |     10 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("HAS_Y_CHROMOSOME"='Y')
   2 - access("GENDER"='M')
21 rows selected.

  如预期一样，优化器确实得到了错误的基数估计( E-Rows 的值)。这个是个典型的案例直方图,扩展的统计信息

http://www.cndba.cn/Expect-le/article/309
http://www.cndba.cn/Expect-le/article/309

会帮助优化器更准确的估计基数。

2.5查看sql是否被重新优化

  查看V$SQL视图中IS_REOPTIMIZABLE列表示优化器是否注意到了错误的基数估计。http://www.cndba.cn/Expect-le/article/309

COLUMN sql_text FORMAT A40
COLUMN is_reoptimizable FORMAT A16
SELECT sql_text, is_reoptimizable
FROM   v$sql
WHERE  sql_id = '5t8y8p5mpb99j';
SQL_TEXT                                 IS_REOPTIMIZABLE
---------------------------------------- ----------------
SELECT /*+ GATHER_PLAN_STATISTICS */           Y
    * FROM   tab1 WHERE  gender = 'M' AN
D    has_y_chromosome = 'Y'
SQL>

Y表示，该SQL被标记为重新优化，也很有肯能是已经创建了SQL计划指令。

3.查看SQL计划指令(SPD)

  现在优化计划指令还在SGA中，所以我们还是看不见的。

SET LINESIZE 200


COLUMN dir_id FORMAT A20
COLUMN owner FORMAT A10
COLUMN object_name FORMAT A10
COLUMN col_name FORMAT A10
SELECT TO_CHAR(d.directive_id) dir_id, o.owner, o.object_name, 
       o.subobject_name col_name, o.object_type, d.type, d.state, d.reason
FROM   dba_sql_plan_directives d, dba_sql_plan_dir_objects o
WHERE  d.directive_id=o.directive_id
AND    o.owner = 'LEI'
ORDER BY 1,2,3,4,5;
no rows selected
SQL>

可以等数据库自动刷新，也可以手动刷新数据到数据字典中。.

SQL> EXEC DBMS_SPD.flush_sql_plan_directive;
PL/SQL procedure successfully completed.


SELECT TO_CHAR(d.directive_id) dir_id, o.owner, o.object_name, 
       o.subobject_name col_name, o.object_type, d.type, d.state, d.reason
FROM   dba_sql_plan_directives d, dba_sql_plan_dir_objects o
WHERE  d.directive_id=o.directive_id
AND    o.owner = 'LEI'
ORDER BY 1,2,3,4,5;
DIR_ID               OWNER      OBJECT_NAM COL_NAME   OBJECT TYPE             STATE      REASON
-------------------- ---------- ---------- ---------- ------ ---------------- ---------- ------------------------------------
11749925840852248025 LEI       TAB1       GENDER     COLUMN DYNAMIC_SAMPLING USABLE     SINGLE TABLE CARDINALITY MISESTIMATE
11749925840852248025 LEI       TAB1                  TABLE  DYNAMIC_SAMPLING USABLE     SINGLE TABLE CARDINALITY MISESTIMATE
SQL>

4.查看SQL计划指令使用情况

  再次执行这个已经产生过sql计划指令的sql,注意后面输出部分的信息。http://www.cndba.cn/Expect-le/article/309

SQL> SELECT /*+ GATHER_PLAN_STATISTICS */
       *
FROM   tab1
WHERE  gender = 'M'
AND    has_y_chromosome = 'Y';
SQL> SET LINESIZE 200 PAGESIZE 100
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5t8y8p5mpb99j, child number 1
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */     * FROM   tab1 WHERE  gender
= 'M' AND    has_y_chromosome = 'Y'
Plan hash value: 1552452781
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation     | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |      1 | |     10 |00:00:00.01 |       4 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TAB1       |      1 |     10 |     10 |00:00:00.01 |       4 |
|*  2 |   INDEX RANGE SCAN     | TAB1_GENDER_IDX |      1 |     10 |     10 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("HAS_Y_CHROMOSOME"='Y')
   2 - access("GENDER"='M')
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - statistics feedback used for this statement
   - 1 Sql Plan Directive used for this statement
27 rows selected.
SQL>

note部分提示很清楚，一个sql计划指令被使用了。

5.SQL计划指令和统计信息

  SQL计划指令就是一个通常表明"丢失的信息"。这些信息可能会在未来变成有用的，如果重新收集表tab1的统计信息，http://www.cndba.cn/Expect-le/article/309

可以看到直方图了。

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'TAB1');
PL/SQL procedure successfully completed.
SQL> SELECT column_id,
       column_name,
       histogram
FROM   user_tab_columns
WHERE  table_name = 'TAB1'
ORDER BY column_id;
 COLUMN_ID COLUMN_NAME          HISTOGRAM
---------- -------------------- ---------------
         1 ID                   NONE
         2 GENDER               FREQUENCY
         3 HAS_Y_CHROMOSOME     FREQUENCY
SQL>

#如果有扩展的统计信息，可以通过下面查询看到

SELECT extension_name,extension FROM user_stat_extensions;

一旦所需的直方图或扩展的统计信息是存在的,或者另一个SQL计划指令被创建,SQL计划指示可能变成SUPERSEDED和no longer used状态。

SELECT state, COUNT(*)
FROM   dba_sql_plan_directives
GROUP BY state
  4  ORDER BY state;
STATE        COUNT(*)
-------------------- ----------
SUPERSEDED     129
USABLE     157
SQL>


官方文档：http://docs.oracle.com/database/121/TGSQL/tgsql_statscon.htm#TGSQL347




版权声明：本文为博主原创文章，未经博主允许不得转载。

SQL plan directives

用户评论
* 以下用户言论只代表其个人观点，不代表CNDBA社区的观点或立场
Expect-乐

Expect-乐

关注

Without the continuous bitter cold, there can be no fragrant plum blossom

  • 336
    原创
  • 6
    翻译
  • 100
    转载
  • 41
    评论
  • 访问：1601396次
  • 积分：1957
  • 等级：核心会员
  • 排名：第4名
精华文章

    达梦数据库运维实战

    Oracle 18c 必须掌握的新特性：管理与实战

    Oracle数据库问题解决方案和故障排除手册
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        友情链接： CNDBA社区 | openGauss | openEuler | openAnolis | 广告联系: database@ustc.edu
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ