遇到执行计划改变,并且好的执行计划还在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;
ORACLE 如何执行计划绑定
未经允许不得转载:徐万新之路 » ORACLE 如何执行计划绑定
相关推荐
- ORACLE 临时表空间使用率过高的原因及解决方案(转)
- EBS s_oc4j_sesstimeout配置和ICX:会话超时时间
- EBS Cloning 更改颜色Java 色彩设计
- cursors.sql
- ORA-20005: Object Statistics Are Locked (stattype = ALL) ORA-06512: At "APPS.FND_STATS"
- Oracle EBS 主要模块中文名称及英文缩写
- How To Download And Install The Latest OPatch(6880880) Version (Doc ID 274526.1)
- Oracle 11g备份与恢复
最新评论
这个牛
放下欲望,男人从来不醉,充分且必要
勇气、责任、自信、创新,为天下先!
软件即数据,软件即服务,软件即管理,软件就是对人类各种社会活动的仿真和记录。软件很重要,但软件不可能凌驾于业务之上,尤其不可能高人一等。