sql plan baselines
網(wǎng)站建設(shè)哪家好，找創(chuàng)新互聯(lián)！專注于網(wǎng)頁(yè)設(shè)計(jì)、網(wǎng)站建設(shè)、微信開(kāi)發(fā)、重慶小程序開(kāi)發(fā)公司、集團(tuán)企業(yè)網(wǎng)站建設(shè)等服務(wù)項(xiàng)目。為回饋新老客戶創(chuàng)新互聯(lián)還提供了宜賓免費(fèi)建站歡迎大家使用！
在一個(gè)成熟的生產(chǎn)系統(tǒng)上，我們總是希望運(yùn)行越穩(wěn)定越好，執(zhí)行計(jì)劃越確定越好。但由于統(tǒng)計(jì)信息的變化、優(yōu)化參數(shù)的修改、系統(tǒng)設(shè)置的修改、profile的創(chuàng)建等，都有可能造成執(zhí)行計(jì)劃的變化，從而性能上會(huì)有變化。這種變化不一定都是差的方向，比如優(yōu)化器版本的升級(jí)，一般會(huì)使執(zhí)行計(jì)劃更加優(yōu)化，但作為運(yùn)維DBA，要確保萬(wàn)無(wú)一失，可能還是會(huì)相信自己。
為了應(yīng)對(duì)可能的執(zhí)行計(jì)劃變化，oracle提供了baseline，我們翻譯為基線，用于保證執(zhí)行計(jì)劃的穩(wěn)定。
以下用實(shí)驗(yàn)來(lái)說(shuō)明：
sql baseline有兩種生成方式：
（1）需要通過(guò)AWR報(bào)告或者sql tuning set（STS）生成，因此，首先需要查看當(dāng)前系統(tǒng)中可用的STS：
[oracle@localhost ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Sun Dec 23 00:10:50 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
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報(bào)告中提取，則需要先用AWR報(bào)告生成STS，再執(zhí)行以上步驟。
（2）從shared_pool_area中獲取sql
比如我們剛剛執(zhí)行了select * from dept where deptno=10，想要將此語(yǔ)句歸入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.
每次數(shù)據(jù)庫(kù)編譯一條sql語(yǔ)句的時(shí)候，優(yōu)化器首先基于成本找到最優(yōu)的計(jì)劃，然后在基線中查找是否有匹配的計(jì)劃，如果找到了，就使用匹配的計(jì)劃；如果沒(méi)找到，就對(duì)基線中的計(jì)劃進(jìn)行比較，選出成本最低的。最開(kāi)始產(chǎn)生的最優(yōu)計(jì)劃不會(huì)被使用，而是作為一個(gè)不可接受的計(jì)劃放入計(jì)劃歷史中，如果由于系統(tǒng)變化使得所有基線中的計(jì)劃都不可用，才會(huì)使用剛才選出來(lái)的最優(yōu)計(jì)劃。
如果oracle確認(rèn)不可接受的計(jì)劃不會(huì)造成性能問(wèn)題（比基線中的計(jì)劃性能好），就會(huì)將其放入基線中。
SQL> show parameter optimizer_use_sql
--此參數(shù)的值為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
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 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
通過(guò)以上查詢我們可以看到：語(yǔ)句使用了基線"SQL_PLAN_1tkkvscv7fp020348d329"
通過(guò)如下語(yǔ)句顯示基線的內(nèi)容：
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.
里面包含了語(yǔ)句的內(nèi)容、執(zhí)行計(jì)劃等信息。
如果不知道plan_name，可以通過(guò)如下方式獲得：
select * from dba_sql_plan_baselines;
下面我們看一下在索引被刪除，無(wú)法用baseline的情況能否正常運(yùn)行語(yǔ)句，對(duì)基線是否有影響：（僅用于實(shí)驗(yàn)，實(shí)際中不要?jiǎng)h除主鍵）
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
--由于存在主鍵約束，無(wú)法刪除索引
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
--由于存在外鍵約束，無(wú)法刪除主鍵
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
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 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)
--沒(méi)辦法使用基線，只能全表掃描了。那么這時(shí)的基線還存在嗎？
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
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 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)
通過(guò)手工方式，可以將我們認(rèn)為性能沒(méi)有問(wèn)題的執(zhí)行計(jì)劃（對(duì)應(yīng)的基線）插入到系統(tǒng)基線中，這時(shí)候oracle并不會(huì)檢查插入的基線性能是否真的好。
我們將上面的語(yǔ)句使用全表掃描查詢，并將其生成基線
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
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 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.
然而這個(gè)語(yǔ)句并不會(huì)被select * from dept where deptno=10;使用到。即使是下面的語(yǔ)句也不能用到基線：
SQL> select /*+full(dept)*/* from dept where deptno='10';
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 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認(rèn)為兩個(gè)語(yǔ)句是不同的，只有在除了空格數(shù)以外完全相同的語(yǔ)句，才算是同一個(gè)語(yǔ)句。
如何刪除一個(gè)基線呢？
dbms_spm中只提供了drop_sql_plan_baseline函數(shù)
找到dbms_spm的源碼，該函數(shù)的聲明為：
FUNCTION drop_sql_plan_baseline( sql_handle IN VARCHAR2 := NULL,
plan_name IN VARCHAR2 := NULL
)
RETURN PLS_INTEGER;
因此需要定義一個(gè)pls_integer類型的變量去接受結(jié)果：
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對(duì)于系統(tǒng)中sql的穩(wěn)定性、性能有重要作用，因此下一部分實(shí)驗(yàn)baseline的導(dǎo)入、導(dǎo)出等。
如果在創(chuàng)建索引之前為語(yǔ)句創(chuàng)建了基線，然后創(chuàng)建索引。這時(shí)候，走索引進(jìn)行查詢可能比全表掃描要高效。
重慶分公司
重慶分公司
