Oracle 19C 管理SQL计划指令

原创 Asher.HU 2021-02-04

996

一个SQL计划指令，在附加信息和指令优化器可以用它来生成更优化的方案。

指令通知数据库优化器错误估计某些谓词类型的基数，并发出警报DBMS_STATS以在将来收集其他统计信息。因此，指令对统计信息的收集有影响。

数据库自动在SGA中创建和管理SQL计划指令，然后定期将它们写入数据字典。如果在53周内未使用这些指令，则数据库将自动清除它们。

您可以使用DBMS_SPD过程和函数来手动更改，保存，删除和传输指令。下表列出了一些更常用的过程和功能。

表12-6 DBMS_SPD过程

程序描述

程序	描述
`FLUSH_SQL_PLAN_DIRECTIVE`	强制数据库将指令从内存写入`SYSAUX`表空间中的持久性存储。
`DROP_SQL_PLAN_DIRECTIVE`	删除SQL计划指令。如果触发动态采样的指令造成了不可接受的性能开销，那么您可能需要手动将其删除。如果手动或自动删除了SQL计划指令，则数据库可以重新创建它。为防止重新创建，您可以使用`DBMS_SPM.ALTER_SQL_PLAN_DIRECTIVE`以下方法：通过设置`ENABLED`为禁用指令`NO` 通过设置`AUTO_DROP`为防止删除指令`NO` 要禁用SQL计划指令，请设置`OPTIMIZER_ADAPTIVE_STATISTICS`为`FALSE`。

FLUSH_SQL_PLAN_DIRECTIVE

强制数据库将指令从内存写入SYSAUX表空间中的持久性存储。

DROP_SQL_PLAN_DIRECTIVE

删除SQL计划指令。如果触发动态采样的指令造成了不可接受的性能开销，那么您可能需要手动将其删除。

如果手动或自动删除了SQL计划指令，则数据库可以重新创建它。为防止重新创建，您可以使用DBMS_SPM.ALTER_SQL_PLAN_DIRECTIVE以下方法：

通过设置ENABLED为禁用指令NO
通过设置AUTO_DROP为防止删除指令NO

要禁用SQL计划指令，请设置OPTIMIZER_ADAPTIVE_STATISTICS为FALSE。

先决条件

您必须具有“管理SQL管理对象”特权才能执行DBMS_SPD API。

假设条件

本教程假定您要执行以下操作：

将sh架构的所有指令写入持久性存储。
删除该sh架构的所有指令。

要编写然后删除所有sh模式计划指令：

在SQL * Plus或SQL Developer中，以具有必要特权的用户身份登录数据库。
强制数据库将SQL计划指令写入磁盘。
例如，执行以下DBMS_SPD程序：
```
BEGIN 
  DBMS_SPD.FLUSH_SQL_PLAN_DIRECTIVE;
END;
/
```
查询数据字典以获取有关sh架构中现有指令的信息。
例12-3在数据字典中查询有关指令的信息。
删除该sh模式的现有SQL计划指令。
以下PL / SQL程序单元删除ID为的SQL plan指令1484026771529551585：
```
BEGIN
  DBMS_SPD.DROP_SQL_PLAN_DIRECTIVE ( directive_id => 1484026771529551585 );
END;
/
```

示例12-3 sh模式的显示指令

本示例显示了SQL计划指令以及SQL计划指令动态采样查询的结果。

SELECT TO_CHAR(d.DIRECTIVE_ID) dir_id, o.OWNER, o.OBJECT_NAME, 
       o.SUBOBJECT_NAME col_name, o.OBJECT_TYPE object, 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 IN ('SH')
ORDER BY 1,2,3,4,5;
 
DIR_ID              OWN OBJECT_NA COL_NAME   OBJECT  TYPE     STATE      REASON
------------------- --- --------- ---------- ------- -------- ---------- ------------
1484026771529551585  SH CUSTOMERS COUNTRY_ID  COLUMN DYNAMIC_ SUPERSEDED SINGLE TABLE  
                                                     SAMPLING            CARDINALITY 
                                                                         MISESTIMATE
1484026771529551585  SH CUSTOMERS CUST_STATE_ COLUMN DYNAMIC_ SUPERSEDED SINGLE TABLE
                                  PROVINCE           SAMPLING            CARDINALITY 
                                                                         MISESTIMATE
1484026771529551585  SH CUSTOMERS              TABLE DYNAMIC_ SUPERSEDED SINGLE TABLE 
                                                     SAMPLING            CARDINALITY 
                                                                         MISESTIMATE
9781501826140511330  SH dyg4msnst5           SQL STA DYNAMIC_     USABLE VERIFY
                                             TEMENT  SAMPLING            CARDINALITY 
                                                     _RESULT             ESTIMATE
9872337207064898539  SH TIMES                  TABLE DYNAMIC_     USABLE VERIFY
                                                     SAMPLING            CARDINALITY 
                                                     _RESULT             ESTIMATE
9781501826140511330  SH 2nk1v0fdx0           SQL STA DYNAMIC_     USABLE VERIFY
                                             TEMENT  SAMPLING            CARDINALITY 
                                                     _RESULT             ESTIMATE

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Oracle 19C 管理SQL计划指令

评论