SPM BASELINE 是11G出現的技術，它的出現是為了取代outline技術，outline在後面的版本可能會被廢棄。outline可以實現的功能baseline都可以實現，相對於outline，baseline還有著更多更優秀的功能，如最佳化器認為有更好的執行計劃時，會自動產生一個baseline，DBA可以透過進化baseline來驗證、接受新產生的baseline。baseline與outline一樣被設計用來提供穩定的執行計劃，以防止執行環境和統計資訊等變化導致的執行計劃變化，但是就像本章後面所描述的，有些情況也會導致baseline出現不穩固。此外，baseline也可以像outline,sql profile一樣在不修改SQL語句的情況下，修正查詢的執行計劃，本章也會對這一技術進行講解。

建立baseline

在講述一些更高階的baseline知識之前，我們先看看如何去手工建立一個baseline。本文提供了三種方式來建立baseline：手工方式建立、自動建立、透過SQL調優集建立。

1) 手工建立

下面的程式碼建立了一張表T，並且對錶T的統計資訊進行了分析。表上status欄位的值有資料傾斜。列status上分析了直方圖。

test@DLSP>CREATE TABLE test

2 AS

3 SELECT ROWNUM id,

4 DBMS_RANDOM.STRING('A', 12) name,

5 DECODE(MOD(ROWNUM, 500), 0, 'Inactive', 'Active') status

6 FROM all_objects a,dba_objects b

7 WHERE ROWNUM <= 50000;

Table created.

test@DLSP>begin

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

3 tabname => 'test',

4 no_invalidate => FALSE,

5 estimate_percent => 100,

6 force => true,

7 degree => 5,

8 method_opt => 'for columns status size 2',

9 cascade => true);

10 end;

11 /

PL/SQL procedure successfully completed.

test@DLSP>select status,count(*) from test group by status;

STATUS COUNT(*)

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

Active 49900

Inactive 100

我們看看如何透過手工方式建立baseline。

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: 1950795681

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

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

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

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

|* 2 | TABLE ACCESS FULL| TEST | 100 | 2500 | 51 (2)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - filter("STATUS"=:A)

根據上面的輸出，我們可以看到我們在對錶的status欄位做等值查詢時，查詢計劃走了全表掃描，假如這個sql是生產環境的核心SQL，為了防止執行環境發生變化導致對執行計劃產生影響，可以透過baseline技術來穩固這個SQL的執行計劃。我們透過函式DBMS_SPM.load_plans_from_cursor_cache來對SQL的執行計劃進行穩固：

test@DLSP>declare

2 l_pls number;

3 begin

4 l_pls := DBMS_SPM.load_plans_from_cursor_cache(sql_id => 'aa8mzbnrzu42f',

5 plan_hash_value => 1950795681

6 );

7 end;

8 /

PL/SQL procedure successfully completed.

test@DLSP>select sql_handle, plan_name, origin, accepted,fixed

2 from dba_sql_plan_baselines

3 where sql_text like '%count(name)%';

SQL_HANDLE PLAN_NAME ORIGIN ACCEPT FIXED

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

SQL_619bd8394153fd05 SQL_PLAN_636ys750p7z856b581ab9 MANUAL-LOAD YES NO

test@DLSP>select * from table(dbms_xplan.display_sql_plan_baseline('SQL_619bd8394153fd05',null));

PLAN_TABLE_OUTPUT

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

SQL handle: SQL_619bd8394153fd05

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

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

Plan name: SQL_PLAN_636ys750p7z856b581ab9 Plan id: 1800936121

Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD

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

Plan hash value: 1950795681

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

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

| 0 | SELECT STATEMENT | | 1 | 25 | 51 (2)| 00:00:01 |

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

|* 2 | TABLE ACCESS FULL| TEST | 25000 | 610K| 51 (2)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - filter("STATUS"=:A)

上面的程式碼透過DBMS_SPM包的load_plans_from_cursor_cache函式來建立baseline，使用此函式需要提供sql_id、plan_hash_value等引數。建立完baseline後，可以透過dba_sql_plan_baselines來檢視已經建立的baseline的相關資訊。sql_handle和plan_name標識出這個SQL特定的基線。 sql_handler非常重要，我們需要靠它來得到關於基線更多的資訊，從上面輸出欄位accepted為YES可以知道這個基線已經被啟用，在決定執行計劃時將會被查詢最佳化器所使用。我們還可以使用dbms_xplan.display_sql_plan_baseline來檢視與此基線結合在一起的執行計劃資訊。我們可以重新執行SQL，看是否已經使用到了新建立的baseline。

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 3 N Y N

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

An uncaught error happened in prepare_sql_statement : ORA-01403: no data found

NOTE: cannot fetch plan for SQL_ID: aa8mzbnrzu42f, CHILD_NUMBER: 0

Please verify value of SQL_ID and CHILD_NUMBER;

It could also be that the plan is no longer in cursor cache (check v$sql_plan)

8 rows selected.

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';

no rows selected

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: 1950795681

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

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

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

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

|* 2 | TABLE ACCESS FULL| TEST | 100 | 2500 | 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後，第一次SQL時，SQL之前的對應遊標會被清除出shared_pool，並且沒有產生新遊標，上面程式碼執行select * from table(dbms_xplan.display_cursor);提示找不到child_number為0的遊標，此提示並非偶爾，讀者可以在各個版本測試，截止到12C都是如此，原因我還不清楚。當然如果在執行SQL前先重新整理共享池，將不會導致這一情況，最佳化器會直接建立一個新的cursor，此cursor使用到了新建立的baseline。我們看到我們第二次執行SQL後，執行計劃輸出的Note部分：- SQL plan baseline SQL_PLAN_636ys750p7z856b581ab9 used for this statement，說明已經使用到了新建立的baseline。

未完，待續-----------

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

建立baseline

相關文章