本文介绍了Oracle SQL Plan Management（SPM），包括计划捕获、选择和演进，以及SQL计划基线的工作原理。SPM是一种预防性机制，确保数据库仅使用已验证的执行计划，防止性能下降。主要组件包括计划捕获（自动或手动），计划选择（优化器根据历史记录选择适当计划）和计划演进（新计划的验证和添加）。SQL计划基线存储了已接受的执行计划，优化器在选择计划时会参考。配置SPM涉及捕获和使用计划基准的参数设置，以及使用DBMS_SPM软件包进行管理。
文章目录
参考
- 【⭐️官网】Managing SQL Plan Baselines https://docs.oracle.com/database/121/TGSQL/tgsql_spm.htm#TGSQL94621
- Oracle SQL执行计划基线总结（SQL Plan Baseline） https://blog.csdn.net/oradh/article/details/30456915
计划基准（SQL plan baselines）是允许优化器用于SQL语句的一组可接受的计划。
（ plan baseline is a set of accepted plans that the optimizer is allowed to use for a SQL statement）
关于SQL计划管理（SQL Plan Management，SPM）
SQL计划管理（SQL Plan Management）是一种预防性机制（preventative mechanism），可使优化器自动管理执行计划，从而确保数据库仅使用已知（known）或经过验证的（verified）计划。
SQL计划管理的目的（Purpose）
- SQL计划管理的主要目标是防止由于计划更改而导致性能下降。
- 第二个目标是通过仅验证和接受可提高性能的计划更改来适应新的优化程序统计信息或索引之类的更改。
SQL计划管理使用一种称为SQL计划基线的机制。在典型的用例中，只有在确认计划执行良好之后，数据库才会将计划接受（accepts）到计划基准中。在这种情况下，计划包括优化器重现执行计划所需的所有与计划相关的信息（例如，SQL计划标识符，提示集，绑定值和优化器环境） (for example, SQL plan identifier, set of hints, bind values, and optimizer environment) 。
SQL计划管理的主要组件（ main components）如下：
-
计划捕获（Plan capture）
该组件存储有关一组SQL语句的计划的相关信息。请参阅“计划捕获”。 -
计划选择（Plan selection）
该组件是优化程序根据存储的计划历史记录来检测计划变更的步骤，以及使用SQL计划基准来选择适当的计划以避免潜在的性能下降的方法。请参阅“计划选择”。 -
计划演进（Plan evolution）
该组件是手动或自动将新计划添加到现有SQL计划基准的过程。请参阅“计划演变”。
# 计划捕获（Plan capture）
SQL计划捕获是指用于在一组SQL语句的SQL管理库中捕获和存储有关计划的相关信息的技术。捕获计划意味着使SQL计划管理人员意识到该计划。
您可以通过设置初始化参数将初始计划捕获配置为自动发生，也可以使用DBMS_SPM软件包手动捕获计划。
自动初始计划捕获
您可以通过将初始化参数OPTIMIZER_CAPTURE_SQL_PLAN_BASELINEStruefalse设置为true（默认值为false）来启用自动初始计划捕获。启用后，数据库将自动为在数据库上执行的任何可重复SQL语句创建一个SQL计划基线。
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES的设置和OPTIMIZER_USE_SQL_PLAN_BASELINES独立。例如，如果OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES为true，则数据库创建初始计划基线，而不管是否OPTIMIZER_USE_SQL_PLAN_BASELINES为true或false。
OPTIMIZER_USE_SQL_PLAN_BASELINES（默认启用）对存储在SQL Management Base中的SQL计划基准的使用。
启用后，优化器将为正在编译的SQL语句查找SQL计划基线。如果在SQL Management Base中找到了一个，则优化器将对每个基准计划进行成本估算，并选择成本最低的方案。
手动计划捕获
在SQL计划管理中，手动计划捕获是指用户启动的现有计划的批量加载到SQL计划基准中。
使用Cloud Control或PL / SQL从SQL调整集（SQL tuning set, STS），共享SQL区域（shared SQL area），登台表（a staging table）或存储的大纲（stored outline）中加载SQL语句的执行计划。
下图说明了将计划加载到SQL计划基线中。
加载行为因批量加载中表示的每个语句是否存在SQL计划基准而有所不同：
- 如果该语句的基准不存在，那么数据库将执行以下操作：
（If a baseline for the statement does not exist, then the database does the following）- 为报表创建计划历史记录和计划基线
（Creates a plan history and plan baseline for the statement） - 将声明的初始计划标记为已接受
（Marks the initial plan for the statement asaccepted） - 将计划添加到新基准
（Adds the plan to the new baseline）
- 为报表创建计划历史记录和计划基线
- 如果存在该语句的基准，那么数据库将执行以下操作：
（If a baseline for the statement exists, then the database does the following）- 将加载的计划标记为已接受
（Marks the loaded plan asaccepted） - 将计划添加到报表的计划基线，而不验证计划的绩效
（Adds the plan to the plan baseline for the statement without verifying the plan’s performance）
- 将加载的计划标记为已接受
手动加载的计划始终标记为已接受，因为优化器假定管理员手动加载的任何计划都具有可接受的性能。
# 计划选择（Plan Selection）
SQL计划选择是优化程序的功能，它可以基于存储的计划历史记录来检测计划更改，并使用SQL计划基准来选择计划以避免潜在的性能下降。
当数据库对SQL语句执行硬解析（hard parse）时，优化器将生成最佳成本计划。默认情况下，优化器然后尝试在SQL计划基线中为该语句找到匹配的计划。如果不存在计划基准，那么数据库将使用最佳成本计划运行该语句。
如果存在计划基准，那么优化程序的行为取决于新生成的计划是否在计划基准中：
（If a plan baseline exists, then the optimizer behavior depends on whether the newly generated plan is in the plan baseline）
- 如果新计划在基线中，则数据库使用找到的计划执行该语句。
（If the new plan is in the baseline, then the database executes the statement using the found plan） - 如果新计划不在基线中，则优化器会将新生成的计划标记为不接受，并将其添加到计划历史记录中。优化程序的行为取决于计划基准的内容：
（If the new plan is not in the baseline, then the optimizer marks the newly generated plan as unaccepted and adds it to the plan history. Optimizer behavior depends on the contents of the plan baseline:）- 如果计划基线中存在固定计划，则优化器将使用成本最低的固定计划。
（If fixed plans exist in the plan baseline, then the optimizer uses the fixed plan with the lowest cost） - 如果计划基线中不存在固定计划，那么优化器将使用成本最低的基线计划。
（If no fixed plans exist in the plan baseline, then the optimizer uses the baseline plan with the lowest cost） - 如果计划基线中不存在可复制的计划，并且如果基线中的每个计划都引用了掉落的索引，则可能会发生这种情况，那么优化器将使用新生成的基于成本的计划。
（If no reproducible plans exist in the plan baseline, which could happen if every plan in the baseline referred to a dropped index, then the optimizer uses the newly generated cost-based plan）
- 如果计划基线中存在固定计划，则优化器将使用成本最低的固定计划。
图23-3选择SQL计划的决策树
# 计划演变（Plan evolution）
通常，SQL计划演变是优化程序验证新计划并将其添加到现有SQL计划基准的过程。
具体来说，计划制定包括以下不同的步骤：
- 验证
未接受的计划在SQL计划基线中的执行性能至少与接受的计划相同（称为计划验证）
（Verifying thatunacceptedplans perform at least as well as accepted plans in a SQL plan baseline (known as plan verification)） - 在数据库证明其性能和接受的计划一样好之后，将未接受的计划添
加到计划基线作为接受的计划
（Addingunaccepted plans to the plan baseline as accepted plans after the database has proved that they perform as well as accepted plans）
在计划演变的标准情况下，优化器将按顺序执行前面的步骤，以便SQL计划管理无法使用新计划，直到优化器验证相对于SQL计划基线的计划性能。但是，您可以将SQL计划管理配置为执行一个步骤而不执行另一个步骤。下图显示了计划制定的可能路径。
图23-4计划演进
计划发展的目的
通常，SQL语句的SQL计划基线从单个接受的计划开始。但是，某些SQL语句在不同条件下以不同的计划执行时，效果很好。
例如，具有绑定变量（其值导致不同的选择性）的SQL语句可能具有多个最佳计划。创建实例化视图或索引或对表进行分区可能会使当前计划比其他计划昂贵。
如果从未将新计划添加到SQL计划基准中，那么某些SQL语句的性能可能会降低。因此，有时有必要将新接受的计划演变为SQL计划基准。计划演进通过在将新计划包括在SQL计划基准中之前对其进行验证来防止性能下降。
SQL计划管理的存储体系结构（Storage Architecture）
SQL计划管理基础结构记录已解析语句的签名以及接受和不接受的计划。
# SQL管理基础（SQL Management Base，SMB）
在SQL管理碱（SMB）是在数据字典的逻辑存储库。
（The SQL management base (SMB) is a logical repository in the data dictionary.）
SMB包含以下内容：
- SQL语句日志，仅包含SQL ID
（SQL statement log, which contains only SQL IDs） - SQL计划历史记录，其中包括SQL计划基线
（SQL plan history, which includes the SQL plan baselines） - SQL配置文件
（SQL profiles） - SQL补丁
（SQL patches）
SMB存储信息，优化器可使用该信息来维护或改善SQL性能。
SMB驻留在SYSAUX表空间中，并使用自动段空间管理（automatic segment-space management）。由于SMB完全位于SYSAUX表空间内，因此当该表空间不可用时，数据库将不使用SQL计划管理和SQL调整功能。
图23-5 SMB架构
Note:
- 将SMB与可插入数据库一起使用时，数据可见性和特权要求可能会有所不同。请参阅《Oracle数据库管理指南》中的表，该表总结了可管理性功能在容器数据库（CDB）中的工作方式。
- 《 Oracle数据库管理员指南》以了解SYSAUX表空间
SQL语句日志（SQL statement log）
启用自动SQL计划捕获（Plan capture）后，SQL语句日志将包含优化程序经过一段时间评估的语句的签名（the signature of statements ）。
一个SQL签名（SQL signature）是使用已归为不区分大小写和空格SQL语句的文本计算出的数字哈希值。当优化器解析一条语句时，它会创建签名。
在自动捕获期间，数据库将此签名与SQL语句日志（SQLLOG$）相匹配，以确定之前是否已观察到签名。如果还没有，则数据库将签名添加到日志中。如果签名已经在日志中，那么数据库将确认该语句是可重复的SQL语句。
示例23-1：记录SQL语句
此示例说明了数据库如何跟踪语句日志中的语句并自动为可重复语句创建基线。语句日志的初始查询未显示任何跟踪的SQL语句。
在查询hr.jobsfor之后，日志显示一条被跟踪的语句。
SQL> ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;
System altered.
SQL> SELECT * FROM SQLLOG$;
no rows selected
SQL> SELECT job_title FROM hr.jobs WHERE job_id = 'AD_PRES';
JOB_TITLE
-----------------------------------
President
SQL> SELECT * FROM SQLLOG$;
SIGNATURE BATCH#
---------- ----------
1.8096E+19 1
现在，该会话执行另一个jobs查询。该日志显示了两个跟踪的语句：
SQL> SELECT job_title FROM hr.jobs WHERE job_id='PR_REP';
JOB_TITLE
-----------------------------------
Public Relations Representative
SQL> SELECT * FROM SQLLOG$;
SIGNATURE BATCH#
---------- ----------
1.7971E+19 1
1.8096E+19 1
DBA_SQL_PLAN_BASELINES的查询显示，这两个语句都不存在基线，因为这两个语句都不是重复的（neither statement is repeatable）：
SQL> SELECT SQL_HANDLE, SQL_TEXT
2 FROM DBA_SQL_PLAN_BASELINES
3 WHERE SQL_TEXT LIKE 'SELECT job_title%';
no rows selected
会话job_id='PR_REP’第二次执行查询。因为此语句现在有重复的，并且由于启用了自动SQL计划捕获，所以数据库将为此语句创建计划基线（SQL Plan Baseline）。
SQL> SELECT job_title FROM hr.jobs WHERE job_id='PR_REP';
JOB_TITLE
-----------------------------------
Public Relations Representative
SQL> SELECT SQL_HANDLE, SQL_TEXT
2 FROM DBA_SQL_PLAN_BASELINES
3 WHERE SQL_TEXT LIKE 'SELECT job_title%';
SQL_HANDLE SQL_TEXT
-------------------- --------------------
SQL_f9676a330f972dd5 SELECT job_title FRO
M hr.jobs WHERE job_
id='PR_REP'
查询job_id='AD_PRES’仅执行一次，因此没有计划基准。
SQL计划历史（SQL plan history）
在SQL计划历史记录是一组捕获的SQL执行计划。历史记录包含SQL计划基准和不可接受的计划（The history contains both SQL plan baselines and unaccepted plans）。
在SQL计划管理中，数据库为现有SQL计划基线检测新的SQL执行计划，并将新计划记录在历史记录中，以便可以对其进行演化（验证）。演化由数据库自动启动，或由DBA手动启动。
在Oracle数据库12Ç中开始，SMB（SQL Management Base）存储了被SQL计划历史记录的所有SQL语句的执行计划。该DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE功能从SMB获取并显示计划。对于Oracle数据库12Ç之前创建的计划，函数必须编译SQL语句并生成计划，因为SMB并不保存。
## 已启用的计划（Enabled Plans）
一个启用的计划是计划，是符合优化器使用。
在将enabled参数设置为YES（默认）的情况下加载计划时，数据库会自动将生成的SQL计划基线标记为已启用，即使它们不被接受也是如此。您可以将已启用的计划手动更改为已禁用的计划，这意味着优化程序即使已被接受也无法使用该计划。
## 接受计划（Accepted Plans）
一个公认的计划是一个计划，是在SQL计划基线的SQL语句，因而可用于优化器使用。接受的计划包含一组提示，计划哈希值和其他与计划相关的信息。
语句的SQL计划历史记录包含所有接受和不接受的计划。优化器在计划基线中生成第一个接受的计划后，所有随后的未接受计划都将添加到计划历史记录中，以等待验证，但不在SQL计划基线中。
## 固定计划（Fixed Plans）
甲固定的计划是，被标记为优选的一个公认的计划，使得优化器考虑仅在基线固定计划。固定计划会影响优化器的计划选择过程。
假设在一个语句的SQL计划基线中存在三个计划。您希望优化程序仅对其中两个计划给予优惠。如下图所示，您将这两个计划标记为固定，以便优化程序仅使用这两个计划中的最佳计划，而忽略其他计划。
图23-6固定计划
如果将新计划添加到包含至少一个已启用的固定计划的基准，那么优化器将无法使用新计划，除非您手动将其声明为固定计划。
DBMS_SPM软件包
您可以使用该DBMS_SPM软件包管理SQL计划基准。
在命令行上，使用DBMS_SPM和DBMS_XPLANPL / SQL包执行大多数SQL计划管理任务。
下表描述了DBMS_SPM用于创建，删除和加载SQL计划基准的最相关的过程和功能。
表23-1 DBMS_SPM的过程和功能
| 包裹 | 程序或功能 | 描述 |
|---|---|---|
| DBMS_SPM | CONFIGURE | 此过程以名称/值格式更改SMB的配置选项。 |
| DBMS_SPM | CREATE_STGTAB_BASELINE | 此过程创建一个临时表，使您可以将SQL计划基准从一个数据库传输到另一个数据库。 |
| DBMS_SPM | DROP_SQL_PLAN_BASELINE | 此功能将部分或全部计划放到计划基准中。 |
| DBMS_SPM | LOAD_PLANS_FROM_CURSOR_CACHE | 此函数将共享SQL区域中的计划（也称为游标缓存）加载到SQL计划基准中。 |
| DBMS_SPM | LOAD_PLANS_FROM_SQLSET | 此功能将STS中的计划加载到SQL计划基准中。 |
| DBMS_SPM | PACK_STGTAB_BASELINE | 此函数打包SQL计划基准，这意味着它将基准计划从SMB复制到暂存表中。 |
| DBMS_SPM | UNPACK_STGTAB_BASELINE | 此函数解压缩SQL计划基线，这意味着它将SQL计划基线从登台表复制到SMB。 |
| DBMS_XPLAN | DISPLAY_SQL_PLAN_BASELINE | 此函数显示由SQL句柄标识的SQL语句的一个或多个执行计划。 |
配置SQL计划管理（Configuring SQL Plan Management）
# 配置SQL计划基准的捕获和使用（Configuring the Capture and Use of SQL Plan Baselines）
您可以使用初始化参数来控制SQL计划管理。
默认值如下：
-
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=false
对于不已经在计划历史记录存在任何重复的SQL语句，数据库并不会自动创建的声明初始SQL计划基准。（For any repeatable SQL statement that does not already exist in the plan history, the database does not automatically create an initial SQL plan baseline for the statement.） -
OPTIMIZER_USE_SQL_PLAN_BASELINES=true
对于具有现有SQL计划基线的任何SQL语句，数据库都会自动将新计划作为不可接受的计划添加到SQL计划基线。（For any SQL statement that has an existing SQL plan baseline, the database automatically adds new plans to the SQL plan baseline as nonaccepted plans.）
Note：
前述参数的设置彼此独立。例如，如果OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES是true，那么数据库中创建新的报表初步计划基线，即使OPTIMIZER_USE_SQL_PLAN_BASELINES是false。
以下各节说明如何从命令行更改默认参数设置。如果使用Cloud Control，请在“ SQL计划基准”子页面中设置这些参数。
为SQL计划管理启用自动初始计划捕获（Automatic Initial Plan Capture）
数据库自动为计划历史记录中尚未存在的任何SQL语句自动创建初始SQL计划基线，将OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES初始化参数设置为即可true。此参数不控制将新发现的计划自动添加到以前创建的SQL计划基准中。
WARM
启用自动基线捕获后，数据库将为每个可重复语句（包括所有递归SQL和监视SQL）创建SQL计划基线。因此，自动捕获可能会导致创建大量计划基准。
SQL> SHOW PARAMETER SQL_PLAN
NAME TYPE VALUE
------------------------------------ ----------- -----
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
SQL> ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;
# 管理SPM Evolve Advisor任务（Managing the SPM Evolve Advisor Task）
Evolve
vt. 发展；进化；使逐步形成；推断出
vi. 发展，进展；进化；逐步形成
SPM Evolve Advisor是一个SQL顾问，用于发展最近已添加到SQL计划基准中的计划。该顾问消除了手动进行计划的需要，从而简化了计划制定过程。
启用和禁用SPM Evolve Advisor任务
自动SPM Evolve Advisor任务没有单独的调度程序客户端。
一个客户端同时控制SQL Tuning Advisor和SPM Evolve Advisor。因此，同一任务启用或禁用这两者。
- “启用和禁用自动SQL调整任务”以了解如何启用和禁用自动
SPM Evolve Advisor
配置自动SPM Evolve Advisor任务
该DBMS_SPM软件包使您可以通过使用SET_EVOLVE_TASK_PARAMETER过程指定任务参数来配置自动计划演变。由于任务由拥有SYS，因此只能SYS设置任务参数。
该ACCEPT_PLANS调整任务参数指定是否自动接受建议的计划。如果ACCEPT_PLANS为true（默认），则SQL计划管理将自动接受任务建议的所有计划。设置false为时，任务会验证计划并在发现结果后生成报告，但不会演变计划。
假设条件（Assumptions）
本节中的教程假定以下内容：
- 您不希望数据库自动制定计划。
- 您希望任务在每次执行1200秒后超时。
设置自动演化任务参数：
-
使用适当的特权将SQL * Plus连接到数据库，然后（可选）查询当前任务设置。
例如，使用管理员权限将SQL * Plus连接到数据库，然后执行以下查询：COL PARAMETER_NAME FORMAT a25 COL VALUE FORMAT a10 SELECT PARAMETER_NAME, PARAMETER_VALUE AS "VALUE" FROM DBA_ADVISOR_PARAMETERS WHERE ( (TASK_NAME = 'SYS_AUTO_SPM_EVOLVE_TASK') AND ( (PARAMETER_NAME = 'ACCEPT_PLANS') OR (PARAMETER_NAME = 'TIME_LIMIT') ) );Sample output appears as follows:
PARAMETER_NAME VALUE ------------------------- ---------- ACCEPT_PLANS TRUE TIME_LIMIT 3600 -
Set parameters using PL/SQL code of the following form:
BEGIN DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( task_name => 'SYS_AUTO_SPM_EVOLVE_TASK' , parameter => parameter_name , value => value ); END; /For example, 以下PL / SQL块将时间限制设置为20分钟，并且还自动接受计划：
BEGIN DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( task_name => 'SYS_AUTO_SPM_EVOLVE_TASK' , parameter => 'LOCAL_TIME_LIMIT' , value => 1200 ); DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( task_name => 'SYS_AUTO_SPM_EVOLVE_TASK' , parameter => 'ACCEPT_PLANS' , value => 'true' ); END; /
- 《 Oracle数据库PL / SQL软件包和类型参考》以获取有关该
DBMS_SPM软件包的更多信息
to be continue…https://docs.oracle.com/database/121/TGSQL/tgsql_spm.htm#TGSQL653
扫一扫
496
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
