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;

• 1.
• 2.
• 3.

下面的测试表包含指示省和市存在的列。这些列上有检查约束，以便告诉优化器这些列被允许的值，此外，这些列上还有索引。通过包括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');

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.
• 22.
• 23.
• 24.
• 25.
• 26.
• 27.
• 28.
• 29.

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

登录后复制

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>

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.

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>

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.
• 22.
• 23.
• 24.
• 25.
• 26.
• 27.
• 28.
• 29.
• 30.
• 31.
• 32.
• 33.
• 34.
• 35.

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

下列查询检查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>

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.

由于该语句被标作可重新优化的，因此，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>

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.

我们可以等数据库把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>

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.
• 22.
• 23.
• 24.
• 25.
• 26.
• 27.
• 28.

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>

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.
• 22.
• 23.
• 24.
• 25.
• 26.
• 27.
• 28.
• 29.
• 30.
• 31.
• 32.
• 33.
• 34.
• 35.
• 36.
• 37.
• 38.
• 39.
• 40.
• 41.

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>

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.

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

登录后复制

COLUMN extension FORMAT A30
 
SELECT extension_name, extension
FROM  user_stat_extensions;

• 1.
• 2.
• 3.
• 4.

一旦需要的柱状图或扩展信息存在，或如果替代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>

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.

默认的，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过程。

 

原创作者: lhdzbj 转载于: https://blog.51cto.com/lhdzbj/11541741