************************************************************
第一部分:概念
************************************************************
SQL 计划管理是一种随Oracle Database 11g 引入的新功能,通过维护所谓的“SQL
计划基线(SQL plan baseline(11g))”来使系统能够自动控制SQL 计划演变。启用此功能后,
只要证明新生成的SQL 计划与SQL 计划基线相集成不会导致性能回归,就可以进行此项集成。
因此,在执行某个SQL 语句时,只能使用对应的SQL 计划基线中包括的计划。可以使用SQL
优化集自动加载或植入SQL 计划基线。
SQL 计划管理功能的主要优点是系统性能稳定,不会出现计划回归。此外,
该功能还可以节省DBA 的许多时间,这些时间通常花费在确定和分析SQL 性能回归以及
寻找可用的解决方案上.
(1) 即时捕获:
使用自动计划捕获,方法是:将初始化参数OPTIMIZER_CAPTURE_SQL_PLAN_BASELIN
ES 设置为TRUE。默认情况下,该参数设置为FALSE。将该参数设置为TRUE
将打开自动标识可重复SQL 语句,以及自动为此类语句创建计划历史记录的功能。
(2) 成批加载:
使用DBMS_SPM 程序包;该程序包支持手动管理SQL
计划基线。使用此程序包,可以将SQL 计划从游标高速缓存或现有的SQL
优化集(STS) 直接加载到SQL计划基线中。对于要从STS 加载到SQL 计划基线的SQL
语句,需要将其SQL计划存储在STS中。使用DBMS_SPM
可以将基线计划的状态从已接受更改为未接受(以及从未接受更改为已接受),还
可以从登台表导出基线计划,然后使用导出的基线计划将SQL
计划基线加载到其它数据库中。
NOTE:
SQL计划管理使用一种叫做SQL计划基准机制。计划基线是针对sql优化器
允许使用并接受的执行计划的一个集合。
在典型使用情况下,数据库只接受那些通过验证并执行良好的执行计划到计划基线中。
-----设置参数:启用sql_plan_baseline
show parameter optimizer_capture_sql_plan_baselines
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
alter system set optimizer_capture_sql_plan_baselines=true;
show parameter optimizer_capture_sql_plan_baselines
---1.准备测试环境
create table t2
(
sid number not null ,
sname varchar2(10)
)
tablespace test;
--循环导入数据
declare
maxrecords constant int:=20000;
i int :=1;
begin
for i in 1..maxrecords loop
insert into t2 values(i,'ocpyang');
end loop;
dbms_output.put_line(' 成功录入数据! ');
commit;
end;
/
exec dbms_stats.gather_table_stats('SCOTT','T2',cascade => true);
************************************************************
第二部分:为sql 调优集中的sql语句创建计划基线
************************************************************
创建基线的几种方式
1.自动捕获基线
2.从SQL调优集合中加载,通过使用包dbms_spm.load_plans_from_sqlset
3.从库缓存中加载,通过包dbms_spm.load_plans_from_cursor_cache函数为一条已经在游标缓存中的语句创建基线
----------------------------------*
方式1.自动捕获基线
----------------------------------*
--------案例演示
步骤1:简单查询
set autotrace on;
var v varchar2(5);
exec :v :=1000;
select * from t2 where sid<=:v;
set autotrace off;
执行计划
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 12000 | 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2 | 1000 | 12000 | 15 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SID"<=TO_NUMBER(:V))
步骤2:简单查询
set autotrace on;
var v varchar2(5);
exec :v :=1000;
select * from t2 where sid<=:v;
set autotrace off;
步骤3:查看SQL PLAN BASELINE
SELECT sql_handle, plan_name,enabled, accepted
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%select * from t2 where sid<=:v%';
SQL_HANDLE PLAN_NAME ENA ACC
------------------------------ ------------------------------ --- ---
SQL_60fea6835db2e913 SQL_PLAN_61zp6hdfv5u8mb860bcf2 YES YES
步骤4:新建索引
create index index_01 on t2(sid);
exec dbms_stats.gather_table_stats('SCOTT'
oracle SPM 执行计划管理
最新推荐文章于 2023-05-18 16:28:06 发布
本文详细介绍了Oracle数据库11g引入的SQL计划管理功能,包括SQL计划基线的概念、自动捕获和成批加载计划基线的方法,以及如何通过DBMS_SPM包进行管理。SQL计划管理确保系统性能稳定,防止计划回归,节省DBA的时间。文章通过实例演示了如何创建和管理SQL计划基线,包括自动捕获、从SQL调优集合加载和从库缓存加载,并展示了如何修改和迁移SQL计划基线。
摘要由CSDN通过智能技术生成