明天给大家讲一下另外一种固定执行打算的办法：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