明天给大家讲一下另外一种固定执行打算的办法：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$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"=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$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"=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 expSQL> set lin 120 pages 999SQL> 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