绑定执行计划
create table sqltest as select * from dba_objects; insert into sqltest select * from sqltest; / / SQL> select count(*) from sqltest; COUNT(*) ---------- 1514320 SQL> select * from sqltest s where s.object_id =202; SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID cabhsaac0b7z5, child number 0 ------------------------------------- select * from sqltest s where s.object_id =202 Plan hash value: 2099455689 ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 16 |00:00:00.12 | 21480 | 21474 | |* 1 | TABLE ACCESS FULL| SQLTEST | 1 | 238 | 16 |00:00:00.12 | 21480 | 21474 | PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("S"."OBJECT_ID"=202) Note ----- - dynamic sampling used for this statement (level=2) 22 rows selected.
将Plan hash value: 2099455689 固定到 cabhsaac0b7z5 上。
SQL> declare 2 v_hint sys.sqlprof_attr; 3 v_sql_text clob; 4 p_bad_sqlid varchar2(32) default '&bad_sqlid'; 5 begin 6 select sql_text 7 into v_sql_text 8 from dba_hist_sqltext 9 where sql_id = p_bad_sqlid 10 and rownum = 1; 11 select extractvalue(value(d), '/hint') as outline_hints bulk collect 12 into v_hint 13 from xmltable('/*/outline_data/hint' passing 14 (select xmltype(other_xml) as xmlval 15 from dba_hist_sql_plan 16 where sql_id = '&bad_sqlid' 17 and plan_hash_value = '&good_hash' 18 and other_xml is not null)) d; 19 dbms_sqltune.import_sql_profile(v_sql_text, 20 v_hint, 21 'sql_profile_&bas_sqlid', 22 force_match => true, 23 replace => true); 24 end; 25 26 / Enter value for bad_sqlid: cabhsaac0b7z5 old 4: p_bad_sqlid varchar2(32) default '&bad_sqlid'; new 4: p_bad_sqlid varchar2(32) default 'cabhsaac0b7z5'; Enter value for bad_sqlid: cabhsaac0b7z5 old 16: where sql_id = '&bad_sqlid' new 16: where sql_id = 'cabhsaac0b7z5' Enter value for good_hash: 2099455689 old 17: and plan_hash_value = '&good_hash' new 17: and plan_hash_value = '2099455689' Enter value for bas_sqlid: 2099455689 old 21: 'sql_profile_&bas_sqlid', new 21: 'sql_profile_2099455689', PL/SQL procedure successfully completed. Elapsed: 00:00:00.17 SQL> create index inx_object_id on sqltest(object_id); ---创建索引 Index created. Elapsed: 00:00:01.60
查看执行计划是否会使用索引
SQL> select * from sqltest s where s.object_id =202; SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID cabhsaac0b7z5, child number 0 ------------------------------------- select * from sqltest s where s.object_id =202 Plan hash value: 2099455689 ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 16 |00:00:00.11 | 21480 | 21474 | |* 1 | TABLE ACCESS FULL| SQLTEST | 1 | 17696 | 16 |00:00:00.11 | 21480 | 21474 | ------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("S"."OBJECT_ID"=202) Note ----- - SQL profile sql_profile_2099455689 used for this statement 22 rows selected.
可以看到创建了索引，但执行计划依然全表扫描。
删除绑定的执行计划，再来看执行计划。
SQL> begin 2 DBMS_SQLTUNE.DROP_SQL_PROFILE(name =>'sql_profile_2099455689'); 3 end; 4 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 SQL> select * from sqltest s where s.object_id =202; SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID cabhsaac0b7z5, child number 1 ------------------------------------- select * from sqltest s where s.object_id =202 Plan hash value: 2424517891 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 18 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| SQLTEST | 16 | 3312 | 18 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | INX_OBJECT_ID | 16 | | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / S@SEL$1 2 - SEL$1 / S@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.4') DB_VERSION('11.2.0.4') ALL_ROWS OUTLINE_LEAF(@"SEL$1") INDEX_RS_ASC(@"SEL$1" "S"@"SEL$1" ("SQLTEST"."OBJECT_ID")) END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("S"."OBJECT_ID"=202) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "S"."OWNER"[VARCHAR2,30], "S"."OBJECT_NAME"[VARCHAR2,128], "S"."SUBOBJECT_NAME"[VARCHAR2,30], "S"."OBJECT_ID"[NUMBER,22], "S"."DATA_OBJECT_ID"[NUMBER,22], "S"."OBJECT_TYPE"[VARCHAR2,19], "S"."CREATED"[DATE,7], "S"."LAST_DDL_TIME"[DATE,7], "S"."TIMESTAMP"[VARCHAR2,19], "S"."STATUS"[VARCHAR2,7], "S"."TEMPORARY"[VARCHAR2,1], "S"."GENERATED"[VARCHAR2,1], "S"."SECONDARY"[VARCHAR2,1], "S"."NAMESPACE"[NUMBER,22], "S"."EDITION_NAME"[VARCHAR2,30] 2 - "S".ROWID[ROWID,10], "S"."OBJECT_ID"[NUMBER,22] Note ----- - dynamic sampling used for this statement (level=2) - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level 58 rows selected.
执行计划立刻变成了使用索引。可以在dba_sql_profiles中查看绑定的执行计划。
还可直接用OUTLINE_DATA进行绑定，参考 sql profile 使用
SPM绑定执行计划 （11g以后推荐使用此方法）
--绑定执行计划 declare m_clob clob; begin select sql_fulltext into m_clob from v$sql where sql_id = '9r2gykqt5j1ss' --需要绑定的SQL ID and child_number = 0; --SQL ID对应的子游标 dbms_output.put_line(m_clob); dbms_output.put_line(dbms_spm.load_plans_from_cursor_cache(sql_id => 'gn32n5tu70y0m', --好的执行计划SQL ID plan_hash_value => 1601196873, --好的执行计划 Hash value sql_text => m_clob, fixed => 'YES', enabled => 'YES')); end; / --查询是否绑定 select sql_handle, plan_name, accepted, fixed,optimizer_cost from dba_sql_plan_baselines ; --删除SPM绑定的执行计划 declare xx PLS_INTEGER; BEGIN xx :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_916244ba197a1647',plan_name=>null); END; / --检查执行计划是否生效 exec sql select * from table(dbms_xplan.display_cursor(null,null,'advanced -PROJECTION -bytes iostats,last')); - SQL plan baseline SQL_PLAN_92sk4r8crn5k7f0218608 used for this statement --查看基线具体内容 select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_c7423114026678c5',plan_name=>'SQL_PLAN_cfhjj2h16cy6544ad9a20'));
