Oracle-繫結執行計劃

綠茶有點甜發表於2021-10-19
Oracle

一、繫結執行計劃

Oracle存在某個SQL多個執行計劃的情況，那麼如何快速將Oracle 好的執行計劃，繫結到不好的SQL上呢？

由於版本的演進，繫結執行計劃在10g 一般使用sql profile; >=11g之後，大部分使用spm就可以了。

本篇文件針對這兩種繫結執行計劃的方式進行了梳理。 有需要的小夥伴們可以直接使用。

 

二、實驗測試

2.1 SPM繫結

--好的執行計劃，查詢SQL ID
SQL> select sql_text ,sql_id from v$sql where sql_text like '%2021_10_19_test_sql_text%';
5yv7w368z62bz
--查詢好的執行計劃SQL 對應的hash value
select * from table(dbms_xplan.display_awr('&sql',format=>'PEEKED_BINDS'));
select * from table(dbms_xplan.display_cursor('&sql'));


--繫結執行計劃
declare
m_clob clob;
begin
select sql_fullteXt
into m_clob
from v$sql
where sql_id = 'bcq5f5sd2k5wu' --需要繫結的SQL ID
and child_number = 0; --需要繫結的SQL ID對應的子游標編號
dbms_output.put_line(m_clob);
dbms_output.put_line(dbms_spm.load_plans_from_cursor_cache(sql_id => '5yv7w368z62bz', --參考需要的執行計劃SQL ID  及好的執行計劃對應的SQL ID
plan_hash_value => 3270942279, --參考需要的執行計劃 Hash value
sql_text => m_clob,
fixed => 'YES',
enabled => 'YES'));



end;
/


 


--如下實際執行
declare
  m_clob clob;
begin
  select sql_fullteXt
    into m_clob
    from v$sql
   where sql_id = 'bcq5f5sd2k5wu'
     and child_number = 0;
  dbms_output.put_line(m_clob);
  dbms_output.put_line(dbms_spm.load_plans_from_cursor_cache(sql_id          => '5yv7w368z62bz',
                                                             plan_hash_value => 3270942279,
                                                             sql_text        => m_clob,
                                                             fixed           => 'YES',
                                                             enabled         => 'YES'));


end;
/


 


查詢是否繫結


select sql_handle, plan_name, accepted, fixed,optimizer_cost from dba_sql_plan_baselines ;


SQL_HANDLE PLAN_NAME ACC FIX OPTIMIZER_COST
------------------------------ ------------------------------ --- --- --------------
SQL_916244ba197a1647           SQL_PLAN_92sk4r8crn5k7f0218608 YES YES              1


 


刪除SPM繫結的執行計劃


 declare 
 xx PLS_INTEGER; 
 BEGIN 
 xx :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_916244ba197a1647',plan_name=>null); 
 END; 
 /


檢查執行計劃是否生效，手工可以執行的情況可以看到如下！


exec sql


select * from table(dbms_xplan.display_cursor(null,null,'advanced -PROJECTION -bytes iostats,last'));


- SQL plan baseline SQL_PLAN_92sk4r8crn5k7f0218608 used for this statement





 


2.2 SQL Profile繫結




使用sql profile繫結執行計劃--參考
declare
 ar_profile_hints sys.sqlprof_attr;
 clsql_text CLOB;
begin
select extractvalue(value(d), '/hint') as outline_hints bulk collect
 into ar_profile_hints
 from xmltable('/*/outline_data/hint' passing
 (select xmltype(other_xml) as xmlval
 from dba_hist_sql_plan
 where sql_id = 'SQLID 值'   --好的執行計劃對應的SQL ID
 and plan_hash_value = value 值  --好的執行計劃對應SQL ID 的hash value值
 and other_xml is not null)) d;
 SELECT sql_text INTO clsql_text
 FROM dba_hist_sqltext
 where sql_id = ' SQLID 值';   --需要繫結的SQL ID的值,通過檢視查詢該SQL 對應的文字
 DBMS_SQLTUNE.IMPORT_SQL_PROFILE(sql_text => clsql_text,
 profile => ar_profile_hints,
 name => 'PROFILE_ SQLID 值',  --SQL Profile繫結的標記
 force_match => TRUE,——true 表示對於謂詞部分 具體值變化後的 SQL_ID 也能使用該 SQL profile
 REPLACE => TRUE);
end;
/




declare
 ar_profile_hints sys.sqlprof_attr;
 clsql_text CLOB;
begin
select extractvalue(value(d), '/hint') as outline_hints bulk collect
 into ar_profile_hints
 from xmltable('/*/outline_data/hint' passing
 (select xmltype(other_xml) as xmlval
 from dba_hist_sql_plan
 where sql_id = '5yv7w368z62bz'
 and plan_hash_value = '3270942279'
 and other_xml is not null)) d;
 SELECT sql_text INTO clsql_text
 FROM dba_hist_sqltext
 where sql_id = 'bcq5f5sd2k5wu';
 DBMS_SQLTUNE.IMPORT_SQL_PROFILE(sql_text => clsql_text,
 profile => ar_profile_hints,
 name => 'PROFILE_ SQLID bcq5f5sd2k5wu',
 force_match => TRUE,
 REPLACE => TRUE);
end;
/
-----------------------------------------好的SQL執行計劃，可能並不在歷史的檢視中記錄，可以換成如下在memory cache的檢視
select xmltype(other_xml) as xmlval
 from v$sql_plan                       --dba_hist_sql_plan檢視找不到記錄！
 where sql_id = '5yv7w368z62bz'
 and plan_hash_value = '3270942279'
 and other_xml is not null
--
declare
 ar_profile_hints sys.sqlprof_attr;
 clsql_text CLOB;
begin
select extractvalue(value(d), '/hint') as outline_hints bulk collect
 into ar_profile_hints
 from xmltable('/*/outline_data/hint' passing
 (select xmltype(other_xml) as xmlval
 from v$sql_plan
 where sql_id = '5yv7w368z62bz'
 and plan_hash_value = '3270942279'
 and other_xml is not null)) d;
 SELECT sql_text INTO clsql_text
 FROM dba_hist_sqltext
 where sql_id = 'bcq5f5sd2k5wu';
 DBMS_SQLTUNE.IMPORT_SQL_PROFILE(sql_text => clsql_text,
 profile => ar_profile_hints,
 name => 'PROFILE_ SQLID bcq5f5sd2k5wu',
 force_match => TRUE,
 REPLACE => TRUE);
end;
/

--檢查是否繫結成功
SQL> select * from dba_sql_profiles WHERE name ='PROFILE_ SQLID bcq5f5sd2k5wu';
--SQL 能執行的情況下


- SQL profile PROFILE_ SQLID bcq5f5sd2k5wu used for this statement


 


--刪除SQL Profile


BEGIN
 DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'PROFILE_ SQLID bcq5f5sd2k5wu');
END;
/


 


 


--驗證SQL執行效率對比，可以檢查SQL的執行效率



select sql_id,sql_profile，
 executions,plan_hash_value,
 elapsed_time / DECODE(executions, 0, 1, EXECUTIONS) / 1000 elasp_time_ms,
 buffer_gets / DECODE(executions, 0, 1, EXECUTIONS),
 disk_reads / DECODE(executions, 0, 1, EXECUTIONS),
 cpu_time / DECODE(executions, 0, 1, EXECUTIONS)/1000 cpu_time_ms,
 last_load_time,last_active_time,
 sql_text,child_number
from v$sql
where SQL_ID IN ('&sql_id');
select *
 from (select to_char(begin_interval_time,'yyyy-mm-dd hh24:mi:ss') begin_time, 
 a.instance_number,
 module,
 plan_hash_value,
 EXECUTIONS_DELTA exec,
 decode(EXECUTIONS_DELTA,
 0,
 buffer_gets_deltA,
 round(BUFFER_GETS_DELTA / EXECUTIONS_DELTA)) per_get,
 decode(EXECUTIONS_DELTA,
 0,
 ROWS_PROCESSED_DELTA,
 round(ROWS_PROCESSED_DELTA / EXECUTIONS_DELTA, 3)) 
per_rows,
 decode(EXECUTIONS_DELTA,
 0,
 ELAPSED_TIME_DELTA,
 round(ELAPSED_TIME_DELTA / EXECUTIONS_DELTA / 1000,
 2)) time_ms,
 decode(EXECUTIONS_DELTA,
 0,
 DISK_READS_DELTA,
 round(DISK_READS_DELTA / EXECUTIONS_DELTA, 2)) per_read
 from dba_hist_sqlstat a, DBA_HIST_SNAPSHOT b
 where a.snap_id = b.snap_id
 and a.instance_number = b.instance_number
 and a.sql_id = '&sql_id'
 order by 1 desc)
where rownum < 100;


 


SPM非參考，SQL PROFILE ,SQL查詢效率部分參考文件https://www.modb.pro/doc/287




 






相關文章