------sql plans(plan_hash_value)select  ss.plan_hash_value phv,          to_char(s.begin_interval_time, ‘DD-MON HH24:MI‘) snap_time,          ss.executions_delta execs,          ss.buffer_gets_delta/decode(ss.executions_delta,0,1,ss.executions_delta) bufferget_per_exec,          ss.disk_reads_delta/decode(ss.executions_delta,0,1,ss.executions_delta) diskread_per_exec,          (ss.cpu_time_delta/1000000)/decode(ss.executions_delta,0,1,ss.executions_delta) cpu_per_exec,          (ss.elapsed_time_delta/1000000)/decode(ss.executions_delta,0,1,ss.executions_delta) ela_per_exec  from    dba_hist_snapshot       s,          dba_hist_sqlstat        sswhere   ss.dbid = s.dbidand     ss.instance_number = s.instance_numberand     ss.snap_id = s.snap_idand     ss.sql_id = ‘ga0fsnswaqtxb‘and     ss.executions_delta > 0and     s.begin_interval_time >= sysdate - 4order by ss.plan_hash_value, s.snap_id;    select * from v$sql where sql_id=‘ga0fsnswaqtxb‘;--plan bind进行绑定  declare  m_clob clob;  begin  select sql_fulltext  into m_clob  from v$sql  where sql_id = ‘day4zwzuhxjnd‘  and child_number = 0;  dbms_output.put_line(m_clob);  dbms_output.put_line(dbms_spm.load_plans_from_cursor_cache(  sql_id          => ‘fzs1wktabj9ny‘,  plan_hash_value => 1956636844,  sql_text        => m_clob,  fixed           => ‘YES‘,   enabled         => ‘YES‘));  end; /

对于dbms_spm.load_plans_from_cursor_cache的说明既然是from cursor的话那么要保证在执行过程中sql还是在内存里的不能被刷出去declare m_clob clob; begin select sql_fulltext into m_clob from v$sql where sql_id = ‘day4zwzuhxjnd‘————–原始sql的 sql_id and child_number = 0;—————–为了让sql只返回一行，也可以rownum=1代替dbms_output.put_line(m_clob); dbms_output.put_line(dbms_spm.load_plans_from_cursor_cache( sql_id          => ‘fzs1wktabj9ny‘,————HINT SQL_ID plan_hash_value => 1956636844,——————-HINT PLAN_HASH_VALUE sql_text        => m_clob,————————-原始SQL文本fixed           => ‘YES‘, ———————禁止演化baseline enabled         => ‘YES‘));------生效end;

oracle SPM 执行计划管理 ************************************************************第一部分：概念************************************************************SQL 计划管理是一种随Oracle Database 11g 引入的新功能，通过维护所谓的“SQL计划基线(SQL plan baseline(11g))”来使系统能够自动控制SQL 计划演变。启用此功能后，只要证明新生成的SQL 计划与SQL 计划基线相集成不会导致性能回归，就可以进行此项集成。因此，在执行某个SQL 语句时，只能使用对应的SQL 计划基线中包括的计划。可以使用SQL优化集自动加载或植入SQL 计划基线。SQL 计划管理功能的主要优点是系统性能稳定，不会出现计划回归。此外，该功能还可以节省DBA 的许多时间，这些时间通常花费在确定和分析SQL 性能回归以及寻找可用的解决方案上.（1）  即时捕获：使用自动计划捕获，方法是：将初始化参数OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES 设置为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_baselineshow parameter optimizer_capture_sql_plan_baselinesNAME                     TYPE     VALUE------------------------------------ ----------- ------------------------------optimizer_capture_sql_plan_baselines boolean     FALSEalter 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_sqlset3.从库缓存中加载，通过包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 BASELINESELECT sql_handle, plan_name,enabled, accepted FROM   dba_sql_plan_baselinesWHERE  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 baselineSELECT sql_handle, plan_name,enabled, accepted FROM   dba_sql_plan_baselinesWHERE  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.新建STSBEGIN  DBMS_SQLTUNE.DROP_SQLSET(    sqlset_name => ‘OCPYANG_STS‘    );END;/---新建STSBEGIN  DBMS_SQLTUNE.CREATE_SQLSET(    sqlset_name => ‘OCPYANG_STS‘,     sqlset_owner => ‘SYS‘,    description  => ‘ocpyangtest‘);END;/步骤2.填充STSdeclarebaseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;beginopen baseline_ref_cur forselect 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 的值:  11647egin Snapshot Id specified: 11647输入 end_snap 的值:  11859nd   Snapshot Id specified: 11859--或指明sql_iddeclarebaseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;beginopen baseline_ref_cur forselect 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.LOAD_SQLSET(‘OCPYANG_STS‘, baseline_ref_cur);end;/步骤3：从SQLSET中加载即将DBMS_SPM作为输入为sql调优集中包含的每一个查询创建计划基线DECLAREmy_plans pls_integer;BEGINmy_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(    sqlset_name => ‘OCPYANG_STS‘    );END;//********语法DBMS_SPM.LOAD_PLANS_FROM_SQLSET (   sqlset_name      IN  VARCHAR2,   sqlset_owner     IN  VARCHAR2 := NULL,   basic_filter     IN  VARCHAR2 := NULL,   fixed            IN  VARCHAR2 := ‘NO‘,   enabled          IN  VARCHAR2 := ‘YES‘   commit_rows      IN  NUMBER   := 1000)RETURN PLS_INTEGER;**************/步骤4：查看相关计划基线select sql_handle,plan_name,sql_text from dba_sql_baselines;----------------------------------*方式3.从库缓存中加载----------------------------------*通过包dbms_spm.load_plans_from_cursor_cache函数为一条已经在游标缓存中的语句创建基线.----方法1：导入一个指定的sqlid--查看sql_id和hash_value值select sql_id,hash_value from v$sql where sql_textlike ‘%select count(1) from scott.tblorders where orderstatus>0 %‘;declareu int;beginu:=dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID=>‘57pk967xw5jqn‘,PLAN_HASH_VALUE=http://www.mamicode.com/>‘2002323537‘);DBMS_OUTPUT.put_line(‘导入完成!‘ );end;/ declareret  varchar2(100);beginret := dbms_spm.load_plans_from_cursor_cache(sql_id=>‘fwjgwwp18z7ad‘,--plan_hash_value=http://www.mamicode.com/>‘1601196873‘plan_hash_value=>NULL);end;/如果执行计划的哈希值没有指定或指定为NULL,则给定SQL语句的所有可用执行计划都会被加载.---方法2：同时导入多条declareu int;beginu:=dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID=>‘57pk967xw5jqn‘,PLAN_HASH_VALUE=http://www.mamicode.com/>‘2002323537‘);u:=dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID=>‘57pk967xw5jqk‘,PLAN_HASH_VALUE=http://www.mamicode.com/>‘2002323538‘);u:=dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID=>‘57pk967xw5jqm‘,PLAN_HASH_VALUE=http://www.mamicode.com/>‘2002323539‘);DBMS_OUTPUT.put_line(‘导入完成!‘ );end;/ ----方法3：为某个用户的游标创建基线declareret  varchar2(100);beginret := dbms_spm.load_plans_from_cursor_cache(attribute_name=>‘parsing_schema_name‘,attribute_value=>‘SCOTT‘);end;/----方法4：为library cache中每一条文本中包含字符串t1的SQL语句创建一个SQL计划基线：declareret  varchar2(100);beginret := dbms_spm.load_plans_from_cursor_cache(attribute_name=>‘sql_text‘,attribute_value=>‘%t1%‘);end;//*****语法DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (   sql_id            IN  VARCHAR2,   plan_hash_value   IN  NUMBER   := NULL,   sql_text          IN  CLOB,   fixed             IN  VARCHAR2 := ‘NO‘,   enabled           IN  VARCHAR2 := ‘YES‘) RETURN PLS_INTEGER;DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (   sql_id            IN  VARCHAR2,   plan_hash_value   IN  NUMBER   := NULL,   sql_handle        IN  VARCHAR2,   fixed             IN  VARCHAR2 := ‘NO‘,   enabled           IN  VARCHAR2 := ‘YES‘) RETURN PLS_INTEGER;DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (   sql_id            IN  VARCHAR2,   plan_hash_value   IN  NUMBER   := NULL,   fixed             IN  VARCHAR2 := ‘NO‘,   enabled           IN  VARCHAR2 := ‘YES‘) RETURN PLS_INTEGER;DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (   attribute_name   IN VARCHAR2,   attribute_value  IN VARCHAR2,   fixed            IN VARCHAR2 := ‘NO‘,   enabled          IN VARCHAR2 := ‘YES‘)  RETURN PLS_INTEGER;******/---查看是否存在执行计划SELECT sql_handle, plan_name,enabled, accepted FROM   dba_sql_plan_baselinesWHERE  sql_text LIKE ‘%select   sid,sname from t5 where sid<=:v%‘;SQL_HANDLE               PLAN_NAME              ENA ACC------------------------------ ------------------------------ --- ---SQL_e0c42f010eb9d50f           SQL_PLAN_f1j1g047bmp8gb73cade2 YES YESSQL_a9e4491f6b5d9737           SQL_PLAN_amt293xppv5tr14816fa9 YES YESSQL_93ffdec9273ee793           SQL_PLAN_97zyyt4mmxtwm95fcfc25 YES YES----查看某个查询是否使用了sql plan baselineselect sql_id,child_number,sql_plan_baseline,sql_textfrom v$sqlwhere sql_plan_baseline is not nulland sql_text like ‘%select count(*) from scott.tblorders%‘;-------案例演示:select count(1) from scott.tblorders where orderstatus>0;select sql_id,hash_value from v$sql where sql_textlike ‘%select count(1) from scott.tblorders where orderstatus>0 %‘;declareu int;beginu:=dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID=>‘g5f5cz344h5dz‘,PLAN_HASH_VALUE=http://www.mamicode.com/>‘3360167359‘);DBMS_OUTPUT.put_line(‘导入完成!‘ );end;/ ---查看是否存在执行计划SELECT sql_handle, plan_name,enabled, accepted FROM   dba_sql_plan_baselinesWHERE  sql_text LIKE ‘%select count(1) from scott.tblorders where orderstatus>0%‘;************************************************************第三部分：sql plan baseline修改************************************************************----1.修改新计划的ACCEPTED为YES/*********语法使用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE这个API来控制执行计划的演化。语法：DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE (  sql_handle IN VARCHAR2 := NULL, --> NULL 表示针对所有SQL  plan_name  IN VARCHAR2 := NULL,  time_limit IN INTEGER  := DBMS_SPM.AUTO_LIMIT,  verify     IN VARCHAR2 := ‘YES‘,  commit     IN VARCHAR2 := ‘YES‘ )RETURN CLOB;这里由两个标记控制：o Verify   + YES (只有性能更好的计划才会被演化)  + NO (演化所有的计划)o Commit  + YES (直接演化)  + NO (只生成报告)这里可以通过不同的排列组合，达到不同的效果：o 自动接收所有性能更好的执行计划 (Verify->YES, Commit->YES)o 自动接收所有新的执行计划 (Verify->NO, Commit->YES)o 比较性能，生成报告，人工确认是否演化 (Verify->NO, Commit->NO)*********/SET SERVEROUTPUT ONDECLARE l_plans_altered  clob;BEGINl_plans_altered := dbms_spm.evolve_sql_plan_baseline(sql_handle      => ‘SQL_60fea6835db2e913‘,plan_name       => ‘SQL_PLAN_61zp6hdfv5u8m8d82fa42‘,verify           =>‘NO‘,commit        =>‘YES‘);DBMS_OUTPUT.put_line(‘Plans Altered: ‘ || l_plans_altered);END;/----2.:修改已有的Baseline/*********语法DBMS_SPM.ALTER_SQL_PLAN_BASELINE (  sql_handle      IN VARCHAR2 := NULL,  plan_name       IN VARCHAR2 := NULL,  attribute_name  IN VARCHAR2,  attribute_value IN VARCHAR2 )RETURN PLS_INTEGER;************/SET SERVEROUTPUT ONDECLARE l_plans_altered  PLS_INTEGER;BEGINl_plans_altered := DBMS_SPM.alter_sql_plan_baseline(sql_handle      => ‘SQL_60fea6835db2e913‘,plan_name       => ‘SQL_PLAN_61zp6hdfv5u8mb860bcf2‘,attribute_name  => ‘ENABLED‘,attribute_value => ‘NO‘);DBMS_OUTPUT.put_line(‘Plans Altered: ‘ || l_plans_altered);END;/----3.删除已有的BaselineSET SERVEROUTPUT ONDECLARE l_plans_dropped  PLS_INTEGER;BEGIN l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (   sql_handle => ‘SQL_3a8461388a9bfa52‘,   plan_name  => NULL);     DBMS_OUTPUT.put_line(l_plans_dropped);END;/SELECT sql_handle, plan_name,enabled, accepted FROM   dba_sql_plan_baselinesWHERE  sql_text LIKE ‘%select   * from t2 where sid<=:v%‘;SQL_60fea6835db2e913           SQL_PLAN_61zp6hdfv5u8m8d82fa42 YES YESSQL_60fea6835db2e913           SQL_PLAN_61zp6hdfv5u8mb860bcf2 NO  YESset autotrace on;var v varchar2(5);  exec :v :=1000;  select   * from t2 where sid<=:v;set autotrace off;执行计划----------------------------------------------------------Plan hash value: 1194324917----------------------------------------------------------------------------------------| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------------------|   0 | SELECT STATEMENT        |           |  1000 | 12000 |     3     (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| T2       |  1000 | 12000 |     3     (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN        | INDEX_01 |   180 |       |     2     (0)| 00:00:01 |----------------------------------------------------------------------------------------************************************************************第四部分：sql plan baseline迁移************************************************************迁移步骤:1.使用dbms_spm包和create_stgtab_baseline创建一个过程表2.使用dbms_spm.pack_stgtab_baseline将计划基线填充到第一步中新建的过程表3.使用数据库链接或数据泵复制到目标数据库中4.使用DBMS_SPM.unpack_stgtab_baseline导入计划基线到目标数据库中---1.创建舞台表BEGIN  dbms_spm.create_stgtab_baseline(    table_name => ‘BASELINE_STG01‘,    table_owner => ‘SCOTT‘, --不能新建在SYS账户下    tablespace_name=>‘USERS‘); END;//****** dbms_spm.create_stgtab_baseline语法This procedure creates a staging table used for transporting SQL plan baselines from one system to another.SyntaxDBMS_SPM.CREATE_STGTAB_BASELINE (   table_name        IN VARCHAR2,   table_owner       IN VARCHAR2 := NULL,   tablespace_name   IN VARCHAR2 := NULL);The creation of staging table is the first step. To migrate SQL plan baselines from one system to another, the user/DBA has to perform a series of steps as follows:Create a staging table in the source systemSelect SQL plan baselines in the source system and pack them into the staging tableExport staging table into a flat file using Oracle EXP utility or Data PumpTransfer flat file to the target systemImport staging table from the flat file using Oracle IMP utility or Data PumpSelect SQL plan baselines from the staging table and unpack them into the target system************/----2.将sql plan baseline从数据字典复制到舞台表declarek int;begink:=dbms_spm.pack_stgtab_baseline(    TABLE_NAME=>‘BASELINE_STG01‘,    TABLE_OWNER=>‘SCOTT‘    );end;/ declarev_ret  number(100);beginv_ret := dbms_spm.pack_stgtab_baseline(table_name =>‘mystgtab‘,table_owner=>user,sql_handle=>‘SQL_e436abaac44f99d8‘,--plan_name=>‘SQL_PLAN_f8dpbpb24z6fs94ecae5c‘,);end;//**********语法:DBMS_SPM.PACK_STGTAB_BASELINE (   table_name       IN VARCHAR2,   table_owner      IN VARCHAR2 := NULL,   sql_handle       IN VARCHAR2 := NULL,   plan_name        IN VARCHAR2 := NULL,   sql_text         IN CLOB     := NULL,   creator          IN VARCHAR2 := NULL,   origin           IN VARCHAR2 := NULL,   enabled          IN VARCHAR2 := NULL,   accepted         IN VARCHAR2 := NULL,   fixed            IN VARCHAR2 := NULL,   module           IN VARCHAR2 := NULL,   action           IN VARCHAR2 := NULL)RETURN NUMBER;*****/----3.用expdp\impdp或exp,imp工具从测试库将表移到目标库----4.将sql plan baseline从舞台表复制到数据字典---4.1  将所有sql plan baseline从舞台表复制到数据字典SET SERVEROUTPUT ONDECLARE l_plans_unpacked  PLS_INTEGER;BEGIN l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(   table_name      =>‘BASELINE_STG01‘,   table_owner     => ‘SCOTT‘); DBMS_OUTPUT.put_line(‘Plans Unpacked: ‘ || l_plans_unpacked);END;/---4.2 将sql plan baseline中有关T1表的从舞台表复制到数据字典declarev_ret  varchar2(100);beginv_ret := dbms_spm.unpack_stgtab_baseline(table_name =>‘BASELINE_STG01‘,table_owner=>‘SCOTT‘,sql_text=>‘%FROM t1%‘);end;//*********语法:DBMS_SPM.UNPACK_STGTAB_BASELINE (   table_name       IN VARCHAR2,   table_owner      IN VARCHAR2 := NULL,   sql_handle       IN VARCHAR2 := NULL,   plan_name        IN VARCHAR2 := NULL,   sql_text         IN CLOB     := NULL,   creator          IN VARCHAR2 := NULL,   origin           IN VARCHAR2 := NULL,   enabled          IN VARCHAR2 := NULL,   accepted         IN VARCHAR2 := NULL,   fixed            IN VARCHAR2 := NULL,   module           IN VARCHAR2 := NULL,   action           IN VARCHAR2 := NULL)RETURN NUMBER;如果只指定table_name与table_owner,就是处理所有sql plan baseline。sql_handle与plan_name一起能精确识别一个sql plan baseline,plan_name为可选项。sql_text里面区分大小写/********SELECT sql_handle, plan_name,enabled, accepted FROM   dba_sql_plan_baselinesWHERE  sql_text LIKE ‘%select  sid,sname from t4 where sid<=:v%‘;SQL_HANDLE               PLAN_NAME              ENA ACC------------------------------ ------------------------------ --- ---SQL_4e6155ac1d5b5962           SQL_PLAN_4wsapphfpqqb214816fa9 YES YES---删除sql计划基线declarev_ret  varchar2(100);beginv_ret := dbms_spm.unpack_stgtab_baseline(sql_handle=>‘mystgtab‘,plan_name=>‘swew223‘);end;/这两个参数至少要指定一个。进行绑定  125.SQL> declare  126.  2  m_clob clob;  127.  3  begin  128.  4  select sql_fulltext  129.  5  into m_clob  130.  6  from v$sql  131.  7  where sql_id = ‘day4zwzuhxjnd‘  132.  8  and child_number = 0;  133.  9  dbms_output.put_line(m_clob);  134. 10  dbms_output.put_line(dbms_spm.load_plans_from_cursor_cache(  135. 11  sql_id          => ‘fzs1wktabj9ny‘,  136. 12  plan_hash_value =http://www.mamicode.com/> 1956636844,  >