跟大家说明一下:
ITSM 数据库迁移升级到11G后,有几条SQL语句的执行计划不正确,而且这些语句都是使用绑定变量的。
最初的调整想法是获得这些的语句的绑定变量值,将获得的字面值直接替换SQL语句的绑定变量,调整该SQL到正确的执行计划后执行,取得正确的执行计划并导入SPM。然而实际调整时发现,使用字面量获得的执行计划虽然可以正常导入到SPM,但是无法被相应的SQL语句使用,SQL语句仍然使用错误的计划执行查询。后面调整时,与实际生产时使用SQL语句方式一致,使用绑定变量的方式来执行调整后SQL语句,然后将获得计划导入SPM,发现语句可以使用SPM中的正确计划了。
具体的操作步骤可参看第一封邮件。
发件人: 张思明(Simon Cheung)
发送时间: 2011年10月18日 19:16
收件人: 聂宝红; ML_平安科技数据库技术支持部
主题: RE: ORACLE 11G 使用SPM来调整SQL语句的执行计划
聂宝红,要你发这个电邮是希望其他DBA能从你这次的问题处理上吸取经验。你这个电邮没有前文后理,没有背影介绍,只会让大家看得一头雾水。
From: 聂宝红
Sent: 2011年10月18日 19:00
To: ML_平安科技数据库技术支持部
Subject: ORACLE 11G 使用SPM来调整SQL语句的执行计划
1)获得执行计划错误的SQL语句的SQL_ID,并当前将坏的执行计划装载到SPM里:
variable cnt number;
execute :cnt :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID => '&SQL_ID', PLAN_HASH_VALUE => &HASH_VALUE) ;
检查SPM,确认相关的SQL计划已经被装载到SPM。LOAD进来的一般是最新的:
select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, SQL_TEXT from dba_SQL_PLAN_BASELINES where ACCEPTED = 'YES'
order by LAST_MODIFIED;
SQL_HANDLE PLAN_NAME
-------------------------------------------------------------
SQL_4079a044d6e19677 SQL_PLAN_40yd08mbfffddfdw555d8
2)调整SQL语句,如增加新的hint,确认获得好的正确的执行计划。执行一下调整后的语句,取得SQL_ID和Plan hash value:
select sql_id,plan_hash_value from v$sql where sql_text like '%/*+ test2-nbh INDEX(demand_state_alias%';
注意:对于绑定变量的SQL,最好也使用绑定变量的方式来获得正确的执行计划,如果使用字面量,执行计划虽然被装载,但可能无法被SQL语句使用。同时可以在SQL语句增加一些特别的提示,以容易获得修改后的语句,如上面的查询增加test2-nbh这样一个标识。
3)将正确的执行计划装载到SPM,准备用来替换错误的执行计划:
variable cnt number ;
exec :cnt :=dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE (SQL_ID => '&SQL_ID',PLAN_HASH_VALUE => &plan_hash_value,SQL_HANDLE => '&SQL_HANDLE' ) ;
SQL_ID: dzfky5zdzc231 –这个从步骤2中查询获得
Plan hash value: 751013780 –这个从步骤2中查询获得
SQL_HANDLE
SQL_4079a044d6e19677 --这个sql_handle是步骤1生成来的sql_handle
4)验证SPM执行计划是否正确
select * from dba_sql_plan_baselines where CREATED>sysdate-1/48 order by created;
--SQL_HANDLE为SQL_4079a044d6e19677的SPM记录有两个,可以通过时间的先后顺序来确定哪一个是
好的执行计划,也可以通过以下方 式:
select * from
table(dbms_xplan.DISPLAY_SQL_PLAN_BASELINE('&sql_handle','&PLAN_NAME');
--这里的sql_handle和PLAN_NAME来自步骤1生成的
5)验证了那个是错误的执行计划之后,将坏的执行计划从SPM里边删除
variable cnt number ;
exec :cnt :=dbms_spm.DROP_SQL_PLAN_BASELINE(SQL_HANDLE=> '&SQL_HANDLE', PLAN_NAME=> '&PLAN_NAME')
5)重新执行语句
6)检查语句执行计划是否正常
select
EXECUTIONS,PLAN_HASH_VALUE,ELAPSED_TIME/1000000,ELAPSED_TIME/1000000/EXECUTIONS,LAST_ACTIVE_TIME,ROWS_PROCESSED
from v$sql where EXECUTIONS>0 and sql_id='&sql'; select * from table(dbms_xplan.display_cursor('&sql'));