OracleSQL执行计划基线总结（SQLPlanBaseline） - 数据库编程

select /* outlinetest3 */ /*+ index(dh_stat) */ * from fm35jcmypb3qu 4250242778 0 2780970545 12:27:41 dh_stat where id=711

SQL> select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711; ID NAME TYPE ---------- ------------------------------ --------------- 711 I_STREAMS_PROCESS_PARAMS1 INDEX SQL> select sql_text,sql_id,hash_value,child_number,plan_hash_value,to_char(LAST_ACTIVE_TIME,'hh24:mi:ss') time 2 from v$sql a where sql_text like '%outlinetest%' and sql_text not like '%v$sql%';
SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE TIME ------------------------------------------------------- ------------- ---------- ------------ --------------- -------- select /* outlinetest2 */ /*+ full(dh_stat) */ * from d 4vaj9fgjysy9c 3823925548 0 1845196118 12:30:54 h_stat where id=711 select /* outlinetest3 */ /*+ index(dh_stat) */ * from fm35jcmypb3qu 4250242778 0 2780970545 12:27:41 dh_stat where id=711

SQL> select * from table(dbms_xplan.display_cursor('4vaj9fgjysy9c','','')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 4vaj9fgjysy9c, child number 0 ------------------------------------- select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711
Plan hash value: 1845196118 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 124 (100)| | |* 1 | TABLE ACCESS FULL| DH_STAT | 1 | 38 | 124 (1)| 00:00:02 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------
1 - filter("ID"=711) Note ----- - SQL plan baseline SQL_PLAN_13g6p1maja17934f41c8d used for this statement 已选择23行。
将符合我们预期的执行计划的加载到第一次生成的sql baseline中！ SQL> DECLARE 2 k1 pls_integer; 3 begin 4 k1 := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE ( 5 sql_id=>'fm35jcmypb3qu', 6 plan_hash_value=>2780970545,sql_handle=>'SYS_SQL_11bcd50cd51504e9' 7 ); 8 end; 9 /
PL/SQL 过程已成功完成。

可以看到，SYS_SQL_11bcd50cd51504e9下目前有两个plan_name SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME SQL_TEXT ACC ------------------------------ ------------------------------ ------------------------------------------------------- --- SYS_SQL_11bcd50cd51504e9 SQL_PLAN_13g6p1maja1790cce5f0e select /* outlinetest2 */ /*+ full(dh_stat) */ * from d YES h_sta
SYS_SQL_11bcd50cd51504e9 SQL_PLAN_13g6p1maja17934f41c8d select /* outlinetest2 */ /*+ full(dh_stat) */ * from d YES h_sta 删除第一个plan_name，即将我们不需要的执行计划版本去除掉！ SQL> DECLARE 2 k1 pls_integer; 3 begin 4 k1 := DBMS_SPM.drop_sql_plan_baseline ( sql_handle=>'SYS_SQL_11bcd50cd51504e9',plan_name=>'SQL_PLAN_13g6p1maja17934f41c8d'); 5 end; 6 /
PL/SQL 过程已成功完成。

通过下面的一部分测试，我们可以看到，新的SQL计划基线已经正常生效，及时语句中包含full提示，执行计划也走索引定位数据 SQL> select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711; ID NAME TYPE ---------- ------------------------------ --------------- 711 I_STREAMS_PROCESS_PARAMS1 INDEX
SQL> select * from table(dbms_xplan.display_cursor('4vaj9

OracleSQL执行计划基线总结（SQLPlanBaseline）(五)