Oracle12c中SQL优化（SQL TUNING）新特性之SQL计划指令

SQL计划指令是Oracle12c中自适应查询优化的功能之一。SQL计划指令就像“额外的提醒” ，用以提醒优化器你先前选择了的计划并不是最优的，典型的是因为错误的势评估。错误的势评估往往是由统计信息缺失，统计信息陈旧，复杂的谓词或操作等引起的。SQL计划指令和SQL轮廓（SQL profiles）不同，后者是针对特定语句的，而SQL计划指令和查询表达式关联，因此，它们可以被多个包含匹配查询表达式的语句共享。柱状图或扩展统计信息缺失也许会导致SQL计划指令的产生。

数据库在内部自动管理SQL计划指令。像自动重优化的情况也许会引起SQL计划指令被写入SGA且稍后写到SYSAUX表空间，此时，可以通过DBA_SQL_PLAN_DIRECTIVES和DBA_SQL_PLAN_DIR_OBJECTS视图来查看SQL计划指令。此外，已存在的SQL计划指令也能通过DBMS_SPD包来保存。

1.   设置

确信共享池已经被清空，因此，我们知道在一个干净的系统上进行测试。

CONN sys@pdb1 AS SYSDBA

 

ALTER SYSTEM FLUSH SHARED_POOL;

下面的测试表包含指示省和市存在的列。这些列上有检查约束，以便告诉优化器这些列被允许的值，此外，这些列上还有索引。通过包括10行A省和90行B省的样本来模拟倾斜严重的数据。

CONN test/test@pdb1

 

DROP TABLE tab1 PURGE;

 

CREATE TABLE tab1 (

 id               NUMBER,

  Province           VARCHAR2(10),

city VARCHAR2(10),

 CONSTRAINT tab1_pk PRIMARY KEY (id),

 CONSTRAINT tab1_prov_chk CHECK (province IN ('A', 'B')),

  CONSTRAINTtab1_city_chk CHECK (city IN ('X', 'Y'))

);

 

INSERT /*+ APPEND */ INTO tab1

SELECT level, 'A', 'X'

FROM  dual

CONNECT BY level <= 10;

COMMIT;

 

INSERT /*+ APPEND */ INTO tab1

SELECT 10+level, 'B', 'Y'

FROM  dual

CONNECT BY level <= 90;

COMMIT;

 

CREATE INDEX tab1_prov_idx ON tab1(province);

CREATE INDEX tab1_city_idx ON tab1(city);

 

EXEC DBMS_STATS.gather_table_stats(USER,'TAB1');

无论数据都没倾斜，当用默认设置收集统计信息时，不会为这些列创建柱状图。

COLUMN column_name FORMAT A20

 

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 PROVINCE             NONE

        3 CITY                 NONE

 

SQL>

2.   显示错误的势评估

大家都知道，所有省和市都是关联的。由于优化器并不知道这种关联关系，并且也没柱状图来标示数据的倾斜，接着优化器会利用现有信息尽力为谓词的选择性做出评估。于是，它会假设数据均匀分布在各允许值间且一半行被标为A省且一半被标为B省。如果在两个列之间没任何关联，预期100/2/2=25行被标为即是A省和X市。下列查询查出所有符合该规则的行，且显示返回数据的执行计划，包括预期的和实际的势。

CONN test/test@pdb1

 

SELECT /*+ GATHER_PLAN_STATISTICS */

      *

FROM  tab1

WHERE  province= 'A'

AND    city= 'X';

 

SET LINESIZE 200 PAGESIZE 100

SELECT * FROMTABLE(DBMS_XPLAN.display_cursor(format => 'allstats last'));

 

PLAN_TABLE_OUTPUT

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

SQL_ID 5t8y8p5mpb99j, child number 0

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

SELECT /*+ GATHER_PLAN_STATISTICS */        *FROM   tab1 WHERE  province

= 'A' AND   city = 'X'

 

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_PROV_IDX |      1 |    50 |     10 |00:00:00.01 |       2 |

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

 

Predicate Information (identified byoperation id):

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

 

   1 -filter("CITY"='X')

   2 -access("PROVINCE"='A')

 

SQL>

正如预期的那样，优化器得到了一个错误的势评估。这是一个经典的案例，其中，柱状图，或扩展统计信息，或两者存在将有助于优化器能更精确的对势做出评估。

下列查询检查V$SQL视图的IS_REOPTIMIZABLE列，看优化器是否已注意到错误的势评估。

CONN sys@pdb1 AS SYSDBA

 

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 */     

    *FROM   tab1 WHERE  province = 'A' AN

D    city= 'X'

 

SQL>

由于该语句被标作可重新优化的，因此，SQL计划指令也很可能已经被创建了。

3.   显示SQL计划指令

至此，优化器已经把SQL计划指令写入了SGA，但是我们还不能看到它们，因为它们还没被写入SYSAUX表空间。

CONN sys@pdb1 AS SYSDBA

 

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 = 'TEST'

ORDER BY 1,2,3,4,5;

 

no rows selected

 

SQL>

我们可以等数据库把SQL计划指令保存，或用DBMS_SPD包手工保存。

CONN sys@pdb1 AS SYSDBA

 

EXEC DBMS_SPD.flush_sql_plan_directive;

现在我们运行前面的查询，将会看到SQL计划指令。

CONN sys@pdb1 AS SYSDBA

 

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 = 'TEST'

ORDER BY 1,2,3,4,5;

 

DIR_ID               OWNER     OBJECT_NAM COL_NAME   OBJECTTYPE             STATE      REASON

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

12422623998396966202 TEST       TAB1     PROVINCE     COLUMN DYNAMIC_SAMPLING USABLE     SINGLE TABLE CARDINALITY MISESTIMATE

12422623998396966202 TEST       TAB1                  TABLE  DYNAMIC_SAMPLING USABLE     SINGLE TABLE CARDINALITY MISESTIMATE

 

SQL>

4.   显示SQL计划指令的使用

下列查询是刚才我们产生SQL计划指令的那个。注意，势评估发生了改变，且在DBMS_XPLAN输出末尾出现了提醒。

CONN test/test@pdb1

 

SELECT /*+ GATHER_PLAN_STATISTICS */

      *

FROM  tab1

WHERE  province= 'A'

AND    city= 'X';

 

SET LINESIZE 200 PAGESIZE 100

SELECT * FROMTABLE(DBMS_XPLAN.display_cursor(format => 'allstats last'));

 

PLAN_TABLE_OUTPUT

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

SQL_ID 5t8y8p5mpb99j, child number 1

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

SELECT /*+ GATHER_PLAN_STATISTICS */        *FROM   tab1 WHERE  province

= 'A' AND   city = 'X'

 

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_PROV_IDX |      1 |    10 |     10 |00:00:00.01 |       2 |

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

 

Predicate Information (identified byoperation id):

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

 

   1 -filter("CITY"='X')

   2 -access("PROVINCE"='A')

 

Note

-----

   -dynamic statistics used: dynamic sampling (level=2)

   -statistics feedback used for this statement

   - 1Sql Plan Directive used for this statement

 

SQL>

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

SQL计划指令典型是一个“丢失信息” 的指示，但这些“丢失的信息”也许将来会变大的可用。如果我们重新收集表TAB1的统计信息，我们会注意到存在倾斜数据的列已被收集了统计信息。

EXEC DBMS_STATS.gather_table_stats(USER,'TAB1');

 

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 PROVINCE             FREQUENCY

        3 CITY                 FREQUENCY

 

SQL>

如果收集了扩展统计信息，可用下面的语句查看它们。

COLUMN extension FORMAT A30

 

SELECT extension_name, extension

FROM  user_stat_extensions;

一旦需要的柱状图或扩展信息存在，或如果替代SQL计划指令被创建，则该SQL计划指令也许就会切换到一个替代状态而不再被使用。

CONN sys@pdb1 AS SYDBA

 

SELECT state, COUNT(*)

FROM  dba_sql_plan_directives

GROUP BY state

ORDER BY state;

 

STATE       COUNT(*)

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

SUPERSEDED         26

USABLE             44

 

SQL>

默认的，SQL计划指令超过53周不被使用就会被删除。而某些SQL计划指令会对列组统计信息的收集有影响。

6.   DBMS_SPD包

DBMS_SPD包可以在一定程度上控制SQL计划指令。

可以用以下过程来进行基本的SQL计划指令管理。

1)     FLUSH_SQL_PLAN_DIRECTIVE：将SGA中的SQL计划指令存入SYSAUX表空间。

2)     ALTER_SQL_PLAN_DIRECTIVE：更改确定SQL计划指令的ENABLED和AUTO_DROP属性。

3)     DROP_SQL_PLAN_DIRECTIVE：删除确定的SQL计划指令。

SQL计划指令不使用而被保留的最长时间通过 SPD_RETENTION_WEEKS确定，默认为53周。这可以用如下过程显示和修改。

1)     GET_PREFS ：显示确定的偏好（preference）。目前只支持SPD_RETENTION_WEEKS。

2)     SET_PREFS ：设置确定的偏好(preference)。目前只支持SPD_RETENTION_WEEKS。

SQL计划执行的长期存储和迁移可以用如下过程。

1)     CREATE_STGTAB_DIRECTIVE：创建一个将SQL计划指令导出到的中间表。

2)     PACK_STGTAB_DIRECTIVE：将SQL计划指令导出到指定表。

3)     UNPACK_STGTAB_DIRECTIVE：从中间表导入SQL计划指令。

DBMS_SPD包的很多功能需要ADMINISTER SQL MANAGEMENTOBJECT权限。

7.   注释：

有时SQL计划指令内部管理机制看起来有些混乱。测试期间，我遇到过本来希望它被创建但却没有。也遇到过本希望存在的SQL计划指令标作被替代状态但却没有。开始我认为这可能是因为我忘了保存，但情况似乎并非如此。

记得查询 DBA_SQL_PLAN_DIRECTIVES和DBA_SQL_PLAN_DIR_OBJECTS视图前总是运行DBMS_SPD.FLUSH_SQL_PLAN_DIRECTIVE过程。