ORACLE 11G 使用SPM来调整SQL语句的执行过程

这篇文章将为大家详细讲解有关ORACLE 11G 使用SPM来调整SQL语句的执行过程，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。

跟大家说明一下：
 ITSM 数据库迁移升级到11G后，有几条SQL语句的执行计划不正确，而且这些语句都是使用绑定变量的。
最初的调整想法是获得这些的语句的绑定变量值，将获得的字面值直接替换SQL语句的绑定变量，调整该SQL到正确的执行计划后执行，取得正确的执行计划并导入SPM。然而实际调整时发现，使用字面量获得的执行计划虽然可以正常导入到SPM，但是无法被相应的SQL语句使用，SQL语句仍然使用错误的计划执行查询。后面调整时，与实际生产时使用SQL语句方式一致，使用绑定变量的方式来执行调整后SQL语句，然后将获得计划导入SPM,发现语句可以使用SPM中的正确计划了。

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

关于ORACLE 11G 使用SPM来调整SQL语句的执行过程就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。