************************************************************ 第一部分：概念 ************************************************************ 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','T2',cascade =>true); 步骤5：简单查询 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)) Note ----- - SQL plan baseline "SQL_PLAN_61zp6hdfv5u8mb860bcf2" used for this statement 步骤6：查看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_61zp6hdfv5u8m8d82fa42 YES NO --未启用 SQL_60fea6835db2e913 SQL_PLAN_61zp6hdfv5u8mb860bcf2 YES YES ----------------------------------* 方式2.从SQL调优集合中加载 ----------------------------------* 通过使用包dbms_spm.load_plans_from_sqlset 步骤1.新建STS BEGIN DBMS_SQLTUNE.DROP_SQLSET( sqlset_name => 'OCPYANG_STS' ); END; / ---新建STS BEGIN DBMS_SQLTUNE.CREATE_SQLSET( sqlset_name => 'OCPYANG_STS', sqlset_owner => 'SYS', description => 'ocpyangtest'); END; / 步骤2.填充STS declare baseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR; begin open baseline_ref_cur for select VALUE(p) from table( DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(&begin_snap_id, &end_snap_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p; DBMS_SQLTUNE.LOAD_SQLSET('OCPYANG_STS', baseline_ref_cur); end; / 输入 begin_snap 的值: 11647 egin Snapshot Id specified: 11647 输入 end_snap 的值: 11859 nd Snapshot Id specified: 11859 --或指明sql_id declare baseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR; begin open baseline_ref_cur for select VALUE(p) from table( DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(&begin_snap_id, &end_snap_id,'sql_id='||CHR(39)||'&sql_id'||CHR(39)||'',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p; DBMS_SQLTUNE.LOA