明天咱们来做一个应用SqlPatch固定执行打算的TEST.
1.做TEST用TABLE.
create table tab2(c1 number, c2 number, c3 varchar2(10));declare a number;begin a := 1; for i in 1 .. 50 loop for j in 1 .. 100 loop insert into tab2 values(a,j,'a'); commit; a := a+1; end loop; end loop;end;/create index ind2_2 on tab2(c2);2.察看SQLPLAN
SQL> conn test/testSQL> explain plan for select * from tab2 where c2=1;解析されました。SQL> select * from table (dbms_xplan.display(format=>'advanced'));PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------Plan hash value: 2156729920--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 50 | 450 | 5 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TAB2 | 50 | 450 | 5 (0)| 00:00:01 |--------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):------------------------------------------------------------- 1 - SEL$1 / TAB2@SEL$1Outline Data------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "TAB2"@"SEL$1") OUTLINE_LEAF(@"SEL$1") ALL_ROWS DB_VERSION('11.2.0.4') OPTIMIZER_FEATURES_ENABLE('11.2.0.4') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("C2"=1)Column Projection Information (identified by operation id):----------------------------------------------------------- 1 - "TAB2"."C1"[NUMBER,22], "C2"[NUMBER,22], "TAB2"."C3"[VARCHAR2,10]37行が選択されました。3.通过Hint做成应用Index Scan的SQLPLAN
SQL> explain plan for select /*+ index(tab2 ind2_2) */ * from tab2 where c2=1;解析されました。SQL> select * from table (dbms_xplan.display(format=>'advanced'));PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------Plan hash value: 3201770281--------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 50 | 450 | 11 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| TAB2 | 50 | 450 | 11 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IND2_2 | 50 | | 1 (0)| 00:00:01 |--------------------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):------------------------------------------------------------- 1 - SEL$1 / TAB2@SEL$1 2 - SEL$1 / TAB2@SEL$1Outline Data------------- /*+ BEGIN_OUTLINE_DATA INDEX_RS_ASC(@"SEL$1" "TAB2"@"SEL$1" ("TAB2"."C2")) OUTLINE_LEAF(@"SEL$1") ALL_ROWS DB_VERSION('11.2.0.4') OPTIMIZER_FEATURES_ENABLE('11.2.0.4') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("C2"=1)Column Projection Information (identified by operation id):----------------------------------------------------------- 1 - "TAB2"."C1"[NUMBER,22], "C2"[NUMBER,22], "TAB2"."C3"[VARCHAR2,10] 2 - "TAB2".ROWID[ROWID,10], "C2"[NUMBER,22]40行が選択されました。4.把Hint“INDEX_RS_ASC(@"SEL$1" "TAB2"@"SEL$1" ("TAB2"."C2"))”作成SqlPatch。
SQL> connect / as sysdbaSQL> begin dbms_sqldiag_internal.i_create_patch ( sql_text => 'select * from tab2 where c2=1', hint_text => 'INDEX_RS_ASC(@"SEL$1" "TAB2"@"SEL$1" ("TAB2"."C2"))', name => 'test patch' );end;/ 2 3 4 5 6 7 8PL/SQLプロシージャが失常に完了しました。5.看看后果。
SQL> conn test/testSQL> set lin 120 pages 999SQL> set autot on explainSQL> select * from tab2 where c2=1; C1 C2 C3---------- ---------- ------------------------------ 601 1 a ... ... 4101 1 a50行が選択されました。実行計画----------------------------------------------------------Plan hash value: 3201770281--------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 50 | 450 | 11 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| TAB2 | 50 | 450 | 11 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IND2_2 | 50 | | 1 (0)| 00:00:01 |--------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("C2"=1)Note----- - SQL patch "test patch" used for this statement