修正執行計劃

baseline不僅僅可以用來穩固執行計劃，還可以像SQL PROFILE、SQL PATCH一樣用來修正執行計劃，本節會介紹如何透過baseline來修正執行計劃。首先構建測試需要的表和索引。

test@DLSP>create table t as select * from dba_objects;

Table created.

test@DLSP>create index t_oi_ind on t(object_id);

Index created.

test@DLSP>begin

2 dbms_stats.gather_table_stats(ownname =>'test',

3 tabname => 't',

4 no_invalidate => FALSE,

5 estimate_percent => 100,

6 force => true,

7 degree => 5,

8 method_opt => 'for all columns size 1',

9 cascade => true);

10 end;

11 /

PL/SQL procedure successfully completed.

上面的程式碼建立了測試表t，欄位object_id存在索引，分析表的統計資訊，不收集直方圖。

test@DLSP>select /*+ full(t) */count(object_name) from t where object_id=2;

COUNT(OBJECT_NAME)

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

1

test@DLSP>select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

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

SQL_ID bb0mwx9rcbptu, child number 0

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

select /*+ full(t) */count(object_name) from t where object_id=2

Plan hash value: 2966233522

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

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

| 0 | SELECT STATEMENT | | | | 49 (100)| |

| 1 | SORT AGGREGATE | | 1 | 24 | | |

|* 2 | TABLE ACCESS FULL| T | 1 | 24 | 49 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - filter("OBJECT_ID"=2)

假如我們想修正這個SQL的執行計劃，讓查詢走上索引掃描。我們可以先構造一個SQL，讓構造的SQL使用到索引掃描：

test@DLSP>select count(object_name) from t where object_id=2;

COUNT(OBJECT_NAME)

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

1
test@DLSP>select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

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

SQL_ID 1k6p0yds2rjgp, child number 0

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

select count(object_name) from t where object_id=2

Plan hash value: 3312862475

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

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

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | SORT AGGREGATE | | 1 | 24 | | |

| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 24 | 2 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | T_OI_IND | 1 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

3 - access("OBJECT_ID"=2)

上面我們已經構造了一個SQL，執行計劃走的是索引掃描。我們現在可以交換2個SQL的執行計劃了。

test@DLSP>declare

2 m_clob clob;

3 begin

4 select sql_fulltext

5 into m_clob

6 from v$sql

7 where sql_id = 'bb0mwx9rcbptu' --------------原始sql的 sql_id

8 and child_number = 0; -----------------為了讓sql只返回一行，也可以rownum=1代替

9 dbms_output.put_line(m_clob);

10 dbms_output.put_line(dbms_spm.load_plans_from_cursor_cache(

sql_id => '1k6p0yds2rjgp', ------------構造SQL的 SQL_ID

11 plan_hash_value => 3312862475, -------------------構造SQL的 PLAN_HASH_VALUE

12 sql_text => m_clob,

13 fixed => 'YES', ---------------------禁止演化baseline

14 enabled => 'YES'));

15

16 end;

17

18 /

透過SPM包的load_plans_from_cursor_cache函式我們索引掃描的執行計劃轉嫁到了我們原始的SQL上。我們看看再次查詢有沒有使用到索引掃描：

test@DLSP>select /*+ full(t) */count(object_name) from t where object_id=2;

COUNT(OBJECT_NAME)

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

1

test@DLSP>select * from table(dbms_xplan.display_cursor(null,null));

PLAN_TABLE_OUTPUT

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

SQL_ID bb0mwx9rcbptu, child number 2

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

select /*+ full(t) */count(object_name) from t where object_id=2

Plan hash value: 3312862475

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

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

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | SORT AGGREGATE | | 1 | 24 | | |

| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 24 | 2 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | T_OI_IND | 1 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

3 - access("OBJECT_ID"=2)

Note

-----

- SQL plan baseline SQL_PLAN_3uq7qskvgtv1s4f379100 used for this statement

控制執行計劃之-SPM BASELINE（五）

修正執行計劃

相關文章