Baseline Internal

我們接著上面一節繼續來思考，一個SQL在dba_sql_plan_baselines中存在2個accepted為YES狀態的baseline。最佳化器如何決定使用哪一個？看到網上有些文章提到是依據dba_sql_plan_baselines. OPTIMIZER_COST來決定，哪個baseline對應的執行計劃的cost小，就使用哪個。但是很容易驗證說法是錯誤的。我們可以修改dba_sql_plan_baselines檢視的基表sqlobj$auxdata中的OPTIMIZER_COST欄位，來調整2個baseline的OPTIMIZER_COST的大小，但是實驗結果最終證明是無效的，並不是透過dba_sql_plan_baselines. OPTIMIZER_COST的大小來決定使用哪個baseline。實驗結果這裡不再貼出。那到底是如何決定呢？這個要根據系統的配置來定，我們接著上面的例子，但是先關閉ACS，但是繫結變數窺探的功能保持開啟。

test@DLSP>show parameter _optimizer_extended_cursor_sharing_rel

NAME TYPE VALUE

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

_optimizer_extended_cursor_sharing_rel string NONE

test@DLSP>show parameter bind

NAME TYPE VALUE

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

_optim_peek_user_binds boolean TRUE

test@DLSP>alter system flush shared_pool;

System altered.

提示：針對這個SQL當前在dba_sql_plan_baselines檢視中存在2個baseline。

test@DLSP>ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

test@DLSP>var a varchar2(100)

test@DLSP>exec :a :='Inactive'

PL/SQL procedure successfully completed.

test@DLSP>select count(name) from test where status= :a;

COUNT(NAME)

-----------

100

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

PLAN_TABLE_OUTPUT

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

SQL_ID aa8mzbnrzu42f, child number 0

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

select count(name) from test where status= :a

Plan hash value: 4130896540

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

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

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

| 1 | SORT AGGREGATE | | 1 | 25 | | |

| 2 | TABLE ACCESS BY INDEX ROWID| TEST | 100 | 2500 | 2 (0)| 00:00:01 |

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

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

Predicate Information (identified by operation id):

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

3 - access("STATUS"=:A)

Note

-----

- SQL plan baseline SQL_PLAN_636ys750p7z8519ccc485 used for this statement

當對status的繫結值傳入Inactive時，選用了我們進化後的baseline。由於列上存在直方圖資訊，我們看到了執行計劃的輸出基數部分非常的準確。這也要歸功於繫結變數窺探的作用。上面讀者可以再繼續試試多次執行這個SQL，執行計劃都會一直使用索引掃描。我們繼續試驗，看看重新重新整理共享池，對status的繫結之傳入Active會怎麼樣：

test@DLSP>alter system flush shared_pool;

System altered.

test@DLSP>exec :a :='Active';

PL/SQL procedure successfully completed.

test@DLSP>select count(name) from test where status= :a;

COUNT(NAME)

-----------

49900

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

PLAN_TABLE_OUTPUT

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

SQL_ID aa8mzbnrzu42f, child number 0

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

select count(name) from test where status= :a

Plan hash value: 1950795681

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

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

| 0 | SELECT STATEMENT | | | | 51 (100)| |

| 1 | SORT AGGREGATE | | 1 | 25 | | |

|* 2 | TABLE ACCESS FULL| TEST | 49900 | 1218K| 51 (2)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - filter("STATUS"=:A)

Note

-----

- SQL plan baseline SQL_PLAN_636ys750p7z856b581ab9 used for this statement

已經選用了我們最初建立的baseline，執行計劃已經走了全表掃描了。雖然baseline的作用是為了穩固執行計劃，但是就像我們看到的，由於同一個SQL存在2個可接受的baseline，因此執行計劃發生了不穩固的現象，繫結變數窺探的害處在這裡又重新體現出來了。其實繫結變數和ACS都會在baseline起作用前就會發生，但是繫結變數窺探和ACS發揮作用後產生的執行計劃必須從baseline中選用。因此就會發生前面產生的場景，繫結變數窺探導致已經使用baseline的SQL的執行計劃不穩定。這種情況可以透過ACS來解決：

--------開啟ACS

test@DLSP>alter system set "_optimizer_extended_cursor_sharing_rel"=simple ;

System altered.

test@DLSP>alter system flush shared_pool;

System altered.

test@DLSP>exec :a :='Active';

PL/SQL procedure successfully completed.

test@DLSP>select count(name) from test where status= :a;

COUNT(NAME)

-----------

49900

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

PLAN_TABLE_OUTPUT

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

SQL_ID aa8mzbnrzu42f, child number 0

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

select count(name) from test where status= :a

Plan hash value: 1950795681

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

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

| 0 | SELECT STATEMENT | | | | 51 (100)| |

| 1 | SORT AGGREGATE | | 1 | 25 | | |

|* 2 | TABLE ACCESS FULL| TEST | 49900 | 1218K| 51 (2)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - filter("STATUS"=:A)

Note

-----

- SQL plan baseline SQL_PLAN_636ys750p7z856b581ab9 used for this statement

23 rows selected.

test@DLSP> -- 檢查ACS狀態

test@DLSP>SELECT child_number, executions, buffer_gets, is_bind_sensitive,IS_SHAREABLE,

2 is_bind_aware

3 FROM v$sql

4 WHERE sql_id='aa8mzbnrzu42f';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS IS

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

0 1 452 Y Y N

-- 直方圖

SELECT hash_value, sql_id, child_number, bucket_id, COUNT

test@DLSP>test@DLSP> 2 FROM v$sql_cs_histogram

3 WHERE sql_id='aa8mzbnrzu42f'

4 ORDER BY sql_id, child_number;

HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT

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

805113934 aa8mzbnrzu42f 0 0 0

805113934 aa8mzbnrzu42f 0 2 0

805113934 aa8mzbnrzu42f 0 1 1

test@DLSP>exec :a :='Inactive';

PL/SQL procedure successfully completed.

test@DLSP>select count(name) from test where status= :a;

COUNT(NAME)

-----------

100

test@DLSP> -- 檢查ACS狀態

test@DLSP>SELECT child_number, executions, buffer_gets, is_bind_sensitive,IS_SHAREABLE,

2 is_bind_aware

3 FROM v$sql

4 WHERE sql_id='aa8mzbnrzu42f';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS IS

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

0 2 662 Y Y N

test@DLSP>-- 直方圖

test@DLSP>SELECT hash_value, sql_id, child_number, bucket_id, COUNT

2 FROM v$sql_cs_histogram

3 WHERE sql_id='aa8mzbnrzu42f'

4 ORDER BY sql_id, child_number;

HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT

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

805113934 aa8mzbnrzu42f 0 0 1

805113934 aa8mzbnrzu42f 0 2 0

805113934 aa8mzbnrzu42f 0 1 1

test@DLSP>select count(name) from test where status= :a;

COUNT(NAME)

-----------

100

test@DLSP> -- 檢查ACS狀態

test@DLSP>SELECT child_number, executions, buffer_gets, is_bind_sensitive,IS_SHAREABLE,

2 is_bind_aware

3 FROM v$sql

4 WHERE sql_id='aa8mzbnrzu42f';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS IS

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

0 2 662 Y N N

1 1 102 Y Y Y

test@DLSP>-- 直方圖

test@DLSP>SELECT hash_value, sql_id, child_number, bucket_id, COUNT

2 FROM v$sql_cs_histogram

3 WHERE sql_id='aa8mzbnrzu42f'

4 ORDER BY sql_id, child_number;

HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT

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

805113934 aa8mzbnrzu42f 0 1 1

805113934 aa8mzbnrzu42f 0 0 1

805113934 aa8mzbnrzu42f 0 2 0

805113934 aa8mzbnrzu42f 1 1 0

805113934 aa8mzbnrzu42f 1 0 1

805113934 aa8mzbnrzu42f 1 2 0

6 rows selected.

--------檢視child_number為1的執行計劃

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

PLAN_TABLE_OUTPUT

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

SQL_ID aa8mzbnrzu42f, child number 1

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

select count(name) from test where status= :a

Plan hash value: 4130896540

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

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

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

| 1 | SORT AGGREGATE | | 1 | 25 | | |

| 2 | TABLE ACCESS BY INDEX ROWID| TEST | 100 | 2500 | 2 (0)| 00:00:01 |

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

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

Predicate Information (identified by operation id):

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

3 - access("STATUS"=:A)

Note

-----

- SQL plan baseline SQL_PLAN_636ys750p7z8519ccc485 used for this statement

test@DLSP>exec :a :='Active';

PL/SQL procedure successfully completed.

test@DLSP>select count(name) from test where status= :a;

COUNT(NAME)

-----------

49900

test@DLSP> -- 檢查ACS狀態

test@DLSP>SELECT child_number, executions, buffer_gets, is_bind_sensitive,IS_SHAREABLE,

2 is_bind_aware

3 FROM v$sql

4 WHERE sql_id='aa8mzbnrzu42f';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS IS

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

0 2 662 Y N N

1 1 102 Y Y Y

2 1 210 Y Y Y

test@DLSP>-- 直方圖

test@DLSP>SELECT hash_value, sql_id, child_number, bucket_id, COUNT

2 FROM v$sql_cs_histogram

3 WHERE sql_id='aa8mzbnrzu42f'

4 ORDER BY sql_id, child_number;

HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT

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

805113934 aa8mzbnrzu42f 0 1 1

805113934 aa8mzbnrzu42f 0 0 1

805113934 aa8mzbnrzu42f 0 2 0

805113934 aa8mzbnrzu42f 1 0 1

805113934 aa8mzbnrzu42f 1 1 0

805113934 aa8mzbnrzu42f 1 2 0

805113934 aa8mzbnrzu42f 2 1 1

805113934 aa8mzbnrzu42f 2 0 0

805113934 aa8mzbnrzu42f 2 2 0

9 rows selected.

test@DLSP>select * from table(dbms_xplan.display_cursor('aa8mzbnrzu42f',2));

PLAN_TABLE_OUTPUT

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

SQL_ID aa8mzbnrzu42f, child number 2

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

select count(name) from test where status= :a

Plan hash value: 1950795681

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

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

| 0 | SELECT STATEMENT | | | | 51 (100)| |

| 1 | SORT AGGREGATE | | 1 | 25 | | |

|* 2 | TABLE ACCESS FULL| TEST | 49900 | 1218K| 51 (2)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - filter("STATUS"=:A)

Note

-----

- SQL plan baseline SQL_PLAN_636ys750p7z856b581ab9 used for this statement

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

Baseline Internal

相關文章