溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
本篇文章為大家展示了Oracle 11g中SPM指的是什么，內(nèi)容簡(jiǎn)明扼要并且容易理解，絕對(duì)能使你眼前一亮，通過這篇文章的詳細(xì)介紹希望你能有所收獲。
前言
我們知道,SQL語句的性能在很大程度上依賴于SQL語句的執(zhí)行計(jì)劃,如果SQL語句的執(zhí)行計(jì)劃發(fā)生變化,那么SQL語句的性能有可能發(fā)生很大的變化。影響SQL語句執(zhí)行計(jì)劃的因素很多,常見的有:
?優(yōu)化器版本的變化
?統(tǒng)計(jì)信息的變化
?優(yōu)化器相關(guān)的各種參數(shù)的變化
?對(duì)象定義的修改
?添加、刪除相關(guān)索引
?修改了系統(tǒng)的設(shè)置
?綁定變量的"窺視"功能
?綁定變量的定義發(fā)生變化（比如類型或長(zhǎng)度有變化）
?啟用了outline或SQL Profile等設(shè)置
11g前我們的處理方式
在Oracle 11g前，我們可以借助存儲(chǔ)大綱(Stored Outline)和SQL Profile來幫助我們固定某個(gè)SQL語句的執(zhí)行計(jì)劃，防止由于執(zhí)行計(jì)劃的改變從而導(dǎo)致SQL性能的衰退。不過這些技術(shù)需要DBA人為的處理，比如存儲(chǔ)大綱需要DBA手工創(chuàng)建，而對(duì)10g中提供的SQL Profile來說，則需要DBA手工應(yīng)用才可以生效。
11g中，Oracle 提供了SPM
SPM就是Sql Plan Management,是Oracle自我管理（或者說自動(dòng)性）發(fā)展的新功能。通過這個(gè)特性，Oracle自動(dòng)去判斷某個(gè)SQL的新的執(zhí)行計(jì)劃是否更加合理（成本更低），只有在新的執(zhí)行計(jì)劃比原來的執(zhí)行計(jì)劃更好的情況下，它才會(huì)被使用，從而有效的保護(hù)了執(zhí)行計(jì)劃的穩(wěn)定性，進(jìn)而保證了SQL語句的執(zhí)行效率。
關(guān)于SPM的工作原理,本文不做詳細(xì)的介紹,有興趣的同志可以參閱相關(guān)的資料(網(wǎng)上類似的文章也不少)
執(zhí)行計(jì)劃管理實(shí)例測(cè)試
測(cè)試一:自動(dòng)捕獲的場(chǎng)景
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Connected as study
SQL> create table test as select * from dba_objects;
Table created
SQL> exec DBMS_STATS.gather_table_stats(USER, 'TEST', cascade=>TRUE);
PL/SQL procedure successfully completed
SQL> select object_name from test where object_id=100;
OBJECT_NAME
----------------
FIXED_OBJ$
SQL> select object_name from test where object_id=100;
OBJECT_NAME
----------------
FIXED_OBJ$
盡管上面的查詢語句執(zhí)行了2次,但這個(gè)時(shí)候去查詢dba_sql_plan_baseline的時(shí)候會(huì)發(fā)現(xiàn),找不到我們需要的記錄,因?yàn)檫@個(gè)時(shí)候optimizer_capture_sql_plan_baselines設(shè)置為false.禁止了自動(dòng)捕獲的功能.下面我將該參數(shù)設(shè)置為true,繼續(xù)測(cè)試看看:
SQL> alter session set optimizer_capture_sql_plan_baselines=true;
Session altered
SQL> select object_name from test where object_id=100;
OBJECT_NAME
--------------------
FIXED_OBJ$
SQL> select object_name from test where object_id=100;
OBJECT_NAME
--------------------
FIXED_OBJ$
SQL> select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines;
SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED AUTOPURGE
---------- --------------------------- ------------------------------ -------------- ------- -------- ---------
4.32736134 SYS_SQL_3c0de038050ab8f1 SYS_SQL_PLAN_050ab8f197bbe3d0 AUTO-CAPTURE YES YES YES
這個(gè)時(shí)候,我們?cè)偃ゲ樵僤ba_sql_plan_baseline的時(shí)候,就會(huì)發(fā)現(xiàn)該語句在plan history中記錄了一個(gè)執(zhí)行計(jì)劃.
幾個(gè)關(guān)鍵字段的說明
sql_handle：表示SQL語句的句柄
plan_name： 表示該SQL語句執(zhí)行計(jì)劃的名字
origin： 表示該執(zhí)行計(jì)劃是如何進(jìn)入plan history的。AUTO-CAPTURE：優(yōu)化器自動(dòng)加入；MANUAL：DBA手工加入
enabled： 表示是否被啟用了。YES：?jiǎn)⒂?NO：禁用。如果某個(gè)執(zhí)行計(jì)劃為禁用，則優(yōu)化器根本就不會(huì)考慮使用該執(zhí)行計(jì)劃
accepted： 表示是否接受，也就是是否進(jìn)入了plan baseline里，YES表示接受，NO表示不接受
autopurge： 表示是否為定期自動(dòng)刪除,YES表示是，NO表示否。
fixed： 當(dāng)plan baseline中有多個(gè)執(zhí)行計(jì)劃時(shí)，表示優(yōu)化器僅考慮這些被設(shè)置為FIXED的計(jì)劃，從中選擇一個(gè)最優(yōu)的。
繼續(xù)測(cè)試的旅程，新加一個(gè)index，再執(zhí)行上面的SQL語句：
SQL> create index inx_test_object_id on test(object_id);
Index created
SQL> exec DBMS_STATS.gather_table_stats(USER, 'TEST', cascade=>TRUE);
PL/SQL procedure successfully completed
SQL> select object_name from test where object_id=100;
OBJECT_NAME
-----------------
FIXED_OBJ$
SQL> select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines;
SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED AUTOPURGE
---------- -------------------------- ------------------------------ ------------- ------- -------- ---------
4.32736134 SYS_SQL_3c0de038050ab8f1 SYS_SQL_PLAN_050ab8f127b7cc01 AUTO-CAPTURE YES NO YES
4.32736134 SYS_SQL_3c0de038050ab8f1 SYS_SQL_PLAN_050ab8f197bbe3d0 AUTO-CAPTURE YES YES YES
SQL>
可以看到，dba_sql_plan_baselines視圖里多了一個(gè)執(zhí)行計(jì)劃（上面灰色的），不過該執(zhí)行計(jì)劃的accepted為NO，表示還沒有進(jìn)入到plan baseline里，而是進(jìn)入了plan history中。
如果想讓該計(jì)劃進(jìn)入到plan baseline中，我們可以借助dbms_spm來完成：
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 Connected as study SQL> SQL> set serveroutput on long 100000 SQL> declare 2 report clob; 3 begin 4 report := dbms_spm.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_3c0de038050ab8f1', 5 plan_name => 'SYS_SQL_PLAN_050ab8f127b7cc01'); 6 dbms_output.put_line(report); 7 end; 8 / ------------------------------------------------------------------------------- Evolve SQL Plan Baseline Report ------------------------------------------------------------------------------- Inputs: ------- SQL_HANDLE = SYS_SQL_3c0de038050ab8f1 PLAN_NAME = SYS_SQL_PLAN_050ab8f127b7cc01 TIME_LIMIT = DBMS_SPM.AUTO_LIMIT VERIFY = YES COMMIT = YES Plan: SYS_SQL_PLAN_050ab8f127b7cc01 ----------------------------------- Plan was verified: Time used .062 seconds. Passed performance criterion: Compound improvement ratio >= 337.17. Plan was changed to an accepted plan. Baseline Plan Test Plan Improv. Ratio ------------- --------- ------------- Execution Status: COMPLETE COMPLETE Rows Processed: 1 1 Elapsed Time(ms): 13 0 CPU Time(ms): 15 0 Buffer Gets: 1010 3 336.67 Disk Reads: 0 0 Direct Writes: 0 0 Fetches: 0 0 Executions: 1 1 ------------------------------------------------------------------------------- Report Summary ------------------------------------------------------------------------------- Number of SQL plan baselines verified: 1. Number of SQL plan baselines evolved: 1. PL/SQL procedure successfully completed SQL>
再看一下dba_sql_plan_baselines
SQL> select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines;
SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED AUTOPURGE
---------- -------------------------- ------------------------------ ------------- ------- -------- ---------
4.32736134 SYS_SQL_3c0de038050ab8f1 SYS_SQL_PLAN_050ab8f197bbe3d0 AUTO-CAPTURE YES YES YES
4.32736134 SYS_SQL_3c0de038050ab8f1 SYS_SQL_PLAN_050ab8f127b7cc01 AUTO-CAPTURE YES YES YES
SQL>
咱們分別看一下這兩個(gè)執(zhí)行計(jì)劃的詳細(xì)信息：
SQL> select * from table(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SYS_SQL_PLAN_050ab8f127b7cc01')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL handle: SYS_SQL_3c0de038050ab8f1 SQL text: select object_name from test where object_id=100 -------------------------------------------------------------------------------- Plan name: SYS_SQL_PLAN_050ab8f127b7cc01 Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE -------------------------------------------------------------------------------- Plan hash value: 2422726699 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 30 | 2 | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 30 | 2 |* 2 | INDEX RANGE SCAN | INX_TEST_OBJECT_ID | 1 | | 1 -------------------------------------------------------------------------------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=100) 25 rows selected SQL> SQL> select * from table(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SYS_SQL_PLAN_050ab8f197bbe3d0')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- SQL handle: SYS_SQL_3c0de038050ab8f1 SQL text: select object_name from test where object_id=100 -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Plan name: SYS_SQL_PLAN_050ab8f197bbe3d0 Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE -------------------------------------------------------------------------------- Plan hash value: 1357081020 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 30 | 282 (1)| 00:00:04 | |* 1 | TABLE ACCESS FULL| TEST | 1 | 30 | 282 (1)| 00:00:04 | -------------------------------------------------------------------------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID"=100) 24 rows selected SQL>
在看看這個(gè)SQL執(zhí)行時(shí)到底選擇了那個(gè)baseline:
SQL> conn study/study 已連接。 SQL> set autotrace trace SQL> select object_name from test where object_id=100; 執(zhí)行計(jì)劃 ---------------------------------------------------------- Plan hash value: 2422726699 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 30 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 30 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | INX_TEST_OBJECT_ID | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=100) Note ----- - SQL plan baseline "SYS_SQL_PLAN_050ab8f127b7cc01" used for this statement SQL>
下面再熟悉一下手工刪除plan baseline里的執(zhí)行計(jì)劃，同樣可以借助dbms_spm來完成。下面的DEMO把上面baseline里走full table scan的執(zhí)行計(jì)劃清除：
SQL> var ret number
SQL> exec :ret:=dbms_spm.drop_sql_plan_baseline(sql_handle => 'SYS_SQL_3c0de038050ab8f1',
plan_name => 'SYS_SQL_PLAN_050ab8f197bbe3d0');
PL/SQL procedure successfully completed
ret
---------
1
SQL>
SQL> select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines;
SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED AUTOPURGE
---------- ------------------------ ------------------------------ -------------- ------- -------- ---------
4.32736134 SYS_SQL_3c0de038050ab8f1 SYS_SQL_PLAN_050ab8f127b7cc01 AUTO-CAPTURE YES YES YES
SQL>
測(cè)試二:手動(dòng)捕獲的場(chǎng)景
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Connected as study
SQL> select sql_text,sql_id from v$sql where sql_text like '%select object_name from test where%';
SQL_TEXT SQL_ID
----------------------------------------------------- -------------
select object_name from test where object_id=100 7j7jc706upva2
SQL>
SQL> set serveroutput on
SQL> declare
2 l_plans_loaded PLS_INTEGER;
3 begin
4 l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
5 sql_id => '7j7jc706upva2');
6
7 DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
8 END;
9 /
Plans Loaded: 1
PL/SQL procedure successfully completed
SQL>
SQL> select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge
2 from dba_sql_plan_baselines;
SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED AUTOPURGE
---------- ------------------------- ------------------------------ ----------- ------- -------- ---------
4.32736134 SYS_SQL_3c0de038050ab8f1 SYS_SQL_PLAN_050ab8f127b7cc01 MANUAL-LOAD YES YES YES
SQL>上述內(nèi)容就是Oracle 11g中SPM指的是什么，你們學(xué)到知識(shí)或技能了嗎？如果還想學(xué)到更多技能或者豐富自己的知識(shí)儲(chǔ)備，歡迎關(guān)注億速云行業(yè)資訊頻道。
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼