sql plan baselines（一）

sql plan baselines

在一個成熟的生產系統上，我們總是希望執行越穩定越好，執行計劃越確定越好。但由於統計資訊的變化、優化引數的修改、系統設定的修改、profile的建立等，都有可能造成執行計劃的變化，從而效能上會有變化。這種變化不一定都是差的方向，比如優化器版本的升級，一般會使執行計劃更加優化，但作為運維DBA，要確保萬無一失，可能還是會相信自己。

為了應對可能的執行計劃變化，oracle提供了baseline，我們翻譯為基線，用於保證執行計劃的穩定。

以下用實驗來說明：

sql baseline有兩種生成方式：

（1）需要通過AWR報告或者sql tuning set（STS）生成，因此，首先需要檢視當前系統中可用的STS：

[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Sun Dec 23 00:10:50 2012

SQL> conn scott/scott

Connected.

SQL> select * from dba_sqlset;

ID NAME OWNER DESCRIPTI CREATED LAST_MODI STATEMENT_COUNT

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

2 my_sql_tuningset SCOTT i/o intensive workload 22-DEC-12 22-DEC-12 0

SQL> declare

2 my_plans pls_integer;

3 begin

4 my_plans:=dbms_spm.load_plans_from_sqlset(sqlset_name=>'my_sql_tuningset');

5 end;

6 /

PL/SQL procedure successfully completed.

如果是從AWR報告中提取，則需要先用AWR報告生成STS，再執行以上步驟。

（2）從shared_pool_area中獲取sql

比如我們剛剛執行了select * from dept where deptno=10，想要將此語句歸入baseline中，操作如下：

SQL> select sql_id from v$sql where sql_text like 'select * from dept%';

SQL_ID

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

96kbu89824wkq

SQL> declare

2 my_plans pls_integer;

3 begin

4 my_plans:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'96kbu89824wkq');

5 end;

6 /

PL/SQL procedure successfully completed.

每次資料庫編譯一條sql語句的時候，優化器首先基於成本找到最優的計劃，然後在基線中查詢是否有匹配的計劃，如果找到了，就使用匹配的計劃；如果沒找到，就對基線中的計劃進行比較，選出成本最低的。最開始產生的最優計劃不會被使用，而是作為一個不可接受的計劃放入計劃歷史中，如果由於系統變化使得所有基線中的計劃都不可用，才會使用剛才選出來的最優計劃。

如果oracle確認不可接受的計劃不會造成效能問題（比基線中的計劃效能好），就會將其放入基線中。

SQL> show parameter optimizer_use_sql

--此引數的值為true表示允許使用基線

NAME TYPE VALUE

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

optimizer_use_sql_plan_baselines boolean TRUE

SQL> set autot on

SQL> select * from dept where deptno=10;

DEPTNO DNAME LOC

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

10 ACCOUNTING NEW YORK

Execution Plan

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

Plan hash value: 2852011669

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

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

| 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("DEPTNO"=10)

Note

-----

- SQL plan baseline "SQL_PLAN_1tkkvscv7fp020348d329" used for this statement

通過以上查詢我們可以看到：語句使用了基線"SQL_PLAN_1tkkvscv7fp020348d329"

通過如下語句顯示基線的內容：

SQL> select * from table(dbms_xplan.display_sql_plan_baseline(

2 plan_name=>'SQL_PLAN_1tkkvscv7fp020348d329',format=>'basic'));

PLAN_TABLE_OUTPUT

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

SQL handle: SYS_SQL_1cca5bc336775402

SQL text: select * from dept where deptno=10

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

Plan name: SQL_PLAN_1tkkvscv7fp020348d329 Plan id: 55104297

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

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 2852011669

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

| Id | Operation | Name |

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

| 0 | SELECT STATEMENT | |

| 1 | TABLE ACCESS BY INDEX ROWID| DEPT |

| 2 | INDEX UNIQUE SCAN | PK_DEPT |

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

20 rows selected.

裡面包含了語句的內容、執行計劃等資訊。

如果不知道plan_name，可以通過如下方式獲得：

select * from dba_sql_plan_baselines;

下面我們看一下在索引被刪除，無法用baseline的情況能否正常執行語句，對基線是否有影響：（僅用於實驗，實際中不要刪除主鍵）

SQL> alter table dept drop index PK_DEPT;

alter table dept drop index PK_DEPT

*

ERROR at line 1:

ORA-00905: missing keyword

SQL> drop index PK_DEPT;

drop index PK_DEPT

*

ERROR at line 1:

ORA-02429: cannot drop index used for enforcement of unique/primary key

--由於存在主鍵約束，無法刪除索引

SQL> alter table dept drop constraint pk_dept;

alter table dept drop constraint pk_dept

*

ERROR at line 1:

ORA-02273: this unique/primary key is referenced by some foreign keys

--由於存在外來鍵約束，無法刪除主鍵

SQL> drop table emp;

Table dropped.

SQL> alter table dept drop constraint pk_dept;

Table altered.

SQL> set autot on

SQL> select * from dept where deptno=10;

DEPTNO DNAME LOC

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

10 ACCOUNTING NEW YORK

Execution Plan

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

Plan hash value: 3383998547

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

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

| 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| DEPT | 1 | 20 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("DEPTNO"=10)

--沒辦法使用基線，只能全表掃描了。那麼這時的基線還存在嗎？

SQL> select plan_name from dba_sql_plan_baselines;

PLAN_NAME

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

SQL_PLAN_1tkkvscv7fp020348d329

SQL_PLAN_1tkkvscv7fp020e23be79

SQL_PLAN_dxw60bwfynb5h0e23be79

--基線依然存在

儘管如此，但我們重新建立索引pk_dept後，基線還是不能用了：

SQL> create index pk_dept on dept(deptno);

Index created.

SQL> select * from dept where deptno=10;

DEPTNO DNAME LOC

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

10 ACCOUNTING NEW YORK

Execution Plan

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

Plan hash value: 2985873453

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

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

| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | PK_DEPT | 1 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("DEPTNO"=10)

通過手工方式，可以將我們認為效能沒有問題的執行計劃（對應的基線）插入到系統基線中，這時候oracle並不會檢查插入的基線效能是否真的好。

我們將上面的語句使用全表掃描查詢，並將其生成基線

SQL> set autot on

SQL> select /*+full(dept)*/* from dept where deptno=10;

DEPTNO DNAME LOC

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

10 ACCOUNTING NEW YORK

Execution Plan

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

Plan hash value: 3383998547

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

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

| 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| DEPT | 1 | 20 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("DEPTNO"=10)

SQL> select sql_id from v$sql where sql_text like 'select /*+full(dept)*/* from dept%';

SQL_ID

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

1gbphzt5d0159

SQL> declare

2 plan pls_integer;

3 begin

4 plan:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'1gbphzt5d0159');

5 end;

6 /

PL/SQL procedure successfully completed.

然而這個語句並不會被select * from dept where deptno=10;使用到。即使是下面的語句也不能用到基線：

SQL> select /*+full(dept)*/* from dept where deptno='10';

DEPTNO DNAME LOC

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

10 ACCOUNTING NEW YORK

Execution Plan

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

Plan hash value: 3383998547

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

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

| 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| DEPT | 1 | 20 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("DEPTNO"=10)

Oracle認為兩個語句是不同的，只有在除了空格數以外完全相同的語句，才算是同一個語句。

如何刪除一個基線呢？

dbms_spm中只提供了drop_sql_plan_baseline函式

找到dbms_spm的原始碼，該函式的宣告為：

FUNCTION drop_sql_plan_baseline( sql_handle         IN VARCHAR2 := NULL,
                                   plan_name          IN VARCHAR2 := NULL
                                 )
RETURN PLS_INTEGER;

因此需要定義一個pls_integer型別的變數去接受結果：

SQL> declare

ret pls_integer;

begin

ret:=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SYS_SQL_1cca5bc336775402',

plan_name=> 'SQL_PLAN_1tkkvscv7fp020348d329');

end;

/ 2 3 4 5 6

PL/SQL procedure successfully completed.

SQL> select plan_name from dba_sql_plan_baselines;

PLAN_NAME

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

SQL_PLAN_1tkkvscv7fp020e23be79

SQL_PLAN_1tkkvscv7fp02da6909c3

SQL_PLAN_dxw60bwfynb5h0e23be79

--可以看到，SQL_PLAN_1tkkvscv7fp020348d329被刪除了

本次主要講了baseline的生成、檢視和刪除。Baseline對於系統中sql的穩定性、效能有重要作用，因此下一部分實驗baseline的匯入、匯出等。

如果在建立索引之前為語句建立了基線，然後建立索引。這時候，走索引進行查詢可能比全表掃描要高效。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26451536/viewspace-752946/，如需轉載，請註明出處，否則將追究法律責任。

sql plan baselines（一）

相關文章