Oracle 19C 在存储大纲迁移后执行后续任务

原创 Asher.HU 2021-02-04

663

将大纲迁移到SQL计划基线之后，您必须执行一些后续工作。

该任务的目标如下：

将数据库配置为使用计划基线而不是已迁移到SQL计划基线的存储大纲的存储大纲
为将来的SQL语句创建SQL计划基准，而不是存储大纲
删除已迁移到SQL计划基线的存储大纲

本节说明如何设置与存储的轮廓和计划基线有关的初始化参数。将OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES与CREATE_STORED_OUTLINES初始化参数决定如何以及何时数据库中创建存储概要和SQL计划基线。表29-5说明了这些参数之间的相互作用。

表29-5创建轮廓线和基线

CREATE_STORED_OUTLINES初始化参数	OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES初始化参数	数据库行为
`false`	`false`	执行SQL语句时，数据库不会创建存储的大纲或SQL计划基线。
`false`	`true`	启用了对可重复SQL语句的自动识别以及这些语句的SQL计划基线的生成。执行SQL语句时，数据库仅使用`DEFAULT`该语句的类别名称创建新的SQL计划基线（如果不存在）。
`true`	`false`	Oracle数据库自动为会话期间提交的每个查询创建并存储大纲。执行SQL语句时，数据库仅使用`DEFAULT`该语句的类别名称创建新存储的大纲（如果不存在）。
`category`	`false`	执行SQL语句时，数据库仅使用该语句的指定类别名称创建新的存储大纲（如果不存在）。
`true`	`true`	Oracle数据库自动为会话期间提交的每个查询创建并存储大纲。还可以自动识别可重复的SQL语句，并为这些语句生成SQL计划基线。执行SQL语句时，数据库会使用类别名称创建存储的大纲和SQL计划基线`DEFAULT`。
`category`	`true`	Oracle数据库自动为会话期间提交的每个查询创建并存储大纲。还可以自动识别可重复的SQL语句，并为这些语句生成SQL计划基线。执行SQL语句时，数据库使用指定的类别名称创建存储的大纲，并使用类别名称创建SQL计划基线`DEFAULT`。

该USE_STORED_OUTLINES会话参数（这是不是一个初始化参数）和OPTIMIZER_USE_SQL_PLAN_BASELINES初始化参数确定数据库如何使用存储概要和计划基线。表29-6说明了这些参数如何相互作用。

表29-6存储大纲和SQL计划基准的使用

USE_STORED_OUTLINES会话参数	OPTIMIZER_USE_SQL_PLAN_BASELINES初始化参数	数据库行为
`false`	`false`	在为SQL语句选择计划时，数据库不使用存储的大纲或计划基线。
`false`	`true`	在为SQL语句选择计划时，数据库仅使用SQL计划基线。
`true`	`false`	为SQL语句选择计划时，数据库将使用类别名称为的存储轮廓`DEFAULT`。
`category`	`false`	在为SQL语句选择计划时，数据库将使用具有指定类别名称的存储大纲。如果不存在具有指定类别名称的存储大纲，则数据库将使用该`DEFAULT`类别中的存储大纲（如果存在）。
`true`	`true`	为SQL语句选择计划时，存储的大纲优先于计划基线。如果`DEFAULT`该语句存在具有类别名称的存储大纲，并且适用，则数据库将应用该存储大纲。否则，数据库将使用SQL计划基线。但是，如果存储的大纲具有属性`MIGRATED`，则数据库将不使用大纲，而是使用相应的SQL计划基线（如果存在）。
`category`	`true`	为SQL语句选择计划时，存储的大纲优先于计划基线。如果具有指定类别名称或`DEFAULT`类别的存储大纲适用于该语句并且适用，则数据库将应用存储的大纲。否则，数据库将使用SQL计划基线。但是，如果存储的大纲具有属性`MIGRATED`，则数据库将不使用大纲，而是使用相应的SQL计划基线（如果存在）。

假设条件

本教程假定以下内容：

您已完成存储大纲迁移中的基本步骤。
在Oracle Database 10g之前可能已经创建了一些存储的大纲。
Oracle Database 10g之前的版本中的提示使用本地提示格式。迁移之后，计划基线中存储的提示使用Oracle Database 10 g中引入的全局提示格式。

在迁移后将数据库置于适当的状态：

使用适当的权限将SQL * Plus连接到数据库，然后检查是否已由于迁移而创建了SQL计划基准。

确保已启用并接受计划。例如，输入以下查询（包括部分示例输出）：

SELECT SQL_HANDLE, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED, FIXED, MODULE
FROM   DBA_SQL_PLAN_BASELINES;

SQL_HANDLE                PLAN_NAME  ORIGIN         ENA ACC FIX MODULE
------------------------- ---------- -------------- --- --- --- ------
SYS_SQL_f44779f7089c8fab  STMT01     STORED-OUTLINE YES YES NO  DEFAULT
.
.
.

（可选）更改SQL计划基准的属性。

例如，以下语句将指定的SQL语句的基线状态更改为fixed：

DECLARE
  v_cnt PLS_INTEGER;
BEGIN 
  v_cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(               
                           sql_handle=>'SYS_SQL_f44779f7089c8fab', 
                           attribute_name=>'FIXED', 
                           attribute_value=>'NO');
  DBMS_OUTPUT.PUT_LINE('Plans altered: ' || v_cnt);
END;
/

检查原始存储轮廓的状态。

例如，输入以下查询（包括部分示例输出）：

SELECT NAME, OWNER, CATEGORY, USED, MIGRATED 
FROM   DBA_OUTLINES
ORDER BY NAME;

NAME       OWNER      CATEGORY   USED   MIGRATED
---------- ---------- ---------- ------ ------------
STMT01     SYS        DEFAULT    USED   MIGRATED
STMT02     SYS        DEFAULT    USED   MIGRATED
.
.
.

删除所有已迁移到SQL计划基线的存储大纲。

例如，以下语句将状态MIGRATED为的所有存储轮廓删除DBA_OUTLINES：

DECLARE
  v_cnt PLS_INTEGER;
BEGIN 
  v_cnt := DBMS_SPM.DROP_MIGRATED_STORED_OUTLINE();
  DBMS_OUTPUT.PUT_LINE('Migrated stored outlines dropped: ' || v_cnt);
END;
/

设置初始化参数，以便：
- 执行SQL语句时，数据库创建计划基线，但不创建存储的轮廓。
- 仅当不存在等效的SQL计划基准时，数据库才使用存储的大纲。
例如，以下SQL语句指示数据库在执行SQL语句时创建SQL计划基线，而不是存储的大纲。该示例还指示数据库以类别allrows或DEFAULT仅在存在且尚未迁移到SQL计划基线的情况下应用存储的大纲。在其他情况下，数据库将改用SQL计划基线。
```
ALTER SYSTEM 
  SET CREATE_STORED_OUTLINE = false SCOPE = BOTH;

ALTER SYSTEM 
  SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = true SCOPE = BOTH;

ALTER SYSTEM 
   SET OPTIMIZER_USE_SQL_PLAN_BASELINES = true SCOPE = BOTH;

ALTER SESSION
   SET USE_STORED_OUTLINES = allrows SCOPE = BOTH;
```

也可以看看：

“ 存储大纲迁移中的基本步骤 ”
Oracle Database PL / SQL软件包和类型参考以了解该DBMS_SPM软件包
Oracle数据库参考，了解CREATE_STORED_OUTLINES初始化参数

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

关注作者

Oracle 19C 在存储大纲迁移后执行后续任务

评论