oracle 产看执行计划_ODBA 技能SPM计划
OBA技能1-獲取執(zhí)行計(jì)劃
OBA技能2-執(zhí)行計(jì)劃順序
OBA技能３-執(zhí)行計(jì)劃順序表連接
ODBA 技能４實(shí)戰(zhàn)執(zhí)行計(jì)劃
ODBA 技能5 固定執(zhí)行計(jì)劃
? ? 因?yàn)槊看谓y(tǒng)計(jì)信息作業(yè)在收集完信息后，會(huì)觸發(fā)ACS自適應(yīng)游標(biāo)管理程序，進(jìn)行對綁定變量的窺探工作，窺探完可能就進(jìn)行硬解析生成新的執(zhí)行計(jì)劃。為此一個(gè)SQL語句因?yàn)椴豢擅枋龅脑驅(qū)е伦约旱膱?zhí)行計(jì)劃變多了，有的新計(jì)劃的成本更低了，自然選擇新計(jì)劃。或者原來成本低的計(jì)劃變得不可用了，只好選擇次級計(jì)劃。總之最終可能導(dǎo)致新選的計(jì)劃，ORACLE認(rèn)為好，實(shí)際執(zhí)行更慢。
? ?為此11G后出來個(gè)SPM和執(zhí)行計(jì)劃基線BASELINE。請不要被BASELINE給誤導(dǎo)！真實(shí)行為含義就是執(zhí)行計(jì)劃庫。SPM就是管理基庫的一套程序。
一、基礎(chǔ)概念
Oracle 11g開始，提供了一種新的固定執(zhí)行計(jì)劃的方法，即SQL plan baseline，中文名SQL執(zhí)行計(jì)劃基線(簡稱基線)，可以認(rèn)為是OUTLINE(大綱)或者SQL PROFILE的改進(jìn)版本，基本上它的主要作用可以歸納為如下兩個(gè)：
1、穩(wěn)定給定SQL語句的執(zhí)行計(jì)劃，防止執(zhí)行環(huán)境或?qū)ο蠼y(tǒng)計(jì)信息等等因子的改變對SQL語句的執(zhí)行計(jì)劃產(chǎn)生影響！
2、減少數(shù)據(jù)庫中出現(xiàn)SQL語句性能退化的概率，理論上不允許一條語句切換到一個(gè)比已經(jīng)執(zhí)行過的執(zhí)行計(jì)劃慢很多的新的執(zhí)行計(jì)劃上！
注意：
1、從Oracle的發(fā)展角度來看，估計(jì)這種方法是Oracle發(fā)展和改進(jìn)的方向，如今outline已經(jīng)被廢棄，sql profile估計(jì)在后續(xù)的發(fā)行版本中也難有改進(jìn)，因此，對于從11g開始接觸Oracle的朋友來說，一定要對sql計(jì)劃基線有所了解，因?yàn)檫@是以后的主流！
2、SQL執(zhí)行計(jì)劃基線保存在數(shù)據(jù)字典中，查詢優(yōu)化器會(huì)自動(dòng)判斷使用他們。
二、工作機(jī)制
從Oracle 11g開始，由于基線的存在，一條語句的解析過程大概如下：
SQL語句被硬解析后，CBO(優(yōu)化器)會(huì)產(chǎn)生很多個(gè)的執(zhí)行計(jì)劃，CBO從中選擇一個(gè)成本最低執(zhí)行計(jì)劃。
基于SQL語句的文本形成一個(gè)哈希值(signature)，通過這個(gè)哈希值來檢查數(shù)據(jù)字典中是否存在同樣的基線。
如果基線存在，優(yōu)化器會(huì)對剛剛產(chǎn)生的執(zhí)行計(jì)劃和保存在SQL plan baseline中的執(zhí)行計(jì)劃進(jìn)行比較。
如果基線中有與CBO剛產(chǎn)生的執(zhí)行計(jì)劃的匹配的SQL執(zhí)行計(jì)劃存在，并且被標(biāo)記為可接受(‘a(chǎn)ccepted’)，則這個(gè)CBO生成的執(zhí)行計(jì)劃被啟用。
如果基線中沒有匹配的SQ執(zhí)行計(jì)劃存在，CBO評估基線中被標(biāo)記為‘a(chǎn)ccepted’的的多個(gè)執(zhí)行計(jì)劃，并選擇其中cost最低的執(zhí)行計(jì)劃。(注意，一個(gè)語句的基線可以有多個(gè)執(zhí)行計(jì)劃被保存，這是與其他Outline和SQL profiel都不同的地方)
如果剛剛硬解析過程中CBO選擇的執(zhí)行計(jì)劃比保存在基線中的執(zhí)行計(jì)劃COST都低，這個(gè)新生成的執(zhí)行計(jì)劃被標(biāo)記為‘not-accepted’并保存在基線中。直到這個(gè)執(zhí)行計(jì)劃被演化且驗(yàn)證后才會(huì)被考慮使用，即標(biāo)記為accepted(演化和驗(yàn)證，可以簡單理解為Oracle確認(rèn)這個(gè)執(zhí)行計(jì)劃可以帶來更好的性能)。
上面講解的工作機(jī)制有點(diǎn)繞，我們白話翻譯一下。
1 假如你開啟了SQL的執(zhí)行計(jì)劃庫。
2 如果1個(gè)SQL語句發(fā)生硬解析，產(chǎn)生新的執(zhí)行計(jì)劃。然后去基庫里找，看是否有已經(jīng)存在的執(zhí)行計(jì)劃？
3 如果存在就使用該計(jì)劃
4 如果不存在，但是還存在其他的執(zhí)行計(jì)劃，那么選擇COST最低那個(gè)執(zhí)行。
5 如果這個(gè)新生成的計(jì)劃比庫里的成本都低，它就被納入庫中并標(biāo)記待檢驗(yàn)。
這里有兩個(gè)疑問，誰去驗(yàn)證？是不是每次SQL執(zhí)行都要產(chǎn)生硬解析呢？
三、基庫的一些特點(diǎn)
簡單歸納如下幾個(gè)
通過OPTIMIZER_USE_SQL_PLAN_BASELINE來控制Oracle是否使用基庫，默認(rèn)值為TRUE，即會(huì)自動(dòng)使用基庫。
11g中默認(rèn)是不會(huì)自動(dòng)創(chuàng)建基庫
與OUTLINE和SQL Profile不同，基庫中不存在分類的概念
與OUTLINE和SQL Profile不同，每個(gè)SQL語句可以有多個(gè)基庫。Oracle根據(jù)制定的規(guī)則來判斷具體是否哪個(gè)基庫
基線針對RAC中所有的實(shí)例都生效
基庫有兩個(gè)表示，一個(gè)為sql_handle，可以理解為表示語句文本的唯一標(biāo)識，一個(gè)為sql_plan_name可以理解為執(zhí)行計(jì)劃的唯一標(biāo)識
不能像sql profile一樣通過force_matching屬性將字面值不一樣的SQL語句使用一個(gè)基庫應(yīng)用多個(gè)語句。
三、創(chuàng)建基庫的幾種方式
1、自動(dòng)捕獲基庫，通過將optimizer_cature_sql_plan_baselines設(shè)置為true，優(yōu)化器為重復(fù)執(zhí)行兩次以上的SQL語句生成并保存基庫?
2、從SQL調(diào)優(yōu)集合中加載，通過使用包dbms_spm.load_plans_from_sqlset來從SQL調(diào)優(yōu)集合中加載基庫
DECLARE??l_plans_loaded??PLS_INTEGER;
BEGIN
??l_plans_loaded?:=?DBMS_SPM.load_plans_from_sqlset(
????sqlset_name?=>?'my_sqlset');
END;
/??
3、從庫緩存中加載，通過包dbms_spm.load_plans_from_cursor_cache函數(shù)為一條已經(jīng)在游標(biāo)緩存中的語句創(chuàng)建基線
DECLARE'1fkh93md0802n',plan_hash_value=>四、基庫的幾種狀態(tài)
一個(gè)SQL語句對應(yīng)的基線，我將它們歸納為三種狀態(tài)
accepted(可接受)，只有這種狀態(tài)的基庫，優(yōu)化器才會(huì)考慮此基庫中的執(zhí)行計(jì)劃
no-accepted(不可接受)，這種狀態(tài)的基庫，優(yōu)化器在SQL語句解析期間不會(huì)考慮。這種狀態(tài)的基庫必須通過演化和驗(yàn)證通過后，轉(zhuǎn)變?yōu)閍ccepted狀態(tài)后，才會(huì)被優(yōu)化器考慮使用
fixed為yes(固定)，這種狀態(tài)的基庫固有最高優(yōu)先級！比其他兩類基庫都要優(yōu)先考慮
五、查看基庫
1、基本視圖：dba_sql_plan_baselines、dba_sql_management_config
2、通過函數(shù)來查看基線的詳細(xì)信息：select?*?
from?table(
?????dbms_xplan.display_sql_plan_baseline
????(
??????sql_handle=>'SYS_SQL_11bcd50cd51504e9',
??????plan_name=>'SQL_PLAN_13g6p1maja1790cce5f0e'
?????)
);
?下面顯示具體的執(zhí)行計(jì)劃信息：
--------------------------------------------------------------------------------SQL?handle:?SQL_44c9b37ac97e85e9
SQL?text:?select?*?from?test_objects?
--------------------------------------------------------------------------------?
--------------------------------------------------------------------------------
Plan?name:?SQL_PLAN_49kdmgb4rx1g95cd5cc6d?????????Plan?id:?1557515373
Enabled:?YES?????Fixed:?NO??????Accepted:?YES?????Origin:?MANUAL-LOAD
--------------------------------------------------------------------------------?
Plan?hash?value:?3570092908?
----------------------------------------------------------------------------------
|?Id??|?Operation??????????????|?Name????????????????|?Rows??|?Bytes?|?Cost?(%CPU)|?Time?????|
----------------------------------------------------------------------------------
|???0?|?SELECT?STATEMENT???|????????????????????????|?68108?|??6518K|???275???(1)|?00:00:04?|
|???1?|??TABLE?ACCESS?FULL?|?TEST_OBJECTS?|?68108?|??6518K|???275???(1)|?00:00:04?|
----------------------------------------------------------------------------------
顯然這有點(diǎn)不方便
六、演化基庫
為了驗(yàn)證基庫中一個(gè)處于不可接受狀態(tài)的執(zhí)行計(jì)劃是否比一個(gè)處于可接受狀態(tài)的執(zhí)行計(jì)劃具有更高的效率，必須通過演化來驗(yàn)證，需要讓優(yōu)化器以不同的執(zhí)行計(jì)劃來執(zhí)行這條SQL語句，觀察不可接受狀態(tài)的執(zhí)行計(jì)劃基庫是否會(huì)帶來更好的性能，如果性能確實(shí)更高，這個(gè)不可接受狀態(tài)的基庫將會(huì)轉(zhuǎn)換為可接受狀態(tài)。演化的方式有兩種：
1、手工執(zhí)行運(yùn)行
SELECT?DBMS_SPM.evolve_sql_plan_baseline(sql_handle?=>?From?dual;-------------------------------------------------------------------------------????????????????????????Evolve?SQL?Plan?Baseline?Report
-------------------------------------------------------------------------------
Inputs:
-------
??SQL_HANDLE?=?SQL_44c9b37ac97e85e9
??PLAN_NAME??=?
??TIME_LIMIT?=?DBMS_SPM.AUTO_LIMIT
??VERIFY?????=?YES
??COMMIT?????=?YES
-------------------------------------------------------------------------------
?????????????????????????????????Report?Summary
-------------------------------------------------------------------------------
There?were?no?SQL?plan?baselines?that?required?processing.
還有time_limit/verify/commit幾個(gè)參數(shù)，可以參考文檔
2、調(diào)優(yōu)包實(shí)現(xiàn)基線的自動(dòng)演化，可以理解為，啟動(dòng)一個(gè)調(diào)度任務(wù)，周期性的檢查是否有不可接受狀態(tài)的基線庫可以被演化
手動(dòng)select?DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle?=>?NULL,??plan_name??=>?NULL,?verify?=>?'YES',??commit?=>?'NO'?)?from?dual;
這里由兩個(gè)標(biāo)記控制：
o?Verify?
??+?YES?(只有性能更好的計(jì)劃才會(huì)被演化)
??+?NO?(演化所有的計(jì)劃)
o?Commit
??+?YES?(直接演化)
??+?NO?(只生成報(bào)告)
不讓系統(tǒng)智能選擇使用新計(jì)劃
七、修改基庫
可以通過dbms_spm.alter_sql_plan_baseline包來修改基庫的一些屬性，主要有如下幾個(gè)屬性
ENABLED ：設(shè)置該屬性的值為NO告訴Oracle 11g臨時(shí)禁用某個(gè)計(jì)劃，一個(gè)SQL計(jì)劃必須同時(shí)標(biāo)記為ENABLED和ACCEPTED，否則CBO將忽略它
FIXED：設(shè)置為YES，那個(gè)計(jì)劃將是優(yōu)化器唯一的選擇，即使如果某個(gè)計(jì)劃可能擁有更低的成本。這讓DBA可以撤銷SPM的默認(rèn)行為，對于轉(zhuǎn)換一個(gè)存儲概要進(jìn)入一穩(wěn)定的SQL計(jì)劃基庫特別有用，注意當(dāng)一個(gè)新計(jì)劃被添加到被標(biāo)記為FIXED的SQL計(jì)劃基庫，該新計(jì)劃不能被利用除非它申明為FIXED狀態(tài)
AUTOPURG：設(shè)置這個(gè)屬性的值為NO告訴Oracle 11g無限期保留它，從而不用擔(dān)心SMB的自動(dòng)清除機(jī)制
plan_name : 改變SQL plan 名字
description : 改變SQL plan描述
實(shí)驗(yàn)
1 開啟SCOTT用戶
2 創(chuàng)建表
?create table test_objects as select * from all_objects
3 執(zhí)行SQL語句
? ?select * from test_objects?
4 使用SYS等其他高級用戶獲得剛才的語句SQLID
select?*?from?v$sqlarea?s?where?sql_text?order?5 SYS創(chuàng)建該SQL的基庫
SQL>?DECLARE??2??my_plans?pls_integer;
??3??BEGIN
??4??my_plans?:=?DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE?(sql_id?=>?'0g9hjnj0x1nbb');
??5??END;
??6??/
PL/SQL?procedure?successfully?completed
6 查看執(zhí)行計(jì)劃基庫
SELECT *?
FROM ? dba_sql_plan_baselines
2?修改已有的Baseline
/*********語法DBMS_SPM.ALTER_SQL_PLAN_BASELINE?(
??sql_handle??????IN?VARCHAR2?:=?NULL,
??plan_name???????IN?VARCHAR2?:=?NULL,
??attribute_name??IN?VARCHAR2,
??attribute_value?IN?VARCHAR2?)
RETURN?PLS_INTEGER;
************/
SET?SERVEROUTPUT?ON
DECLARE
?l_plans_altered??PLS_INTEGER;
BEGIN
l_plans_altered?:=?DBMS_SPM.alter_sql_plan_baseline(
sql_handle??????=>?'SQL_44c9b37ac97e85e9',
plan_name???????=>?'SQL_PLAN_49kdmgb4rx1g95cd5cc6d',
attribute_name??=>?'AUTOPURGE',
attribute_value?=>?'NO');
DBMS_OUTPUT.put_line('Plans?Altered:?'?||?l_plans_altered);
END;
/
把自動(dòng)清除機(jī)制關(guān)閉了
往執(zhí)行計(jì)劃基庫里添加新的計(jì)劃。
1 添加索引?
create?index?IX_OBJECT_NAME?on?TEST_OBJECTS?(OBJECT_NAME2使用提示強(qiáng)制走索引
select?/*+?index(test_objects?IX_OBJECT_NAME)*/?*?from?test_objects執(zhí)行成本特高
3從V$SQL_PLAN獲得
select?*?(select?*??4加入
SQL>?DECLARE??2??k1?pls_integer;
??3??begin
??4??k1?:=?DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE
??5??(
??6??sql_handle=>'SQL_44c9b37ac97e85e9',
??7??sql_id=>'5ptdb66rf053s',
??8??plan_hash_value=>127622217
??9??);
?10??end;
?11??/
PL/SQL?procedure?successfully?completed
5查詢基庫
SQL_HANDLE?????????????SQL_TEXT???PLAN_NAME????????????????????ORIGIN??????PARSING_SCHEMA_NAME????ENABLED?ACCEPTED????FIXED???REPRODUCED??AUTOPURGE???OPTIMIZER_COSTSQL_44c9b37ac97e85e9??????SQL_PLAN_49kdmgb4rx1g95cd5cc6d??MANUAL-LOAD?SCOTT?????????????????YES???????YES??????????NO???????YES?????????NO???????275
SQL_44c9b37ac97e85e9????<CLOB>??SQL_PLAN_49kdmgb4rx1g9f1aba20d??MANUAL-LOAD?SCOTT?????????????????YES???????YES??????????NO???????YES????????YES??????35397
我們成功加入了！！
６ 啟用我們走索引的計(jì)劃
SET?SERVEROUTPUT?ONDECLARE
?l_plans_altered??PLS_INTEGER;
BEGIN
l_plans_altered?:=?DBMS_SPM.alter_sql_plan_baseline(
sql_handle??????=>?'SQL_44c9b37ac97e85e9',
plan_name???????=>?'SQL_PLAN_49kdmgb4rx1g9f1aba20d',
attribute_name??=>?'FIXED',
attribute_value?=>?'YES');
DBMS_OUTPUT.put_line('Plans?Altered:?'?||?l_plans_altered);
END;
/
７驗(yàn)證
８有了基庫后普通的V$SQL_PLAN 就沒它了
? FROM V$SQL_PLAN
?WHERE SQL_ID = '0g9hjnj0x1nbb'
9 刪除基庫里一個(gè)執(zhí)行計(jì)劃
SET?SERVEROUTPUT?ONDECLARE
?l_plans_dropped??PLS_INTEGER;
BEGIN
?l_plans_dropped?:=?DBMS_SPM.drop_sql_plan_baseline?(
???sql_handle?=>?'SQL_44c9b37ac97e85e9',
???plan_name??=>?'SQL_PLAN_49kdmgb4rx1g95cd5cc6d');
?DBMS_OUTPUT.put_line(l_plans_dropped);
END;
/
總結(jié)下，SPM是好東西，直接開啟參數(shù)產(chǎn)生基庫，然后使用人工進(jìn)化非智能模式。基庫唯一缺點(diǎn)沒有SQLID，無法通過SQLID找到對應(yīng)的基庫，只能看SQLTEXT。
總結(jié)
以上是生活随笔為你收集整理的oracle 产看执行计划_ODBA 技能SPM计划的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 广发e贷分期卡是什么
- 下一篇： 医保怎么在手机上交费