ORACLE 执行计划绑定

原创张鹏远 2021-09-28

8457

遇到执行计划改变，并且好的执行计划还在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;

复制