oracle 11g sql plan baseline(1)基本使用

學習11g sqlplan baselines

sql plan baselines將取代outline，由於目前負責的幾套rac版本已經是11.1.0.7.2所學習下sqlplan base lines特此記錄
當想獲得一個穩定的執行計劃時候可以使用sqlplan baselines，原理跟outline類似
下面將具體操作，簡單講解下原理

參考資料
1.otn
2.oracle trouble shooting performance

SQL> show user
USER is "SYS"

建立測試用表
SQL> create table t2 (a int);

Table created.

SQL> declare
2 begin
3 for i in 1..1000 loop
4 insert into t2 values(i);
5 end loop;
6 commit;
7 end;
8 /

PL/SQL procedure successfully completed.

SQL> execute dbms_stats.gather_table_stats('SYS','T2');

PL/SQL procedure successfully completed.

SQL> select * from t2 where a=2;

A
----------
2

SQL> SELECT * FROM table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 78nw3vwby13ha, child number 0
-------------------------------------
select * from t2 where a=2

Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |       |     2 (100)|          |
|* 1 | TABLE ACCESS FULL| T2   |     1 |     3 |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("A"=2)

18 rows selected.

可以看到現在走的是FTS

SQL> 建立sqlplan baselins 這是用引數方式建立（session,system可設定），這種叫自動捕獲方式，不需要任何許可權
SQL> ALTER SESSION SET optimizer_capture_sql_plan_baselines = TRUE;（開啟自動捕獲）

Session altered.

自動捕獲方式原理是第一次sql語句執行先檢視資料字典看有對應的sqlplan baseline沒(用簽名查詢)，沒有的話檢視一個log（log中有簽名，所謂的簽名就是將sql語句格式化後的一個標記，可以不分大小寫空格之類），沒有的話在log中根據sql_text生成一個籤
名
第2次執行SQL語句時候還是先看有對應的sqlplan baselines沒，沒有的話檢視log中有對應的簽名沒，有的話（此時已經有了），此時才存入sql plan baselines（這個時候存入的執行計劃
，就是你sql語句此時此刻的執行計劃，根據統計資訊之類算出來的）
所以這個方式為一個sql語句生成sqlplan baseline需要執行2次sql

SQL> ALTER SESSION SET optimizer_capture_sql_plan_baselines = FALSE; （生成後關閉）

Session altered.

下面這個方法比較好,從shared pool->library cache中直接載入sql plan baseline，此時的plan 為sql語句執行的plan，為fts
SQL> DECLARE
2    ret PLS_INTEGER;
3 BEGIN
4    ret := dbms_spm.load_plans_from_cursor_cache(sql_id          => '&sql_id',
5                                                 plan_hash_value => NULL);
6    dbms_output.put_line(ret || ' SQL plan baseline(s) created');
7 END;
8 /
Enter value for sql_id: 78nw3vwby13ha
old   4:   ret := dbms_spm.load_plans_from_cursor_cache(sql_id          => '&sql_id',
new   4:   ret := dbms_spm.load_plans_from_cursor_cache(sql_id          => '78nw3vwby13ha',

PL/SQL procedure successfully completed.

檢視
SQL> SELECT sql_handle, sql_text, enabled, accepted
2 FROM dba_sql_plan_baselines
3 WHERE created > systimestamp - to_dsinterval('0 00:15:00');

SQL_HANDLE                     SQL_TEXT             ENA ACC
------------------------------ -------------------- --- ---
SYS_SQL_f43222572aa1cbaf       select * from t2 whe YES YES
                               re a=2

新生成的sql plan baselines

SQL> SQL> create index t2_ind on t2(a);

Index created.

此時建立一個index

SQL> select * from t2 where a=2;

A
----------
2

SQL> SELECT * FROM table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 78nw3vwby13ha, child number 1
-------------------------------------
select * from t2 where a=2

Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |       |     2 (100)|          |
|* 1 | TABLE ACCESS FULL| T2   |     1 |     3 |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("A"=2)

Note
-----
- SQL plan baseline SYS_SQL_PLAN_2aa1cbafb860bcf2 used for this statement~~~~使用了sql plan baselines

22 rows selected.

可以看到plan 被固定為fts了

SQL> select * from T2 where A=2; 讓SQL語句大小寫

A
----------
2

SQL> SELECT * FROM table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 6ja9pu3tjhw6w, child number 1
-------------------------------------
select * from T2 where A=2

Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |       |     2 (100)|          |
|* 1 | TABLE ACCESS FULL| T2   |     1 |     3 |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("A"=2)

Note
-----
- SQL plan baseline SYS_SQL_PLAN_2aa1cbafb860bcf2 used for this statement ~~~~使用了sqlplan baseline

22 rows selected.
可以看到簽名的作用，這樣sql語句經過格式化不區分大小寫空格之類了
下面是空格

SQL> select * from t2 where a=2;

A
----------
2

SQL> SELECT * FROM table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID dj9yskcth9vj7, child number 1
-------------------------------------
select * from t2 where a=2

Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |       |     2 (100)|          |
|* 1 | TABLE ACCESS FULL| T2   |     1 |     3 |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("A"=2)

Note
-----
- SQL plan baseline SYS_SQL_PLAN_2aa1cbafb860bcf2 used for this statement~~~~使用了sqlplan baseline

22 rows selected.

刪除這個物件，對應的sqlplan baseline不會刪除

SQL> drop table t2;

Table dropped.

SQL> SELECT sql_handle, sql_text, enabled, accepted
2 FROM dba_sql_plan_baselines
3 WHERE created > systimestamp - to_dsinterval('0 00:15:00');

SQL_HANDLE                     SQL_TEXT             ENA ACC
------------------------------ -------------------- --- ---
SYS_SQL_f43222572aa1cbaf       select * from t2 whe YES NO~~~這個baseline是走index的（建立index之後的）
                               re a=2

SYS_SQL_f43222572aa1cbaf select * from t2 whe YES YES ~~這個baseline是走fts的
re a=2

分析：
首先可以看到對應的baseline未刪除，有2個baseline其中一個是未建立index前生成走fts的baseline
第2個是怎麼生成的呢，生成第一個fts baseline後,建立index然後再次執行sql語句，此時sql語句用簽名查詢sqlplan baselines，此時發現存在baselines(走fts的)，關鍵步驟到了
此時這個sql語句用當前統計資訊（此時有index統計資訊了）計算出的plan與存在的baseline比較執行計劃一樣不，此時比較是index scan與fts比較，發現不一樣，那麼走index的執行計劃
存入為一個新baseline，注意這個新baseline accepted屬性為no此時表示這個baseline不能使用（即便他的執行計劃是更好的，需要演化，稍後講解演化），所以還是使用了走fts的baseline

此時在重新建立物件
SQL> create table t2 (a int);

Table created.

SQL> declare
2 begin
3 for i in 1..500 loop~~~~~資料量有變化
4 insert into t2 values(i);
5 commit;
6 end loop;
7 end;
8 /

PL/SQL procedure successfully completed.

SQL> create index t2_id on t2(a);

Index created.

SQL> execute dbms_stats.gather_table_stats('SYS','T2');

PL/SQL procedure successfully completed.

SQL> select * from t2 where a=2;

A
----------
2

SQL> SELECT * FROM table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID dj9yskcth9vj7, child number 1
-------------------------------------
select * from t2 where a=2

Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |       |     2 (100)|          |
|* 1 | TABLE ACCESS FULL| T2   |     1 |     3 |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("A"=2)

Note
-----
- SQL plan baseline SYS_SQL_PLAN_2aa1cbafb860bcf2 used for this statement

22 rows selected.

可以看到由於sql baseline未刪除所以可以直接使用

又多了一個baseline，原因很簡單，我們重新建立的表T2 資料量變化了（上面還有index，所以產生了不一樣的plan，存入為了新baseline但accetped為no，新的都為no）,還是用了fts的
baseline

SQL> SELECT sql_handle, sql_text, enabled, accepted
2 FROM dba_sql_plan_baselines
3 WHERE created > systimestamp - to_dsinterval('0 00:15:00');

SQL_HANDLE                     SQL_TEXT             ENA ACC
------------------------------ -------------------- --- ---
SYS_SQL_f43222572aa1cbaf       select * from t2 whe YES NO
                               re a=2

SYS_SQL_f43222572aa1cbaf select * from t2 whe YES NO
re a=2

SYS_SQL_f43222572aa1cbaf select * from t2 whe YES YES
re a=2

補充：

用dbm_spm建立baseline時候，不需要物件許可權，需要下面系統許可權（default dba role擁有）
SQL> select * from session_privs where privilege like '%ADMINISTER SQL MAN%';

PRIVILEGE
----------------------------------------
ADMINISTER SQL MANAGEMENT OBJECT

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

oracle 11g sql plan baseline(1)基本使用

相關文章