oracle hint index rs,Oracle ---- 固定执行计划之SqlPatch

今天我们来做一个使用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.观察SQLPLANSQL> conn test/test

SQL> 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$1

Outline 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的SQLPLANSQL> 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$1

Outline 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 sysdba

SQL> 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 8

PL/SQLプロシージャが正常に完了しました。

5.看看结果。SQL> conn test/test

SQL> set lin 120 pages 999

SQL> set autot on explain

SQL> select * from tab2 where c2=1;

C1 C2 C3

---------- ---------- ------------------------------

601 1 a

... ...

4101 1 a

50行が選択されました。

実行計画

----------------------------------------------------------

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