SQL BASELINE修改固定執行計劃
http://www.itpub.net/viewthread.php?tid=1445246
打算把固定執行計劃的方法做個整理和比較。上面的連結是SQL PROFILE，SQL OUTLINE的用法。這篇說下BASELINE的用法。
目的：讓執行計劃走上全表掃描
查詢語句：select count(*) from wxh_tbd where object_id=:a
SQL_ID:85f05qy1aq0dr
PLAN_HASH_VALUE：1501268522
步驟一-------------------------建立測試表，根據DBA_OBJECTS建立，OBJECT_ID上有索引
Create table wxh_tbd as select * from dba_objects;
create index t_3 on wxh_tbd(object_id);
步驟二------------------------建立指定SQLID的BASELINE,後面要做修改，由於預設走的索引
declare
l_pls number;
begin
l_pls := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '85f05qy1aq0dr',
plan_hash_value => 1501268522,
enabled => 'NO');
end;
/
步驟三--------------------------想辦法構造出執行計劃為全表掃描的SQL_ID
var a number
exec :a :=1234
select /*+ full(wxh_tbd) */count(*) from wxh_tbd where object_id=:a;
select sql_id ,sql_text from v$sql where sql_text like '%wxh_tbd%';
查出SQL_ID為89143jku5hzcw，PLAN_HASH_VALUE為853361775
步驟四--------------------------確定原始執行計劃的 sql_handle
select sql_handle, plan_name, origin, enabled, accepted,fixed,optimizer_cost,sql_text
from dba_sql_plan_baselines where sql_text like '%count(*) from wxh_tbd %'
order by last_modified;
SQL_HANDLE:SYS_SQL_ad9f0ff741832bd9
PLAN_NAME:SYS_SQL_PLAN_41832bd9d63f8aa9
步驟五------------------------與正確的執行計劃做關聯
declare l_pls number;
begin
l_pls := DBMS_SPM.load_plans_from_cursor_cache(sql_id => '89143jku5hzcw', -- hinted_SQL_ID'
plan_hash_value => 853361775, --hinted_plan_hash_value
sql_handle => 'SYS_SQL_ad9f0ff741832bd9' --sql_handle_for_original
);
end;
/
步驟六--------------------------刪除錯誤的執行計劃
declare l_pls number;
begin
l_pls := DBMS_SPM.DROP_SQL_PLAN_BASELINE(sql_handle => 'SYS_SQL_ad9f0ff741832bd9', --sql_handle_for_original
plan_name => 'SYS_SQL_PLAN_41832bd9d63f8aa9 ' --sql_plan_name_for_original
);
end;
/
步驟七----------------------確認是否使用到BASELINE
explain plan for select count(*) from wxh_tbd where object_id=:a;
select * from table(dbms_xplan.display);
--------------------------------------
| Id | Operation | Name |
--------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
|* 2 | TABLE ACCESS FULL| WXH_TBD |
--------------------------------------
Note
-----
- SQL plan baseline "SYS_SQL_PLAN_41832bd9cca3d082" used for this statement
Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE
1）FUNCTION LOAD_PLANS_FROM_CURSOR_CACHE RETURNS BINARY_INTEGER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE NUMBER IN DEFAULT
FIXED VARCHAR2 IN DEFAULT
ENABLED VARCHAR2 IN DEFAULT
2）FUNCTION LOAD_PLANS_FROM_CURSOR_CACHE RETURNS BINARY_INTEGER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE NUMBER IN DEFAULT
SQL_TEXT CLOB IN
FIXED VARCHAR2 IN DEFAULT
ENABLED VARCHAR2 IN DEFAULT
3）FUNCTION LOAD_PLANS_FROM_CURSOR_CACHE RETURNS BINARY_INTEGER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE NUMBER IN DEFAULT
SQL_HANDLE VARCHAR2 IN
FIXED VARCHAR2 IN DEFAULT
ENABLED VARCHAR2 IN DEFAULT
DBMS_SPM包裡有三個同名的LOAD_PLANS_FROM_CURSOR_CACHE函式，作用各不同。第一個函式用於直接對特定sql_id、plan_hash_value的共享池物件建立baseline。一般用於當前sql執行計劃已經是正確的，只是為了透過baseline來進一步固定。也可以用於sql對應多個執行計劃，只有一個執行計劃是需要透過baseline來穩固的情況。這種情況可以根據plan_hash_value來確定期望的執行計劃。
第二個函式一般用於修正執行計劃，當然也能實現第一個函式的功能。修正執行計劃的情況，sql_id,plan_hash_value要是新增過hint的sql的，不是原始sql的。sql_text是原始sql的。這個函式是我建議在修正執行計劃的時候使用的，不推薦使用後面將要介紹的第三種。
第三個函式一般用於修正執行計劃，也能夠實現第一個函式的功能。修正執行計劃的情況，sql_id,plan_hash_value要是新增過hint的sql的，不是原始sql的。SQL_HANDLE是依據原始sql建立的baseline的SQL_HANDLE。
上面三個函式的FIXED，ENABLED統一都設定成YES.FIXED為YES,代表我們建立的baseline禁止演化，演化的意思是，當ORACLE發現一個比目前執行計劃更高效的執行計劃時，自動建立一個不可接受狀態的baseline.ENABLED的意思是，讓baseline立即起效。
Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE我們以如下查詢為例。object_id列上存在索引。查詢預設的執行計劃走了 object_id列上的索引。|
sql 文字 |
select count(*) from wxh_tbd where object_id=:a |
|
sql_id |
85f05qy1aq0dr |
|
plan_hash_value |
1501268522 |
我們可能對於這個查詢計劃的固化有兩種需求：
1）想繼續用走索引的執行計劃，為確保執行計劃不走錯，透過baseline來固化執行計劃。步驟如下：
declare
l_pls number;
begin
l_pls := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id=> ' bmrc3akp0v4uh ',
plan_hash_value
=> 1501268522,
enabled
=> 'YES');
end;
/
2)不想用走索引的執行計劃，想讓執行計劃走全表掃描。可以透過如下方式操作：
步驟一：透過新增HINT，構造出需要的執行計劃。 需要注意的是，這一步的意義是需要在共享池裡產生出正確的執行計劃，後面需要跟它做關聯，需要確保這個執行計劃不要被重新整理出共享池。否則第二步的關聯會無效。
select /*+ full(wxh_tbd)
*/count(*) from wxh_tbd where object_id=1;
查詢v$sql獲得這個sql的sql_id和plan_hash_value
|
sql文字 |
select /*+ full(wxh_tbd) */count(*) from wxh_tbd where object_id=:a |
|
sql_id |
bmrc3akp0v4uh |
|
plan_hash_value |
853361775 |
步驟二：依據原始sql文字與正確的執行計劃做關聯。關聯前最好再執行一下，步驟一里新增過HINT的sql語句，以免執行計劃已經被刷出共享池。
declare
m_clob clob;
begin
select sql_fulltext
into m_clob
from v$sql
where sql_id = '9jnx2cjukjtu7'--------------原始sql的 sql_id
and child_number = 0;-----------------為了讓sql只返回一行，也可以rownum=1代替
dbms_output.put_line(m_clob);
dbms_output.put_line(dbms_spm.load_plans_from_cursor_cache(
sql_id => 'bmrc3akp0v4uh',------------HINT SQL_ID
plan_hash_value => 853361775,-------------------HINT PLAN_HASH_VALUE
sql_text => m_clob,-------------------------原始SQL文字
fixed => 'YES', ---------------------禁止演化baseline
enabled => 'YES'));
end;
/
1. 驗證方案
另開一個SESSION，確定已經用到了baseline。
explain plan for select count(*) from wxh_tbd where object_id=:a;
select * from table(dbms_xplan.display);
--------------------------------------
| Id | Operation | Name |
--------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
|* 2 | TABLE ACCESS FULL| WXH_TBD |
--------------------------------------
Note
-----
- SQL plan baseline "SYS_SQL_PLAN_41832bd9cca3d082" used for this statement
執行計劃的note部分顯示已經用到了baseline ，執行計劃也由索引改為了全表掃描。
相關文章
- baseline固定SQL執行計劃SQL
- SQL PROFILE修改固定執行計劃SQL
- zt_sql baseline_sql profile_sql outline修改固定執行計劃SQL
- 使用sql profile固定執行計劃SQL
- 用outline修改固定執行計劃
- 用sql profile來固定執行計劃SQL
- Oracle手動固定SQL執行計劃OracleSQL
- Oracle SQL Profile固定執行計劃的方法OracleSQL
- oracle 固定執行計劃Oracle
- 使用coe_xfr_sql_profile固定執行計劃SQL
- oracle固定執行計劃--sqlprofileOracleSQL
- .Oracle固定執行計劃之SQL PROFILE概要檔案OracleSQL
- oracle sqlprofile 固定執行計劃，並遷移執行計劃OracleSQL
- zt_oracle11g sql baseline與sql execution plan執行計劃OracleSQL
- 使用SQL Profile及SQL Tuning Advisor固定執行計劃SQL
- 【SPM】Oracle如何固定執行計劃Oracle
- 影響執行計劃之oracle sql baseline與sql profile之互動OracleSQL
- 控制執行計劃之-SPM BASELINE（六）
- 控制執行計劃之-SPM BASELINE（五）
- 控制執行計劃之-SPM BASELINE（四）
- 控制執行計劃之-SPM BASELINE（三）
- 控制執行計劃之-SPM BASELINE（二）
- 控制執行計劃之-SPM BASELINE（一）
- 使用OUTLINE固定執行計劃
- sql 執行計劃SQL
- 使用SPM和STA進行固定執行計劃
- Oracle緊急固定執行計劃之手段Oracle
- Oracle sql執行計劃OracleSQL
- SQL Server執行計劃SQLServer
- SQL的執行計劃SQL
- SQL執行計劃分析SQL
- 【sql調優之執行計劃】獲取執行計劃SQL
- 修改Process Chain的執行計劃AI
- oracle使用outline固定執行計劃事例Oracle
- 透過SPM手動新增執行計劃到baseLine
- 獲取SQL執行計劃SQL
- SQL 執行計劃案例1SQL
- 剖析SQL Server執行計劃SQLServer