1.?? 簡(jiǎn)介

Oracle Database11gR1引進(jìn)了SQL PlanManagement（簡(jiǎn)稱SPM），一套允許DBA捕獲和保持任意SQL語(yǔ)句執(zhí)行計(jì)劃最優(yōu)的新工具，這樣，限制了刷新優(yōu)化器統(tǒng)計(jì)數(shù)據(jù)，已有應(yīng)用改變，甚至數(shù)據(jù)庫(kù)版本升級(jí)帶來的影響。本文幫助對(duì)SPM原理基本了解，并對(duì)其性能優(yōu)化能力進(jìn)行簡(jiǎn)要的說明。

2.?? SPM原理和機(jī)制

Oracle 11g通過一個(gè)簡(jiǎn)單而優(yōu)雅的方法實(shí)施了解決SQL計(jì)劃意外惡化的一套稱為SQL Plan Management(SPM)的新特點(diǎn)。只要用戶會(huì)話開啟了自動(dòng)SQL Plan Baseline捕獲，CBO就會(huì)在SQL Management Base(SMB)內(nèi)記錄該會(huì)話內(nèi)執(zhí)行的任何SQL，把SQL語(yǔ)句文本，梗概（Outline）,綁定變量，及其編譯環(huán)境等存儲(chǔ)為一個(gè)SQL Plan Baseline。

由于這是語(yǔ)句第一次執(zhí)行，Oracle11g會(huì)把當(dāng)時(shí)的執(zhí)行計(jì)劃當(dāng)成最優(yōu)的。正是在相同SQL語(yǔ)句第二次執(zhí)行時(shí)，SPM的優(yōu)雅才體現(xiàn)的更加明顯。在語(yǔ)句的第二次執(zhí)行期間，CBO會(huì)比較語(yǔ)句的執(zhí)行計(jì)劃和存儲(chǔ)在SMB中的計(jì)劃，新計(jì)劃被評(píng)估看它是否比SMB中的計(jì)劃更高效。

如果新計(jì)劃會(huì)改善語(yǔ)句的性能，那么，SPM會(huì)把新計(jì)劃標(biāo)記為該語(yǔ)句最好的計(jì)劃。只要DBA沒修改OPTIMIZER_USE_SQL_PLAN_BASELINES參數(shù)的默認(rèn)設(shè)置（true）,那么，CBO就會(huì)在當(dāng)前的語(yǔ)句執(zhí)行中采用新的計(jì)劃。否則，如果新計(jì)劃降低了語(yǔ)句的性能，那么，CBO會(huì)從SMB中所有可接受計(jì)劃中選擇一個(gè)成本最低的計(jì)劃，并且，SPM會(huì)把那個(gè)新計(jì)劃存儲(chǔ)到SMB中，因?yàn)樵诓痪玫奈磥?#xff0c;該新計(jì)劃也許成為不錯(cuò)的選擇。

2.1.? 捕獲SQL Plan Basebline

Oracle11g中，捕獲SQLPlan Baseline并存儲(chǔ)到SMB中是非常容易的事情。首先，OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES參數(shù)控制SQL Plan Baselines自動(dòng)捕獲是否開啟，該參數(shù)的默認(rèn)設(shè)置為FALSE，這意味著SQL Plan Baselines默認(rèn)不會(huì)被自動(dòng)捕獲。然而，DBA在會(huì)話或系統(tǒng)級(jí)將它設(shè)置為TRUE，SPM就開始記錄SQL語(yǔ)句的執(zhí)行，當(dāng)一條SQL語(yǔ)句被執(zhí)行多于一次時(shí)，該SQL語(yǔ)句就被認(rèn)為SQL Plan Baselines捕獲的候選。?

其次，Oracle11g有個(gè)新的包DBMS_SPM，通過從以下幾個(gè)來源手工“種植”計(jì)劃,可以預(yù)先捕獲和引進(jìn)大量SQL語(yǔ)句：

??數(shù)據(jù)庫(kù)Library Cache中一個(gè)或多個(gè)SQL語(yǔ)句能被用來創(chuàng)建SQL PlanBaselines。過程LOAD_PLANS_FROM_CURSOR_CACHE能被用來在Library Cache中捕獲任何語(yǔ)句的子集作為潛在SMB的候選。?

??存儲(chǔ)于SQL Tuning Set或一個(gè)AWR快照中的SQL語(yǔ)句能被過程LOAD_PLANS_FROM_SQLSET捕獲和被轉(zhuǎn)換進(jìn)SQL Plan Baselines。

??最后，SQL Plan Baselines能來自一個(gè)導(dǎo)入存儲(chǔ)表。這意味著可以從不同的數(shù)據(jù)庫(kù)捕獲語(yǔ)句。

3.??查看SQL Plan Baseline信息

被捕獲和存儲(chǔ)在SMB中的SQL Plan Baseline元數(shù)據(jù)包含SPM和CBO用來控制計(jì)劃的屬性。當(dāng)新計(jì)劃進(jìn)入SMB時(shí)，它被標(biāo)為ENABLED,但還不能標(biāo)記ACCEPTED，直到：

1)?? CBO已經(jīng)評(píng)估了該計(jì)劃并判斷它為最好的計(jì)劃；

2)?? 計(jì)劃已被演化為ACCEPTED模式。在CBO考慮采用一個(gè)計(jì)劃前，該計(jì)劃必須被標(biāo)記為ENABLED和ACCEPTED。

查看這些元數(shù)據(jù)最簡(jiǎn)單的方法就是查詢DBA_SQL_PLAN_BASELINES字典視圖。下面是一個(gè)控制執(zhí)行計(jì)劃的最有價(jià)值信息的總結(jié)：?

Table 1.1. SQL Plan Baseline Plan Control Metadata
Attribute	Description
SQL_HANDLE	A unique SQL identifier in?string form; it can be used as a?search key
PLAN_NAME	A unique SQL plan identifier in?string form; it can be used as a?search key
SQL_TEXT	The SQL statement’s?unnormalized, actual text
ORIGIN	Tells if the SQL Plan was either: AUTO-CAPTURE: Automatically captured MANUAL-LOAD: Manually evolved MANUAL-SQLTUNE: Automatically evolved by SQL Tuning Advisor AUTO-SQLTUNE: Automatically evolved by Automatic SQL Tuning
ENABLED	Indicates that the SQL Plan is enabled (YES) for CBO utilization or not (NO). Disabled plans are ignored by the CBO
ACCEPTED	Indicates that the SQL Plan is?validated as a good plan, either because Oracle 11g has: Automatically accepted it, or The DBA has forced its manual acceptance by changing its status to ACCEPTED via procedure DBMS_SPM.ALTER_SQL_PLAN_BASELINE()
FIXED	SQL Plans whose FIXED attribute is set to YES will be considered by the CBO. If multiple plans are marked as FIXED, the CBO will only select the best execution plan from those so marked
OPTIMIZER_COST	The total cost estimated by the CBO to execute the SQL statement using this execution plan

查看已存在SQL Plan Baselines中，對(duì)一條SQL語(yǔ)句執(zhí)行有潛在影響的另一個(gè)方法是通過DBMS_XPLAN的新過程DISPLAY_SQL_PLAN_BASELINE。例如：能用這個(gè)過程來查看SMB中和SQL語(yǔ)句柄匹配的所有SQLPlan Baselines；如果提供了SQL語(yǔ)句的計(jì)劃名，也可以顯示該語(yǔ)句的執(zhí)行計(jì)劃等。

4.?? 自動(dòng)捕獲的實(shí)現(xiàn)和過程

下面，我們分析自動(dòng)捕獲SQL Plan Baselines的過程。首先，我們?cè)O(shè)置OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES參數(shù)為TRUE（默認(rèn)為FALSE）以開啟SQL Plan Baselines自動(dòng)捕獲；我們還需要把OPTIMIZER_USE_SQL_PLAN_BASELINES參數(shù)設(shè)置為TRUE?(默認(rèn)值)。該參數(shù)控制CBO是否檢查SQL語(yǔ)句重復(fù)執(zhí)行產(chǎn)生的計(jì)劃是否將被評(píng)估為一個(gè)好的計(jì)劃。

接著，我們執(zhí)行同樣的一個(gè)SQL兩次。第一次執(zhí)行時(shí)，SQL語(yǔ)句被記錄，第二次執(zhí)行時(shí)，計(jì)劃自動(dòng)被捕獲進(jìn)SMB并被標(biāo)記為該語(yǔ)句ACCEPTED的SQLPlan Baseline。

當(dāng)今后該SQL語(yǔ)句再次被執(zhí)行，并產(chǎn)生了一個(gè)不同的新的計(jì)劃時(shí)，該計(jì)劃也會(huì)被自動(dòng)捕獲進(jìn)SMB，但并不被標(biāo)為ACCEPTED，所以，SPM只會(huì)把第一個(gè)計(jì)劃標(biāo)記為ENABLED和ACCEPTED。

5.??SQL Plan Baseline的演化

SPB中未被標(biāo)示為ACCEPTED的SQL Plan Baselines，需要進(jìn)一步被演化為標(biāo)示ACCEPTED狀態(tài)，才可以被今后再次執(zhí)行的SQL語(yǔ)句采用，對(duì)SPB進(jìn)行演化的方法，主要有如下兩種：

5.1.? 手工方法

??調(diào)用dbms_spm包的evolve_sql_plan_baseline()函數(shù)

SQL> var report clob;

SQL> exec :report := dbms_spm.evolve_sql_plan_baseline();

SQL> print :report

SQL> select sql_text, plan_name, enabled, accepted fromdba_sql_plan_baselines;

??調(diào)用SQL Tuning Advisor工具包

SQL> var tname varchar2(30);

SQL> exec :tname :=dbms_sqltune.create_tuning_task(sql_id => 'bfbr3zrg9d5cc');

SQL> execdbms_sqltune.execute_tuning_task(task_name => :tname);

SQL> selectdbms_sqltune.report_tuning_task(:tname, 'TEXT', 'BASIC') FROM dual;

SQL> exec dbms_sqltune.accept_sql_profile(task_name=> :tname);

SQL> select sql_text, plan_name, enabled,accepted from dba_sql_plan_baselines;

5.2.? 自動(dòng)方法

?? 定期調(diào)度dbms_spm包的evolve_sql_plan_baseline()

?? 配置SQL TUNING ADVISOR，使其在自動(dòng)任務(wù)窗口自動(dòng)運(yùn)行

6.?? 具體操作命令

?? 開啟自動(dòng)捕獲和采用SPM

ALTER SESSION SET optimizer_capture_sql_plan_baselines=TRUE;

ALTER SESSION SET optimizer_use_sql_plan_baselines=TRUE;

?? 查看SPM元數(shù)據(jù)

COL creator???????? FORMAT A08????? HEADING 'Creator' COL hndle?????????? FORMAT A08????? HEADING 'SQL|Handle' COL plnme?????????? FORMAT A08????? HEADING 'Plan|Name' COL sql_hdr???????? FORMAT A25????? HEADING 'SQL Text' WRAP COL origin????????? FORMAT A12????? HEADING 'Origin' COL optimizer_cost? FORMAT 9999999? HEADING 'CBO|Cost' COL enabled???????? FORMAT A04????? HEADING 'Ena-|bled' COL accepted??????? FORMAT A04????? HEADING 'Acpt' COL fixed?????????? FORMAT A04????? HEADING 'Fixd' COL autopurge?????? FORMAT A04????? HEADING 'Auto|Purg' COL create_dt?????? FORMAT A11????? HEADING 'Created|On' WRAP COL lst_exc_dt????? FORMAT A11????? HEADING 'Last|Executed' WRAP SELECT ?????creator ??? ,SUBSTR(sql_handle, -8, 8) hndle ??? ,SUBSTR(plan_name, -8, 8)? plnme ??? ,SUBSTR(sql_text, 1, 75) sql_hdr ??? ,origin ??? ,optimizer_cost ??? ,enabled ??? ,accepted ??? ,fixed ??? ,autopurge ??? ,TO_CHAR(created, 'yyyy-mm-dd hh24:mi:ss') create_dt ??? ,TO_CHAR(last_executed, 'yyyy-mm-dd hh24:mi:ss') lst_exc_dt ? FROM dba_sql_plan_baselines ? WHERE (sql_text LIKE '%SPM%') ORDER BY 1,2,3;

?? 通過DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE顯示已保留的包含特定文本的SQL Plan Baselines

SET LINESIZE 150 SET PAGESIZE 2000 SELECT PT.* ?? FROM (SELECT ?????????????DISTINCT sql_handle ???????????FROM dba_sql_plan_baselines ????????? WHERE sql_text like '%SPM%') SPB, ??????? TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(SPB.sql_handle, NULL,'TYPICAL +NOTE')) PT;

轉(zhuǎn)載于:https://www.cnblogs.com/lhdz_bj/p/8874496.html

總結(jié)

以上是生活随笔為你收集整理的oracle11g中SQL优化（SQL TUNING）新特性之SQL Plan Management(SPM)的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。