一、概述
SQL计划管理(SPM)是一种预防机制，它使优化器能够自动管理执行计划，确保数据库仅使用已知或经过验证的计划。可防止因计划更改而导致的性能下降
SPM有三个主要组件:
1、计划捕获
该组件存储有关一组 SQL 语句的计划的相关信息。
2、计划选择
该组件是优化器根据存储的计划历史检测计划更改，并使用SQL计划基线来选择适当的计划以避免潜在的性能回归。
3、计划演变
该组件是手动或自动将新计划添加到现有 SQL 计划基线的过程。在典型的用例中，只有在验证计划执行良好之后，数据库才会将计划接受到计划基线中。
SPM作用
- 1、SQL计划基线显着减少升级导致的潜在性能回归
- 2、持续的系统和数据更改可能会影响某些 SQL 语句的计划，从而可能导致性能下降。
- 3、新应用程序模块的部署将新的 SQL 语句引入到数据库中
二、sqlprofile和基线的区别
当调用 SQL Tuning Advisor时，数据库会创建SQL配置文件，通常只在SQL语句显示高负载症状后才这样做。SQL配置文件的主要用途是提供对导致次优计划的优化器错误的持续解决方案。因为SQL profile机制是被动的，当数据库发生剧烈变化时，它不能保证稳定的性能。
SQL计划基线是一组可接受的计划。每个计划都是使用一组完全指定特定计划的大纲提示来实施的。SQL 配置文件也是使用提示来实现的，但是这些提示并不指定任何特定的计划。相反，这些提示会纠正优化器估计中导致次优计划的错误计算。例如，提示可以更正表的基数估计。因为配置文件不会将优化器限制为任何一个计划，所以 SQL 配置文件比 SQL 计划基线更灵活。
例如，初始化参数和优化器统计信息的变化使优化器能够选择更好的计划。
Oracle 建议使用SQL Tuning Advisor。通过这种方式，遵循顾问为SQL配置文件和计划基线提出的建议，而不是试图确定哪种机制最适合每个 SQL 语句。
三、参数
-
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=false
对于计划历史中尚不存在的任何可重复 SQL 语句，数据库不会自动为该语句创建初始 SQL 计划基线。如果OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true，那么可以使用DBMS_SPM.CONFIGURE过程来配置过滤器，以确定哪些语句符合计划捕获的条件。默认情况下，没有配置过滤器，这意味着所有可重复的语句都符合计划捕获的条件。 -
OPTIMIZER_USE_SQL_PLAN_BASELINES=true
对于任何具有现有 SQL 计划基线的 SQL 语句，数据库会自动将新计划作为未接受的计划添加到 SQL 计划基线中。
四、存储位置
SQL> select occupant_name,occupant_desc,schema_name from v$sysaux_occupants where occupant_name='SQL_MANAGEMENT_BASE';
OCCUPANT_NAME OCCUPANT_DESC SCHEMA_NAME
------------------------------- ------------------------------------- ----------------------
SQL_MANAGEMENT_BASE SQL Management Base Schema SYS
五、计划捕获
SQL> alter system set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;
System altered.
SQL> select owner,object_name,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE from t where object_id=100;
OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- --------------- --------------- ---------- -------------- -----------------------
SYS SEQ$ 100 100 TABLE
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like '%select owner,object_name,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE from t where object_id=100%';
no rows selected
SQL> select sql_id,sql_text,plan_hash_value,child_number,sql_plan_baseline,executions from v$sql where sql_text like '%select owner,object_name,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE from t where object_id=100%';
SQL_ID SQL_TEXT PLAN_HASH_VALUE CHILD_NUMBER SQL_PLAN_BASELINE EXECUTIONS
------------- ------------------------------------------------------------------------------------------------------------------------ --------------- ------------ -------------------- ----------
d0xss9dwf9k7p select owner,object_name,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE from t where object_id=100 1601196873 0 1
此时还没有捕获基线，再执行一次sql
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like '%select owner,object_name,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE from t where object_id=100%';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT
------------------------------ ------------------------------ ----------------------------- --- --- ------------------------------------------------------------------------------------------------------------------------
SQL_8a78946abe4dd591 SQL_PLAN_8ny4ndaz4vpcj94ecae5c AUTO-CAPTURE YES YES select owner,object_name,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE fr
SQL> select sql_id,sql_text,plan_hash_value,child_number,sql_plan_baseline,executions from v$sql where sql_text like '%select owner,object_name,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE from t where object_id=100%';
SQL_ID SQL_TEXT PLAN_HASH_VALUE CHILD_NUMBER SQL_PLAN_BASELINE EXECUTIONS
------------- ------------------------------------------------------------------------------------------------------------------------ --------------- ------------ -------------------- ----------
d0xss9dwf9k7p select owner,object_name,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE from t where object_id=100 1601196873 0 1
说明第一次不会捕获，第二次才会捕获并且生成baseline。
SQL> set autotrace traceonly
SQL> select owner,object_name,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE from t where object_id=100;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 237 | 385 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 237 | 385 (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=100)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- SQL plan baseline "SQL_PLAN_8ny4ndaz4vpcj94ecae5c" used for this statement
Statistics
----------------------------------------------------------
71 recursive calls
25 db block gets
1660 consistent gets
2 physical reads
7056 redo size
975 bytes sent via SQL*Net to client
465 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed
创建索引后
SQL> create index idx_t_object_id on t(object_id);
Index created.
SQL> exec dbms_stats.gather_TABLE_STATS('SYS','T');
PL/SQL procedure successfully completed.
SQL> select owner,object_name,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE from t where object_id=100;
OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
---------- --------------- --------------- ---------- -------------- -----------------------
SYS SEQ$ 100 100 TABLE
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like '%select owner,object_name,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE from t where object_id=100%';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT
------------------------------ ------------------------------ ----------------------------- --- --- ------------------------------------------------------------------------------------------------------------------------
SQL_8a78946abe4dd591 SQL_PLAN_8ny4ndaz4vpcj773b3377 AUTO-CAPTURE YES NO select owner,object_name,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE fr
SQL_8a78946abe4dd591 SQL_PLAN_8ny4ndaz4vpcj94ecae5c AUTO-CAPTURE YES YES select owner,object_name,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE fr
SQL> select sql_id,sql_text,plan_hash_value,child_number,sql_plan_baseline,executions from v$sql where sql_text like '%select owner,object_name,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE from t where object_id=100%';
SQL_ID SQL_TEXT PLAN_HASH_VALUE CHILD_NUMBER SQL_PLAN_BASELINE EXECUTIONS
------------- ------------------------------------------------------------------------------------------------------------------------ --------------- ------------ -------------------- ----------
d0xss9dwf9k7p select owner,object_name,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE from t where object_id=100 1601196873 1 SQL_PLAN_8ny4ndaz4vp 1
cj94ecae5c
d0xss9dwf9k7p select owner,object_name,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE from t where object_id=100 1601196873 2 SQL_PLAN_8ny4ndaz4vp 5
cj94ecae5c
新的执行计划被捕获，但是没有被accept，所以依然使用旧的执行计划
SQL> select owner,object_name,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE from t where object_id=100;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 59 | 385 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 59 | 385 (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=100)
Note
-----
- SQL plan baseline "SQL_PLAN_8ny4ndaz4vpcj94ecae5c" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1415 consistent gets
0 physical reads
0 redo size
975 bytes sent via SQL*Net to client
465 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
最后修改时间：2022-11-19 18:09:26
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
