转://Oracle数据库升级后保障SQL性能退化浅谈

一、数据库升级后保障手段

为了保障从10.2.0.4版本升级到11.2.0.4版本更加平稳，我们事先采用了oracle性能分析器(SQL Performance Analyzer)来预测数据库的关键SQL在Oracle 11.2.0.4版本上的性能情况。以便提前发现问题并做相关性能优化。这一部分的SQL已经提前进行了优化处理。但是Oracle SPA功能这只是预测，我们并不能完全仿真真实应用业务压力上来之后对数据库性能造成的影响。因此，我们需要对其他SQL问题进行快速的处理，保障系统升级后平稳的运行。

二、SQL语句性能下降

2.1  和10g对比，检查执行计划有无发生变化

我们第一步要做的就是将新库SQL产生的执行计划和老库的执行计划进行对比，这种对比一般需要你先登陆到新库上查询，然后在登陆到老库上查询。这其实是比较麻烦的做法。做SPA的时候，我们把10g的SQL语句进行了一个捕捉，并把结果集保存在SQLSET中。我们可以把这个结果集作为一个数据表进行了导出导入，在11g环境中把这个放在SPA用户下。这个表的数据保存了10g数据库1-2月的游标，数据量大概在200-300万左右。可以考虑对它进行去重，去除字面的SQL，去除重复之后数据量只有20-30万。然后我们可以通过高CPU消耗脚本进行监控，或者是ash，awr报告，找到引起性能的sql_id。通过这个表和v$sql之间的相同SQL_ID语句的PLAN_HASH_VALUE进行对比。检查语句的执行计划有无改变。语句如下：

select distinct 'NEW   ',sql_id,PLAN_HASH_VALUE from V$SQL where sql_id='&sqlid'
union 
select distinct 'OLD   ',sql_id,PLAN_HASH_VALUE from spa.SQLSET2_TAB a where sql_id='&sqlid';

2.2  快速切换统计信息

当发现SQL语句的执行计划发生改变，我们需要检查是否是统计信息引起的问题，在这里我们默认会有两套统计信息供我们随时进行切换。当把数据迁移到11g之后，你可以把所有的表的统计信息收集一遍，然后用export_database_stats的方法导到一个表里面。然后在把10g的统计信息也导入到11g里面。这样你就可以在出问题的时候快速切换统计信息了。

exec dbms_stats.IMPORT_TABLE_STATS(OWNNAME=>'TABLE_OWNER',TABNAME=>'TABLE_NAME',STATTAB=>'STAT_11G',statown=>'SPA');
exec dbms_stats.IMPORT_TABLE_STATS(OWNNAME=>' TABLE_OWNER ',TABNAME=>' TABLE_NAME ',STATTAB=>'STAT_10G',statown=>'SPA');

2.3 使用SPM快速固定执行计划

如果发现统计信息也一致，而执行计划仍然变坏的语句，需要我们使用SPM来固定住执行计划，首先我们做SPA会有一个SQLSET，前面提到过，我们把导入到的表转换成11g的SQLSET，然后使用LOAD_PLANS_FROM_SQLSET方法进行固定。

declare
my_plans pls_integer;
begin
my_plans:=DBMS_SPM.LOAD_PLANS_FROM_SQLSET(SQLSET_NAME=>'SQLSET1',SQLSET_OWNER=>'SPA',basic_filter => 'sql_id=''6j2pfum10dvxg''');
end;
/

如果这个涉及到硬解析的语句，可能SQL_ID太多无法绑定，需要我们在basic_filter需要使用下面的语法。

basic_filter => 'sql_text like ''select /*LOAD_STS*/%'''

以下是一个SPM绑定的示例：

SQL> explain plan for select distinct prod_id from pd_prod_rel a,pd_userprc_info_41 b where a.element_idb= :ELEMENT_IDB           and a.relation_type in('3','4') and a.element_ida=b.prod_id  and b.exp_date>sysdate and b.id_no=20310013952141  and  PROD_MAIN_FLAG='1'and RELPRCINS_ID=0;
Explained.

SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2479329866

---------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                    |     1 |    51 |    23   (9)| 00:00:01 |
|   1 |  HASH UNIQUE                 |                    |     1 |    51 |    23   (9)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| PD_USERPRC_INFO_41 |     1 |    33 |     5   (0)| 00:00:01 |
|   3 |    NESTED LOOPS              |                    |     1 |    51 |    22   (5)| 00:00:01 |
|   4 |     INLIST ITERATOR          |                    |       |       |            |          |
|*  5 |      INDEX RANGE SCAN        | IDX_PRODREL        |     1 |    18 |    17   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN         | IDX_USERPRC_41_02  |     8 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 - filter("PROD_MAIN_FLAG"='1' AND "B"."EXP_DATE">SYSDATE@! AND
              "A"."ELEMENT_IDA"="B"."PROD_ID")
 - access(("A"."RELATION_TYPE"='3' OR "A"."RELATION_TYPE"='4') AND
              "A"."ELEMENT_IDB"=:ELEMENT_IDB)
       filter("A"."ELEMENT_IDB"=:ELEMENT_IDB)
 - access("B"."ID_NO"=20310013952141 AND "RELPRCINS_ID"=0)

Note
-----
   - SQL plan baseline "SQL_PLAN_9z5mbs0jxkucn6ea6bdfe" used for this statement
 rows selected.

SQL> select SQL_HANDLE,PLAN_NAME,ENABLED,ACCEPTED,FIXED,OPTIMIZER_COST from dba_sql_plan_baselines; 
SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX OPTIMIZER_COST
------------------------------ ------------------------------ --- --- --- --------------
SQL_9f966bc023d96994           SQL_PLAN_9z5mbs0jxkucn6ea6bdfe YES YES NO              10

删除SPM的示例：

declare
my_plans pls_integer;
begin
my_plans:=DBMS_SPM.DROP_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_9f966bc023d96994');
end;
/

SQL> select SQL_HANDLE,PLAN_NAME,ENABLED,ACCEPTED,FIXED,OPTIMIZER_COST from dba_sql_plan_baselines;

2.4 使用SQL Profile来绑定执行计划(COE脚本)

有时候使用SPM无法固定执行计划，需要我们使用SQL PROFILE来进行执行计划的固定。

-------------在10g环境下执行
SQL>START coe_xfr_sql_profile.sql

这个脚本执行完成之后会生产一个profile文件，把这个SQL文件拿到11g下执行。以下是一个示例：

SQL> start coe_xfr_sql_profile.sql 
Parameter 1:
SQL_ID (required)

Enter value for 1: 7jduw71f2w00p

PLAN_HASH_VALUE AVG_ET_SECS                ==》这里可以选择最好的计划，生成profile。
--------------- -----------
64        .051
Parameter 2:
PLAN_HASH_VALUE (required)

Enter value for 22344910864

Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID         : "7jduw71f2w00p"
PLAN_HASH_VALUE: "2344910864"

SQL>BEGIN
    IF sql_text IS NULL THEN
      RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
    END IF;
  END;
  /
SQL>SET TERM OFF;
SQL>BEGIN
    IF other_xml IS NULL THEN
      RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
    END IF;
  END;
  /
SQL>SET TERM OFF;

Execute coe_xfr_sql_profile_7jduw71f2w00p_2344910864.sql
on TARGET system in order to create a custom SQL Profile
with plan 2344910864 linked to adjusted sql_text.
COE_XFR_SQL_PROFILE completed.

然后我们就可以把coe_xfr_sql_profile_7jduw71f2w00p_2344910864.sql这个脚本拿到11g下执行即可。 删除sql profile使用下列命令：

exec dbms_sqltune.DROP_SQL_PROFILE(name=>'');