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. 设置
确信共享池已经被清空,因此,我们知道在一个干净的系统上进行测试。
下面的测试表包含指示省和市存在的列。这些列上有检查约束,以便告诉优化器这些列被允许的值,此外,这些列上还有索引。通过包括10行A省和90行B省的样本来模拟倾斜严重的数据。
无论数据都没倾斜,当用默认设置收集统计信息时,不会为这些列创建柱状图。
2. 显示错误的势评估
大家都知道,所有省和市都是关联的。由于优化器并不知道这种关联关系,并且也没柱状图来标示数据的倾斜,接着优化器会利用现有信息尽力为谓词的选择性做出评估。于是,它会假设数据均匀分布在各允许值间且一半行被标为A省且一半被标为B省。如果在两个列之间没任何关联,预期100/2/2=25行被标为即是A省和X市。下列查询查出所有符合该规则的行,且显示返回数据的执行计划,包括预期的和实际的势。
正如预期的那样,优化器得到了一个错误的势评估。这是一个经典的案例,其中,柱状图,或扩展统计信息,或两者存在将有助于优化器能更精确的对势做出评估。
下列查询检查V$SQL视图的IS_REOPTIMIZABLE列,看优化器是否已注意到错误的势评估。
由于该语句被标作可重新优化的,因此,SQL计划指令也很可能已经被创建了。
3. 显示SQL计划指令
至此,优化器已经把SQL计划指令写入了SGA,但是我们还不能看到它们,因为它们还没被写入SYSAUX表空间。
我们可以等数据库把SQL计划指令保存,或用DBMS_SPD包手工保存。
4. 显示SQL计划指令的使用
下列查询是刚才我们产生SQL计划指令的那个。注意,势评估发生了改变,且在DBMS_XPLAN输出末尾出现了提醒。
5. SQL 计划指令和统计信息
SQL计划指令典型是一个“丢失信息” 的指示,但这些“丢失的信息”也许将来会变大的可用。如果我们重新收集表TAB1的统计信息,我们会注意到存在倾斜数据的列已被收集了统计信息。
如果收集了扩展统计信息,可用下面的语句查看它们。
一旦需要的柱状图或扩展信息存在,或如果替代SQL计划指令被创建,则该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过程。