SQL Plan Directives-part1

SQL PLAN Directives(SPD)在12.1.0.1版本被引入,目的是在优化器错误的估算出cardinality的时候进行矫正帮助优化器生成更优的执行计划。SPD是创建在表级或者列级,所以SPD可以被应用在表或者列匹配的SQL上。SPD存储在SYSAUX表空间上,所以从共享池中被age out的语句可以直接使用SPD而不需要重新进行检查(语法、语义)、解析、执行。
在sql编译期间,相应的SQL plan directives信息以判断是否缺少扩展统计信息或者直方图。如果缺少,优化器便会进行记录,并后续的调用DBMS_STATS进行收集。只要优化器没有足够的directive对应的统计信息,优化器都会通过dynamic statistics进行收集。例如, 优化器会在发现错误估算统计信息之后,通过dynamic statistics收集column group的统计信息。目前优化器还只能监控column group,无法监控表达式上的extension statistics。SPD不是绑定给特定的SQL语句或者SQL_ID上,优化器可以对nearly identical的语句使用SPD,因为SPD是定义在查询的表达式上。Oracle数据库自动去管理SPD,SPD最先创建在shared pool中,然后定期的由数据库刷新到SYSAUX表空间。Oracle提供了一个API去管理SPD,这个包叫DBMS_SPD.

实验环境Oracle 12.1.0.2
实验脚本

drop table emp;
create table emp(
  dept_no    not null,
  sal,
  emp_no    not null,
  padding,
  constraint e_pk primary key(emp_no)
)
as
with generator as (
  select  --+ materialize
    rownum     id
  from  dba_objects 
  where  rownum <= 1000
)
select
  /*+ ordered use_nl(v2) */
  mod(rownum,6),
  rownum,
  rownum,
  rpad('x',60)
from
  generator  v1,
  generator  v2
where
  rownum <= 20000
;

update emp set sal = 100 where dept_no = 3 and sal between 3 and 300;
commit;

select /*+ gather_plan_statistics */
count(*)
from EMP t
where dept_no = 3
and sal = 100;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

DEMO

SQL> select /*+ gather_plan_statistics */
  2   count(*)
  3    from EMP t
  4   where dept_no = 3
  5     and sal = 100;

  COUNT(*)
----------
        50

SQL>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
SQL_ID  24wfdz1mfp3fu, child number 0
-------------------------------------
select /*+ gather_plan_statistics */  count(*)   from EMP t  where
dept_no = 3    and sal = 100

Plan hash value: 2083865914

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |     220 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |     220 |
|*  2 |   TABLE ACCESS FULL| EMP  |      1 |      1 |     50 |00:00:00.01 |     220 |
-------------------------------------------------------------------------------------

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

   2 - filter(("SAL"=100 AND "DEPT_NO"=3))

估算和实际的cardinality差距比较大,cardinality的错误估算会造成优化器执行比较差的访问方式。因为默认情况下统计信息的收集是针对独立的列级的,优化器不会知道列之间的关系,也就不会知道列联合起来的统计信息是什么样的。

Oracle在v$sql视图中增加了SQL语句是否可以进行重新优化的列,is_reoptimizable,返回值为YES或者NO。

SQL> select sql_id,child_number,is_reoptimizable from v$sql where sql_id='&sql_id';
Enter value for sql_id: 24wfdz1mfp3fu

SQL_ID          CHILD_NUMBER      IS_REOPTIMIZABLE
--------------- ------------    --------------------------
24wfdz1mfp3fu              0       Y

视图V$sql_reoptimization_hints的hint_text列描述了如果进行优化。

SQL> col HINT_TEXT for a60
SQL>
SQL> select hash_value, sql_id, child_number, hint_text from V$sql_reoptimization_hints where sql_id='&sql_id';
Enter value for sql_id: 24wfdz1mfp3fu

HASH_VALUE SQL_ID          CHILD_NUMBER HINT_TEXT
---------- --------------- ------------ ------------------------------------------------------------
1726647770 24wfdz1mfp3fu              0 OPT_ESTIMATE (@"SEL$1" TABLE "T"@"SEL$1" ROWS=50.000000 )

这个SQL可以做为SPD进行优化的目标,使用DBMS_XPLAN + REPORT参数查看下经过优化器优化之后的执行计划会是什么样子。

SQL> select * from table(dbms_xplan.display_cursor('24wfdz1mfp3fu',null,'allstats +report'));

PLAN_TABLE_OUTPUT
---------------------------------------------------
SQL_ID  24wfdz1mfp3fu, child number 0
-------------------------------------
select /*+ gather_plan_statistics */  count(*)   from EMP t  where
dept_no = 3    and sal = 100

Plan hash value: 2083865914

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |     220 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |     220 |
|*  2 |   TABLE ACCESS FULL| EMP  |      1 |      1 |     50 |00:00:00.01 |     220 |
-------------------------------------------------------------------------------------

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

   2 - filter(("SAL"=100 AND "DEPT_NO"=3))


Reoptimized plan:
-----------------
This cursor is marked for automatic reoptimization.  The plan that is
expected to be chosen on the next execution is displayed below.

Plan hash value: 2083865914

-------------------------------------------
| Id  | Operation          | Name | Rows  |
-------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |
|   1 |  SORT AGGREGATE    |      |     1 |
|*  2 |   TABLE ACCESS FULL| EMP  |    50 |
-------------------------------------------

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

   2 - filter("SAL"=100 AND "DEPT_NO"=3)

查看一下SQL PLAN Directives

SQL> COLUMN dir_id FORMAT A20
SQL> COLUMN owner FORMAT A10
SQL> COLUMN object_name FORMAT A10
SQL> COLUMN col_name FORMAT A10
SQL> column type for a25
SQL> column state for a10
SQL> column reason for a36
SQL> SELECT TO_CHAR(d.DIRECTIVE_ID) dir_id, o.OWNER, o.OBJECT_NAME,
  2         o.SUBOBJECT_NAME col_name, o.OBJECT_TYPE, d.TYPE, d.STATE, d.REASON
  3  FROM   DBA_SQL_PLAN_DIRECTIVES d, DBA_SQL_PLAN_DIR_OBJECTS o
  4  WHERE  d.DIRECTIVE_ID=o.DIRECTIVE_ID
  5  AND    o.object_name = 'EMP'
  6  ORDER BY 1,2,3,4,5;

DIR_ID               OWNER      OBJECT_NAM COL_NAME   OBJECT_TYPE        TYPE                      STATE      REASON
-------------------- ---------- ---------- ---------- ------------------ ------------------------- ---------- ------------------------------------
10135714751416109735 BLUE       EMP        DEPT_NO    COLUMN             DYNAMIC_SAMPLING          USABLE     SINGLE TABLE CARDINALITY MISESTIMATE
10135714751416109735 BLUE       EMP        SAL        COLUMN             DYNAMIC_SAMPLING          USABLE     SINGLE TABLE CARDINALITY MISESTIMATE
10135714751416109735 BLUE       EMP                   TABLE              DYNAMIC_SAMPLING          USABLE     SINGLE TABLE CARDINALITY MISESTIMATE

刷新shared pool中的SPD信息到SYSAUX表空间下,刷新频率是每15分钟刷一次。

查询视图DBA_SQL_PLAN_DIRECTIVES和视图DBA_SQL_PLAN_DIR_OBJECTS

SQL> SELECT directive_id,
  2              state,
  3              last_used,
  4              auto_drop,
  5              reason,
  6              enabled,
  7              extract(notes, '/spd_note/spd_text/text()' )       spd_text,
  8              extract(notes, '/spd_note/internal_state/text()' ) internal_state,
  9        extract(notes, '/spd_note/redundant/text()')       redundant
 10       FROM   DBA_SQL_PLAN_DIRECTIVES
 11       WHERE  directive_id IN
 12              ( SELECT directive_id
 13                FROM   DBA_SQL_PLAN_DIR_OBJECTS
 14                WHERE  object_name = 'EMP' );

                     DIRECTIVE_ID STATE      LAST_US AUTO_DROP REASON                                                       ENABLED   SPD_TEXT                            INTERNAL_S REDUNDANT
--------------------------------- ---------- ------- --------- ------------------------------------------------------------ --------- ----------------------------------- ---------- ----------
             11418517542891894898 USABLE             YES       SINGLE TABLE CARDINALITY MISESTIMATE                         YES       {EC(BLUE.EMP)[DEPT_NO, SAL]}        NEW        NO

重新执行之前的查询语句来看下是否之前创建的SPD被应用在后续执行的SQL上。

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SQL_ID  24wfdz1mfp3fu, child number 1
-------------------------------------
select /*+ gather_plan_statistics */  count(*)   from EMP t  where
dept_no = 3    and sal = 100

Plan hash value: 2083865914

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |     220 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |     220 |
|*  2 |   TABLE ACCESS FULL| EMP  |      1 |     50 |     50 |00:00:00.01 |     220 |
-------------------------------------------------------------------------------------

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

   2 - filter(("SAL"=100 AND "DEPT_NO"=3))

Note
-----
   - statistics feedback used for this statement

此时估算的cardinality和实际的cardinality已经一致,但是SPD没有被使用。从执行计划输出中可以看到此SQL语句产生了第二个子游标,使用statistics feedback。
查看一下SPD内部的状态

SQL> SELECT directive_id,
  2              state,
  3              last_used,
  4              auto_drop,
  5              reason,
  6              enabled,
  7              extract(notes, '/spd_note/spd_text/text()' )       spd_text,
  8              extract(notes, '/spd_note/internal_state/text()' ) internal_state,
  9        extract(notes, '/spd_note/redundant/text()')       redundant
 10       FROM   DBA_SQL_PLAN_DIRECTIVES
 11       WHERE  directive_id IN
 12              ( SELECT directive_id
 13                FROM   DBA_SQL_PLAN_DIR_OBJECTS
 14                WHERE  object_name = 'EMP' );

DIRECTIVE_ID STATE      LAST_USED                           AUTO_DROP REASON                                                       ENABLED   SPD_TEXT                            INTERNAL_STATE                 REDUNDANT
---------- ----------------------------------- --------- ------------------------------------------------------------ --------- ----------------------------------- -----------------         ----------
 10135714751416109735 USABLE     05-SEP-16 04.09.52.000000000 PM     YES       SINGLE TABLE CARDINALITY MISESTIMATE         YES      {EC(BLUE.EMP)[DEPT_NO, SAL]}        MISSING_STATS                  NO

可以看到SPD的internal_state状态从NEW更新到MISSING_STATS,准备被使用相同的表/列/谓词的SQL使用。

更换谓词条件,SPD被使用。

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
SQL_ID  bfwr0uc4jdapw, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ count(*)   from blue.EMP t  where
dept_no = 3    and sal = 303

Plan hash value: 2083865914

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |     220 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |     220 |
|*  2 |   TABLE ACCESS FULL| EMP  |      1 |      1 |      1 |00:00:00.01 |     220 |
-------------------------------------------------------------------------------------

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

   2 - filter(("SAL"=303 AND "DEPT_NO"=3))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - 1 Sql Plan Directive used for this statement

可见SPD是针对表达式来进行优化的,不是针对语句级进行的。

收集统计信息

SQL> exec dbms_stats.gather_table_stats('BLUE','EMP')

PL/SQL procedure successfully completed.

SQL> select TABLE_NAME,EXTENSION_NAME,EXTENSION,CREATOR from dba_stat_extensions where table_name = 'EMP';

TABLE EXTENSION_NAME                      EXTENSION               CREATOR
----- ----------------------------------- ------------------- ------------------
EMP   SYS_STSJCVIM7JR7H9IGR$WQ11U9VO      ("DEPT_NO","SAL")        SYSTEM

DBMS_STATS包从SPD中获取到了丢失的统计信息,创建了extended statistics。

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

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s