oracle 10g固定执行计划,Oracle ---- 固定执行计划之SqlProfile

今天给大家讲一下另外一种固定执行计划的方法：SqlProfile。

1.取得不加Hint的执行计划。SQL> conn test/test

接続されました。

SQL> explain plan for select * from tab2 where c2=2;

解析されました。

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"=2)

Column Projection Information (identified by operation id):

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

1 - "TAB2"."C1"[NUMBER,22], "C2"[NUMBER,22], "TAB2"."C3"[VARCHAR2,10]

37行が選択されました。

2.取得加Hint的执行计划。SQL> explain plan for select /*+ index(tab2 ind2_2) */ * from tab2 where c2=2;

解析されました。

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"=2)

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行が選択されました。

3.把加Hint的执行计划做成SqlProfile，固定给不加Hint的SQL文。SQL> conn / as sysdba

接続されました。

SQL> DECLARE

2 sql_stmt clob;

3 BEGIN

4 sql_stmt:=q'^select * from tab2 where c2=2^';

5 dbms_sqltune.import_sql_profile(

6 sql_text => sql_stmt,

7 name=>'test profile',

8 profile => sqlprof_attr(q'^INDEX_RS_ASC(@"SEL$1" "TAB2"@"SEL$1" ("TAB2"."C2"))^',

9 q'^OUTLINE_LEAF(@"SEL$1")^',

10 q'^ALL_ROWS^',

11 q'^DB_VERSION('11.2.0.4')^',

12 q'^OPTIMIZER_FEATURES_ENABLE('11.2.0.4')^',

13 q'^IGNORE_OPTIM_EMBEDDED_HINTS^'),

14 force_match=>true);

15 end;

16/

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

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

接続されました。

SQL> set autot traceonly exp

SQL> set lin 120 pages 999

SQL> select * from tab2 where c2=2;

実行計画

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

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"=2)

Note

-----

- SQL profile "test profile" used for this statement