知識篇 | ORACLE 如何執行計劃繫結

本文轉摘之如下連結：

正文如下：

遇到執行計劃改變，並且好的執行計劃還在cache中，可以採用以下方法進行執行計劃的繫結：

DECLARE
k1 pls_integer;
begin
k1 := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
sql_id=>'&sql_id',
plan_hash_value=>&plan,
fixed=>'YES',
enabled=>'YES');
end;
/

繫結完成後，把不好的執行計劃從sharepool中刷掉：

select ADDRESS,HASH_VALUE from v$sqlarea where SQL_ID='&sql_id';

把單條SQL從sharepool中刷出去

exec DBMS_SHARED_POOL.PURGE ('&ADDRESS,&HASH_VALUE ','C');

檢視dba_sql_plan_baselines ，確認繫結成功

select * from dba_sql_plan_baselines;

若好的執行計劃並不在cache中，可以採用從AWR中load並繫結執行好的計劃：

1.首先從歷史AWR中找出執行計劃：

SELECT SQL_ID, COUNT (*)
 FROM (SELECT DISTINCT SQL_ID, PLAN_HASH_VALUE FROM DBA_HIST_SQL_PLAN
 WHERE SQL_ID='&SQL_ID')
GROUP BY SQL_ID
ORDER BY 2 DESC;

2.找出 SQL Plan Hash Value

SELECT DISTINCT PLAN_HASH_VALUE,SQL_ID FROM DBA_HIST_SQLSTAT
WHERE SQL_ID='&SQL_ID';

3.選擇snap_id中cost最少最好的plan

SELECT SS.SNAP_ID,
 SS.INSTANCE_NUMBER,
 BEGIN_INTERVAL_TIME,
 SQL_ID,
 PLAN_HASH_VALUE,OPTIMIZER_COST,
 DISK_READS_TOTAL,
 BUFFER_GETS_TOTAL,
 ROWS_PROCESSED_TOTAL,
 CPU_TIME_TOTAL,
 ELAPSED_TIME_TOTAL,
 IOWAIT_TOTAL,
 NVL (EXECUTIONS_DELTA, 0) EXECS,
 ( ELAPSED_TIME_DELTA
 / DECODE (NVL (EXECUTIONS_DELTA, 0), 0, 1, EXECUTIONS_DELTA))
 / 1000000
 AVG_ETIME,
 ( BUFFER_GETS_DELTA
 / DECODE (NVL (BUFFER_GETS_DELTA, 0), 0, 1, EXECUTIONS_DELTA))
 AVG_LIO
FROM DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
WHERE SQL_ID = '&SQL_ID'
 AND SS.SNAP_ID = S.SNAP_ID
 AND SS.INSTANCE_NUMBER = S.INSTANCE_NUMBER
 AND EXECUTIONS_DELTA > 0
ORDER BY 1, 2, 3;

4.建立 STS

BEGIN
 DBMS_SQLTUNE.CREATE_SQLSET(
 sqlset_name => 'STS_xxxx',
 description => 'SQL Tuning Set for loading plan into SQL Plan Baseline');
END;
/
==xxx為SQL_ID==

5.從snap_id中Load STS

DECLARE
 cur sys_refcursor;
BEGIN
 OPEN cur FOR
 SELECT VALUE(P)
 FROM TABLE(
 dbms_sqltune.select_workload_repository(begin_snap=>6819, end_snap=>6820,basic_filter=>'sql_id =''&sql_id''',attribute_list=>'ALL')) p;
 DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'STS_xxxx', populate_cursor=>cur);
 CLOSE cur;
END;
/

6.檢視loaded Plan 是否成功

SELECT
 first_load_time ,
 executions as execs ,
 parsing_schema_name ,
 elapsed_time / 1000000 as elapsed_time_secs ,
 cpu_time / 1000000 as cpu_time_secs ,
 buffer_gets ,
 disk_reads ,
 direct_writes ,
 rows_processed ,
 fetches ,
 optimizer_cost ,
 sql_plan ,
 plan_hash_value ,
 sql_id ,
 sql_text
 FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => 'STS_xxxx')
 );複製

7.繫結從AWR中load的執行計劃

DECLARE
my_plans pls_integer;
BEGIN
 my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
 sqlset_name => 'STS_xxxx',
 basic_filter=>'plan_hash_value = ''xxxx'''
 );
END;
/
==xxxx為最好的plan_hash_value==

8.檢視dba_sql_plan_baselines ，確認繫結成功
SELECT * FROM dba_sql_plan_baselines ;

9.繫結完成後，把不好的執行計劃從sharepool中刷掉：

select ADDRESS,HASH_VALUE from v$sqlarea where SQL_ID='&sql_id';

把單條SQL從sharepool中刷出去

exec DBMS_SHARED_POOL.PURGE ('&ADDRESS,&HASH_VALUE ','C');

有時會遇到從cache中以及AWR中並不能成功LOAD出執行計劃，12.2以上可以採用以下方法LOAD並繫結執行計劃：

variable x number
begin
:x := dbms_spm.load_plans_from_awr( begin_snap=>6785,end_snap=>6953,basic_filter=>q'# sql_id='&SQL_ID' and plan_hash_value='xxxxx' #' );
end;
/
print x
==xxxx為最好的plan_hash_value==

若print

x
--
1

則表示成功。可以檢視dba_sql_plan_baselines ，確認繫結成功

SELECT * FROM dba_sql_plan_baselines ;

隨後，把不好的執行計劃從sharepool中刷掉：

select ADDRESS,HASH_VALUE from v$sqlarea where SQL_ID='&sql_id';

把單條SQL從sharepool中刷出去

exec DBMS_SHARED_POOL.PURGE ('&ADDRESS,&HASH_VALUE ','C');

透過SPM為SQL語句加HINT，繫結執行計劃

若SQL並不存在好的執行計劃
透過DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE在不修改原SQL的情況下對其加HINT來固定好的執行計劃。

原SQL走索引

SELECT * FROM SCOTT.TB_SPM WHERE OBJECT_ID=10;

透過加HINT讓其走全表掃描

SELECT /*+FULL(TB_SPM)*/* FROM SCOTT.TB_SPM WHERE OBJECT_ID=10;

在V$SQL中查詢出，原SQL的SQL_ID=064qcdmgt6thw，加HINT的SQL的SQL_ID=ahdtbgvsd3bht，PLAN_HASH_VALUE=970476072

執行以下：

DECLARE
  CNT   NUMBER;
  V_SQL CLOB;
BEGIN
  --得到原語句SQL文字
  SELECT SQL_FULLTEXT INTO V_SQL FROM V$SQL WHERE SQL_ID = '&SQL_ID' AND ROWNUM=1;
  --用加HINT的SQL的SQL_ID和PLAN_HASH_VALUE，來固定原語句的SQL
  CNT := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID          => '&SQL_ID',
                                               PLAN_HASH_VALUE => &plan,
                                               SQL_TEXT        => V_SQL);
END;

這樣就將加HINT的執行計劃固定在原語句上。
執行原語句，在V$SQL的PLAN_HASH_VALUE列和SQL_PLAN_BASELINE列來確認是否固定。

一些含有繫結變數的SQL，用常量的SQL的SQL_ID和PLAN_HASH_VALUE無法固定，此時可以嘗試使用EXECUTE IMMEDIATE來生成含有繫結變數的SQL。

DECLARE
  V_SQL VARCHAR2(3000);
BEGIN
  V_SQL := 'SELECT /*+FULL(TB_SPM)*/* FROM SCOTT.TB_SPM WHERE OBJECT_ID=:1';
  EXECUTE IMMEDIATE V_SQL
    USING 10;
END;
或
var v number;
exec :v :=10
SELECT /*+FULL(TB_SPM)*/* FROM SCOTT.TB_SPM WHERE OBJECT_ID=:V;