oracle++spm,ORACLE 11G 使用SPM来调整SQL语句的执行计划

跟大家说明一下：

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'));