sql plan management

本部分描述如何用sql plan management管理sql plan。

? sql plan baselines概述

Oracle Database 11g引入了一個名為SQL plan Management(SPM)的新計劃穩定性特性，它讓資料庫透過sql plan baseline

控制sql計劃的演變。SPM的目的是面對資料庫升級、系統和資料庫更改以及應用升級和故障修復等變動時保護sql程式碼的效能。

? sql Plan baselines結構

一個計劃基線包含一個或者多個接受的plan,每個計劃包含如下資訊：

n hit集合

n plan hash value

n plan 相關資訊

plan history是一個計劃集，包括接受的和不接受的.只有接受的plans在sql plan baseline,baseline的plan是plan history的一個子集。

? Managing Sql Plan Baselines

? Capturing sql plan baselines

在sql plan baseline捕獲階段，資料庫探測plan 改變並記錄新的plan，這樣可以演變。為了這個目的，資料庫為每一個sql語句維護了一個

plan history。因為特殊的sql不會重複因此不會遭受效能退化，資料庫只會維護重複執行sql的plan history。

為了識別重複執行的sql語句，資料庫維護一個包含最佳化器評估過的sql id的日誌(透過trace，可以看到，應該記錄到sqllog$)，

當sql解析或者執行時，如果該sql已經記錄了，資料庫就認為這個sql是重複執行的。

每個重複執行的sql語句，資料庫維護最佳化器產生的所有plan的一個plan history。在plan history中被接受的子集就是sql plan baseline.

在sql plan 捕獲階段，有自動捕獲和手動載入兩種方式。

n 自動捕獲

可以透過設定OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES 為true，啟用sql計劃基線自動裝載，如下所示：

16:20:55 sys@vposdb> alter system set optimizer_capture_sql_plan_baselines=true;

System altered.

optimizer_capture_sql_plan_baselines預設值為false,表示自動假話捕捉預設關閉。

資料庫使用每條sql語句的最佳化程式的詳細資料重新生成執行計劃。每條sql語句的第一個計劃被標記為供最佳化程式使用。此時，計劃歷史和sql計劃基線

是相同的。最佳化器隨後生成的sql語句的所有新計劃成為計劃歷史的組成部分。在最後的sql計劃基線計劃階段(sql plan baseline evolution)中，

資料庫將對sql計劃基線新增已證明不會導致效能衰退的計劃。

手動sql計劃裝載

n 從庫快取中載入

我們可以使用dbms_spm.load_plans_from_cursor_cache來將sql計劃基線手工匯入資料字典。此函式過載了多次以支援不同的方法來識別哪些遊標需要處理。

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (

sql_id IN VARCHAR2,

plan_hash_value IN NUMBER := NULL,

sql_text IN CLOB,

fixed IN VARCHAR2 := 'NO',

enabled IN VARCHAR2 := 'YES')

RETURN PLS_INTEGER;

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (

sql_id IN VARCHAR2,

plan_hash_value IN NUMBER := NULL,

sql_handle IN VARCHAR2,

fixed IN VARCHAR2 := 'NO',

enabled IN VARCHAR2 := 'YES')

RETURN PLS_INTEGER;

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (

sql_id IN VARCHAR2,

plan_hash_value IN NUMBER := NULL,

fixed IN VARCHAR2 := 'NO',

enabled IN VARCHAR2 := 'YES')

RETURN PLS_INTEGER;

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (

attribute_name IN VARCHAR2,

attribute_value IN VARCHAR2,

fixed IN VARCHAR2 := 'NO',

enabled IN VARCHAR2 := 'YES')

RETURN PLS_INTEGER;

我們可以透過兩種方式來載入。

1. 透過指定以下屬性中的一個來識別一些sql語句

attribute_name

One of possible attribute names:

'SQL_TEXT' sql語句的文字內容

'PARSING_SCHEMA_NAME' 用於解析遊標的schema

'MODULE' 執行了這條sql語句的模組名

'ACTION' 執行了這條語句的活動名

例子如下

DECLARE

ret PLS_INTEGER;

BEGIN

ret := dbms_spm.load_plans_from_cursor_cache(attribute_name => 'sql_text',

attribute_value => '%/* MySqlStm */%');

dbms_output.put_line(ret || ' SQL plan baseline(s) created');

END;

/

上面pl/sql塊的意思是為庫快取裡每一條文字中包含字串MySqlStm的sql語句建立一個計劃基線。

2. 透過sql_id載入

透過sql_id載入比較簡單，如下：

DECLARE

ret PLS_INTEGER;

BEGIN

ret := dbms_spm.load_plans_from_cursor_cache(sql_id => '&sql_id',

plan_hash_value => NULL);

dbms_output.put_line(ret || ' SQL plan baseline(s) created');

END;

/

這裡面plan_hash_value是可選的，為null意味著該sql_id的所有可用的執行計劃都會被載入。

CREATE TABLE t (id, n, pad, CONSTRAINT t_pk PRIMARY KEY (id))

AS

SELECT rownum, rownum, rpad('*',500,'*')

FROM dual

CONNECT BY level <= 1000;

CREATE INDEX i ON t (n);

11:01:27 sys@vposdb> SELECT /*+ full(t) */ count(pad) FROM t WHERE n = 42;

COUNT(PAD)

----------

1

Elapsed: 00:00:00.02

Execution Plan

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

Plan hash value: 2966233522

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

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

| 0 | SELECT STATEMENT | | 1 | 505 | 21 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 505 | | |

|* 2 | TABLE ACCESS FULL| T | 1 | 505 | 21 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - filter("N"=42)

Statistics

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

1 recursive calls

0 db block gets

74 consistent gets

0 physical reads

0 redo size

528 bytes sent via SQL*Net to client

520 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

11:01:42 sys@vposdb> SELECT /*+ index(t) */ count(pad) FROM t WHERE n = 42;

COUNT(PAD)

----------

1

Elapsed: 00:00:00.01

Execution Plan

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

Plan hash value: 3694077449

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

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

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

| 1 | SORT AGGREGATE | | 1 | 505 | | |

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

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

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

Predicate Information (identified by operation id):

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

3 - access("N"=42)

Statistics

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

1 recursive calls

0 db block gets

3 consistent gets

0 physical reads

0 redo size

528 bytes sent via SQL*Net to client

520 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

明顯第二個執行計劃比第一個效率更高。如果我們無法修改sql可以使用sql計劃基線來解決這個問題

10:45:22 SQL>

ALTER SESSION SET optimizer_capture_sql_plan_baselines = TRUE;

Executed in 0 seconds

SELECT /*+ full(t) */ count(pad) FROM t WHERE n = 42;

COUNT(PAD)

----------

1

Executed in 0.046 seconds

SELECT /*+ full(t) */ count(pad) FROM t WHERE n = 42;

COUNT(PAD)

----------

1

Executed in 0.047 seconds

ALTER SESSION SET optimizer_capture_sql_plan_baselines = FALSE;

Executed in 0 seconds

13:59:08 sys@vposdb> SELECT /*+ full(t) */ count(pad) FROM t WHERE n = 42;

COUNT(PAD)

----------

1

Elapsed: 00:00:00.01

Execution Plan

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

Plan hash value: 2966233522

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

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

| 0 | SELECT STATEMENT | | 1 | 505 | 21 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 505 | | |

|* 2 | TABLE ACCESS FULL| T | 1 | 505 | 21 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - filter("N"=42)

Note

-----

- SQL plan baseline "SQL_PLAN_3u6sbgq7v4u8z3fdbb376" used for this statement

我們可以透過上面輸出的計劃名，可以透過資料字典檢視dba_sql_plan_baselines找到sql計劃基線的標示符：

sql控制程式碼(handle)

14:04:55 sys@vposdb> select sql_handle

14:05:13 2 from dba_sql_plan_baselines

14:05:13 3 where plan_name='SQL_PLAN_3u6sbgq7v4u8z3fdbb376' ;

SQL_HANDLE

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

SQL_3d1b0b7d8fb2691f

Elapsed: 00:00:00.00

14:05:14 sys@vposdb>

接下來，我們從庫快取中載入sql語句使用索引掃描的執行計劃。

14:21:08 sys@vposdb> select sql_text,sql_id from v$sql where sql_text like '%/*+ index(t) */ count(pad) FROM t WHERE n = 42%' ;

SQL_TEXT

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

SQL_ID

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

SELECT /*+ index(t) */ count(pad) FROM t WHERE n = 42

7jvvrqqd0xffz

explain plan for SELECT /*+ index(t) */ count(pad) FROM t WHERE n = 42

4jwnyvhudy196

explain plan for SELECT /*+ index(t) */ count(pad) FROM t WHERE n = 42

4jwnyvhudy196

SQL_TEXT

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

SQL_ID

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

explain plan for SELECT /*+ index(t) */ count(pad) FROM t WHERE n = 42

4jwnyvhudy196

select sql_text,sql_id from v$sql where sql_text like '%/*+ index(t) */ count(pad) FROM t WHERE n = 42%'

02cnh5mgkagba

EXPLAIN PLAN SET STATEMENT_ID='PLUS4294967295' FOR SELECT /*+ index(t) */ count(pad) FROM t WHERE n = 42

d6pw9fs89anwp

SQL_TEXT

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

SQL_ID

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

select sql_text,sql_id from v$sql where sql_text like '%/*+ index(t) */ count(pad) FROM t WHERE n = 42%'

bjycuvyfj2yvs

SELECT /*+ index(t) */ count(pad) FROM t WHERE n = 42

dat4n4845zdxc

select * from v$sql where sql_text like '%/*+ index(t) */ count(pad) FROM t WHERE n = 42%'

3hpk25qpwmxhw

9 rows selected.

Elapsed: 00:00:00.39

14:22:09 sys@vposdb> SELECT * FROM table (

14:23:34 2 DBMS_XPLAN.DISPLAY_CURSOR('dat4n4845zdxc'));

PLAN_TABLE_OUTPUT

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

SQL_ID dat4n4845zdxc, child number 0

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

SELECT /*+ index(t) */ count(pad) FROM t WHERE n = 42

Plan hash value: 3694077449

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

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

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

| 1 | SORT AGGREGATE | | 1 | 505 | | |

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

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

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

Predicate Information (identified by operation id):

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

3 - access("N"=42)

20 rows selected.

Elapsed: 00:00:00.04

14:26:18 sys@vposdb> DECLARE

14:26:53 2 ret PLS_INTEGER;

14:26:53 3 BEGIN

14:26:53 4 ret := dbms_spm.load_plans_from_cursor_cache(sql_id => 'dat4n4845zdxc',

14:26:53 5 plan_hash_value => NULL,

14:26:53 6 sql_handle =>'SQL_3d1b0b7d8fb2691f');

14:26:53 7 dbms_output.put_line(ret || ' SQL plan baseline(s) created');

14:26:53 8 END;

14:26:54 9 /

1 SQL plan baseline(s) created

PL/SQL procedure successfully completed.

14:27:47 sys@vposdb> select plan_name from dba_sql_plan_baselines where sql_handle='SQL_3d1b0b7d8fb2691f';

PLAN_NAME

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

SQL_PLAN_3u6sbgq7v4u8z3fdbb376

SQL_PLAN_3u6sbgq7v4u8z59340d78

我們現在已經有兩個執行計劃了。我們將全表掃描的baseline刪除掉。

SET SERVEROUTPUT ON

14:56:40 sys@vposdb> DECLARE

14:56:59 2 ret PLS_INTEGER;

14:56:59 3 BEGIN

14:56:59 4 ret := dbms_spm.drop_sql_plan_baseline(sql_handle =>'SQL_3d1b0b7d8fb2691f',

14:56:59 5 plan_name =>'SQL_PLAN_3u6sbgq7v4u8z3fdbb376'

14:56:59 6 );

14:56:59 7 dbms_output.put_line(ret || ' SQL plan baseline(s) created');

14:56:59 8 END;

14:56:59 9 /

1 SQL plan baseline(s) created

PL/SQL procedure successfully completed.

接下來，我們再次執行sql看看是否選用新的計劃基線。

14:58:13 sys@vposdb> SELECT /*+ full(t) */ count(pad) FROM t WHERE n = 42;

COUNT(PAD)

----------

1

Elapsed: 00:00:00.07

Execution Plan

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

Plan hash value: 3694077449

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

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

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

| 1 | SORT AGGREGATE | | 1 | 505 | | |

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

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

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

Predicate Information (identified by operation id):

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

3 - access("N"=42)

Note

-----

- SQL plan baseline "SQL_PLAN_3u6sbgq7v4u8z59340d78" used for this statement

我們看到及時sql語句中包含提示full,執行計劃也不在使用全表掃描。

此外，我們還可以檢查v$sql中sql_plan_baseline列。

16:21:40 sys@vposdb> select sql_plan_baseline from v$sql where sql_id='bhk25zu92v046';

SQL_PLAN_BASELINE

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

SQL_PLAN_3u6sbgq7v4u8z3fdbb376

? 顯示sql計劃基線。

當我們讓sql使用了baseline後，baseline對應什麼plan呢，這個是我在看到這部門內容之前，思考但是沒有查詢到答案的問題。

使用

SELECT * FROM table (

DBMS_XPLAN.DISPLAY_CURSOR('61p8t9216bc71',1));

並不能顯示正確的執行計劃，這個只是最初的執行計劃，當然我們有一個方法，就是將sql_id剔出庫快取，

重新載入後，生成的執行計劃為baseline之一(當一個sql有多個baseline時)。

SELECT * FROM TABLE(

DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(

sql_handle=>'SQL_3d1b0b7d8fb2691f'));

16:51:18 sys@vposdb> SELECT * FROM TABLE(

17:11:54 2 DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(

17:11:54 3 sql_handle=>'SQL_PLAN_3u6sbgq7v4u8z59340d78'));

PLAN_TABLE_OUTPUT

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

Error: specified SQL handle SQL_PLAN_3u6sbgq7v4u8z59340d78 does not exist

Elapsed: 00:00:00.02

17:11:55 sys@vposdb> SELECT * FROM TABLE(

17:12:21 2 DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(

17:12:21 3 sql_handle=>'SQL_3d1b0b7d8fb2691f'));

PLAN_TABLE_OUTPUT

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

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

SQL handle: SQL_3d1b0b7d8fb2691f

SQL text: SELECT /*+ full(t) */ count(pad) FROM t WHERE n = 42

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

Plan name: SQL_PLAN_3u6sbgq7v4u8z3fdbb376 Plan id: 1071362934

Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE

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

Plan hash value: 2966233522

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

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

| 0 | SELECT STATEMENT | | 1 | 505 | 21 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 505 | | |

|* 2 | TABLE ACCESS FULL| T | 1 | 505 | 21 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - filter("N"=42)

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

Plan name: SQL_PLAN_3u6sbgq7v4u8z59340d78 Plan id: 1496583544

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

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

Plan hash value: 3694077449

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

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

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

| 1 | SORT AGGREGATE | | 1 | 505 | | |

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

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

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

Predicate Information (identified by operation id):

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

3 - access("N"=42)

46 rows selected.

Elapsed: 00:00:00.16

11g-sql plan management

相關文章