SPM-MANUAL 约定不等于承诺〃 2022-08-08 06:14 136阅读 0赞 手动生成SPB 1根据sql\_id、plan\_hash\_value导入目标sql至SPB中 2加入hint改写sql，导入改写后的执行计划至SPB 3删除步骤1中产生执行计划对于的SPB 设置试验环境 SYS@fyl>create table t2 as select * from dba_objects; Table created. SYS@fyl>create index idx_t2_id on t2(object_id); Index created. SYS@fyl>execute dbms_stats.gather_table_stats(ownname => 'sys',tabname => 't2' ,estimate_percent => 100 ,method_opt => 'for all columns size auto' ,cascade => true); PL/SQL procedure successfully completed. SYS@fyl>exec dbms_stats.set_index_stats(ownname=>'sys',indname=>'IDX_T2_ID',clstfct=>20000000,no_invalidate=>false); PL/SQL procedure successfully completed. SYS@fyl>select index_name,clustering_factor from dba_indexes where index_name='IDX_T2_ID'; INDEX_NAME CLUSTERING_FACTOR ------------------------------ ----------------- IDX_T1_ID 20000000 SYS@fyl>select object_id,object_name from t2 where object_id between 100 and 105; SYS@fyl>select * from table(dbms_xplan.display_cursor(null,null,'advanced')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------- SQL_ID 55bzx08aspfvg, child number 0 ------------------------------------- select object_id,object_name from t2 where object_id between 100 and 105 Plan hash value: 1513984157 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 256 (100)| | |* 1 | TABLE ACCESS FULL| T2 | 7 | 210 | 256 (1)| 00:00:04 | -------------------------------------------------------------------------- 很明显这是个错误的执行计划，没开启SPB自动捕捉情况下，没有数据 SYS@fyl>select SQL_HANDLE,PLAN_NAME,ORIGIN,ENABLED,ACCEPTED,SQL_TEXT from DBA_SQL_PLAN_BASELINES where sql_text like '%t2%'; no rows selected 1根据sql\_id、plan\_hash\_value导入目标sql至SPB中 使用目标SQL的执行计划（table full scan）生成SPB SYS@fyl>var temp number SYS@fyl>exec :temp :=dbms_spm.load_plans_from_cursor_cache( sql_id=>'55bzx08aspfvg',plan_hash_value=>'1513984157') PL/SQL procedure successfully completed. SYS@fyl>select SQL_HANDLE,PLAN_NAME,ORIGIN,ENABLED,ACCEPTED,SQL_TEXT from DBA_SQL_PLAN_BASELINES where sql_text like '%t2%'; SQL_HANDLE PLAN_NAME ORIGIN ENA ACC ------------------------------ ------------------------------ -------------- --- --- SQL_TEXT -------------------------------------------------------------------------------- SYS_SQL_259d611fd3d979fd SQL_PLAN_2b7b13z9xkygxb860bcf2 MANUAL-LOAD YES YES select object_id,object_name from t2 where object_id between 100 and 105 2加入hint改写sql，导入改写后的执行计划至SPB SYS@fyl>select /*+ index(t2 idx_t2_id) */ object_id,object_name from t2 where object_id between 100 and 105; SYS@fyl>select * from table(dbms_xplan.display_cursor(null,null,'advanced')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------- SQL_ID 7kns0j57f3fvt, child number 0 ------------------------------------- select /*+ index(t2 idx_t2_id) */ object_id,object_name from t2 where object_id between 100 and 105 Plan hash value: 921640168 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2073 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| T2 | 7 | 210 | 2073 (1)| 00:00:25 | |* 2 | INDEX RANGE SCAN | IDX_T2_ID | 7 | | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- SYS@fyl>exec :temp :=dbms_spm.load_plans_from_cursor_cache( sql_id=>'7kns0j57f3fvt',plan_hash_value=>'921640168',sql_handle=>'SYS_SQL_259d611fd3d979fd'); PL/SQL procedure successfully completed. SYS@fyl>select SQL_HANDLE,PLAN_NAME,ORIGIN,ENABLED,ACCEPTED,SQL_TEXT from DBA_SQL_PLAN_BASELINES where sql_text like '%t2%'; SQL_HANDLE PLAN_NAME ORIGIN ENA ACC ------------------------------ ------------------------------ -------------- --- --- SQL_TEXT -------------------------------------------------------------------------------- SYS_SQL_259d611fd3d979fd SQL_PLAN_2b7b13z9xkygx6abe946f MANUAL-LOAD YES YES select object_id,object_name from t2 where object_id between 100 and 105 SYS_SQL_259d611fd3d979fd SQL_PLAN_2b7b13z9xkygxb860bcf2 MANUAL-LOAD YES YES select object_id,object_name from t2 where object_id between 100 and 105 3删除步骤1中产生执行计划对于的SPB SYS@fyl>exec :temp :=dbms_spm.drop_sql_plan_baseline( sql_handle=>'SYS_SQL_259d611fd3d979fd',plan_name=>'SQL_PLAN_2b7b13z9xkygxb860bcf2'); PL/SQL procedure successfully completed. SYS@fyl>select SQL_HANDLE,PLAN_NAME,ORIGIN,ENABLED,ACCEPTED,SQL_TEXT from DBA_SQL_PLAN_BASELINES where sql_text like '%t2%'; SQL_HANDLE PLAN_NAME ORIGIN ENA ACC ------------------------------ ------------------------------ -------------- --- --- SQL_TEXT -------------------------------------------------------------------------------- SYS_SQL_259d611fd3d979fd SQL_PLAN_2b7b13z9xkygx6abe946f MANUAL-LOAD YES YES select object_id,object_name from t2 where object_id between 100 and 105 验证：再次执行原sql SYS@fyl>select index_name,clustering_factor from dba_indexes where index_name='IDX_T2_ID'; INDEX_NAME CLUSTERING_FACTOR ------------------------------ ----------------- IDX_T2_ID 20000000 SYS@fyl>select object_id,object_name from t2 where object_id between 100 and 105; SYS@fyl>select * from table(dbms_xplan.display_cursor(null,null,'advanced')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------ SQL_ID 55bzx08aspfvg, child number 1 ------------------------------------- select object_id,object_name from t2 where object_id between 100 and 105 Plan hash value: 921640168 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2073 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| T2 | 7 | 210 | 2073 (1)| 00:00:25 | |* 2 | INDEX RANGE SCAN | IDX_T2_ID | 7 | | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / T2@SEL$1 2 - SEL$1 / T2@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.1') DB_VERSION('11.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$1") INDEX_RS_ASC(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID")) END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID">=100 AND "OBJECT_ID"<=105) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22] 2 - "T2".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22] Note ----- PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------- - SQL plan baseline SQL_PLAN_2b7b13z9xkygx6abe946f used for this statement
还没有评论，来说两句吧...