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

; 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) 已选择19行。
SQL> select * from table(dbms_xplan.display_cursor('fm35jcmypb3qu','','')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID fm35jcmypb3qu, child number 0 ------------------------------------- select /* outlinetest3 */ /*+ index(dh_stat) */ * from dh_stat where id=711 Plan hash value: 2780970545 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| DH_STAT | 1 | 38 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IND_1 | 1 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"=711) 已选择20行。

SQL> DECLARE 2 k1 pls_integer; 3 begin 4 k1 := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE ( 5 sql_id=>'4vaj9fgjysy9c', 6 plan_hash_value=>1845196118 7 ); 8 end; 9 / PL/SQL 过程已成功完成。
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_13g6p1maja17934f41c8d select /* outlinetest2 */ /*+ full(dh_stat) */ * from d YES h_sta
刚生产sql plan baseline的时候，第一次查询，无法找到执行计划，直到第二次执行的时候，才能看到，如下 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('4vaj9fgjysy9c','','')); PLAN_TABLE_OUTPUT --------------------------------------------------------- SQL_ID: 4vaj9fgjysy9c cannot be found 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 ------------------------------------------------------- ------------- ---------- ------------ --------------- --------

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