记一次隐式转化唤起的数据库性能故障
日期：2014-05-16 浏览次数：20426 次
记一次隐式转化引起的数据库性能故障
上周给客户数据库从Oralce 9.2.0.4升级到10.2.0.5之后，系统稳定运行。但昨天打电话给我说，数据库出现性能问题，主要表现为保存提交时非常缓慢，比升级之前慢了好多。正好，同一天，同一个客户的另一个rac数据库二号节点宕机，需要现场支持。帮助客户分析好rac宕机原因之后，开始分析数据库性能分析。通常来讲，数据库升级之后，出现业务响应缓慢，一般都是执行计划变更引起的。由于客户不能提供性能变坏业务模块SQL语句，于是只好从AWR报告开始分析。我们分析问题时，有一点需要注意的是，客户告之的故障之后，对故障要有一般要有自己的判断，不能被客户牵着鼻子走，否则容易误入歧途，给故障诊断，带来不利的影响。闲话不说，步入正题，首先分析awr报告。
awr报告采样自业务高峰期间，具有一定的典型性:
从profile来看，物理读比例有点高，达到了每秒8,211次。硬解析也偏高，达到了每秒14.09次，这两项指标也直接导致了buffer cache命中率和library cache命中率偏低，分别只有
82.59%和81.31%。指标偏低只能给我们指明数据库可能出问题的方向，由于没有做性能指标baseline，通过这指标偏低数据库可能存在2个问题：1、SQL执行计划执行效率有问题，导致
大量的物理读。2、硬解析过多，可能引起shared pool中latch的争用。
通过查看top 5等待事件，数据库存在的问题，渐渐浮出水面。可以看到除了CPU TIME之外，read by other session和db file scattered read排在前2位，一般来讲，这2个等待事件。同时出现，也就意味着数据库中正在并发的执行全表扫描，而硬解析过多引起的故障可以暂时不予考虑。
一般来讲定位全表扫描的语句可以查看SQL ordered by Gets或者SQL ordered by Reads或者SQL ordered by CPU Time。
通过查找发现在SQL ordered by Gets，SQL ordered by Reads，SQL ordered by CPU Time，以下2条SQL语句排名均占前2位，现在问题越来越明朗了。
我们着重分析第一条SQL，查看其的执行计划，确定有无child，注意到字段BIND_DATA，该SQL可能有绑定变量窥视（bind peeking），在Oracle 11g之前，绑定变量窥视一直是执行计划不稳定的重要原因之一。
确定child_number为0之后，利用Oracle 10g提供的dbms_xplan.display_awr工具包可以知道此SQL在awr报告里的执行计划，在Oracle 9i需要将statspack级别设置成6级之后，才能查看sql在statspack的执行计划。可以看到SQL采用了全表扫描的执行计划，其cost高达15397。注意到字段RYYWBM采用绑定变量赋值。
为避免绑定变量窥视带来的执行计划不稳定，可以看到RYYWBM选择性很好
上周给客户数据库从Oralce 9.2.0.4升级到10.2.0.5之后，系统稳定运行。但昨天打电话给我说，数据库出现性能问题，主要表现为保存提交时非常缓慢，比升级之前慢了好多。正好，同一天，同一个客户的另一个rac数据库二号节点宕机，需要现场支持。帮助客户分析好rac宕机原因之后，开始分析数据库性能分析。通常来讲，数据库升级之后，出现业务响应缓慢，一般都是执行计划变更引起的。由于客户不能提供性能变坏业务模块SQL语句，于是只好从AWR报告开始分析。我们分析问题时，有一点需要注意的是，客户告之的故障之后，对故障要有一般要有自己的判断，不能被客户牵着鼻子走，否则容易误入歧途，给故障诊断，带来不利的影响。闲话不说，步入正题，首先分析awr报告。
awr报告采样自业务高峰期间，具有一定的典型性:
从profile来看，物理读比例有点高，达到了每秒8,211次。硬解析也偏高，达到了每秒14.09次，这两项指标也直接导致了buffer cache命中率和library cache命中率偏低，分别只有
82.59%和81.31%。指标偏低只能给我们指明数据库可能出问题的方向，由于没有做性能指标baseline，通过这指标偏低数据库可能存在2个问题：1、SQL执行计划执行效率有问题，导致
大量的物理读。2、硬解析过多，可能引起shared pool中latch的争用。
通过查看top 5等待事件，数据库存在的问题，渐渐浮出水面。可以看到除了CPU TIME之外，read by other session和db file scattered read排在前2位，一般来讲，这2个等待事件。同时出现，也就意味着数据库中正在并发的执行全表扫描，而硬解析过多引起的故障可以暂时不予考虑。
一般来讲定位全表扫描的语句可以查看SQL ordered by Gets或者SQL ordered by Reads或者SQL ordered by CPU Time。
通过查找发现在SQL ordered by Gets，SQL ordered by Reads，SQL ordered by CPU Time，以下2条SQL语句排名均占前2位，现在问题越来越明朗了。
我们着重分析第一条SQL，查看其的执行计划，确定有无child，注意到字段BIND_DATA，该SQL可能有绑定变量窥视（bind peeking），在Oracle 11g之前，绑定变量窥视一直是执行计划不稳定的重要原因之一。
引用
SQL> select CHILD_NUMBER,LAST_ACTIVE_TIME,BIND_DATA from v$sql where sql_id='cx0dsyvc8gmfp';
CHILD_NUMBER LAST_ACTIVE_TIME BIND_DATA
------------ ------------------------------ ----------------------------------
0 2011-04-20 10:08:11 BEDA0A2005004DAE3D43000101C002160BCA041F1501010B0831105E
CHILD_NUMBER LAST_ACTIVE_TIME BIND_DATA
------------ ------------------------------ ----------------------------------
0 2011-04-20 10:08:11 BEDA0A2005004DAE3D43000101C002160BCA041F1501010B0831105E
确定child_number为0之后，利用Oracle 10g提供的dbms_xplan.display_awr工具包可以知道此SQL在awr报告里的执行计划，在Oracle 9i需要将statspack级别设置成6级之后，才能查看sql在statspack的执行计划。可以看到SQL采用了全表扫描的执行计划，其cost高达15397。注意到字段RYYWBM采用绑定变量赋值。
引用
SQL> select * from table(dbms_xplan.display_awr('cx0dsyvc8gmfp'));
PLAN_TABLE_OUTPUT
--------------------------------------------
SQL_ID cx0dsyvc8gmfp
--------------------
SELECT HJDPPBZ,HJDSSXQ,HJDXZ,HJDXQ,HJDDZBM,CLBZ FROM PZT_CZRK_DJ WHERE
RYYWBM=:B1
Plan hash value: 593801944
--------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
PLAN_TABLE_OUTPUT
--------------------------------------------
|
--------------------------------------------
-
| 0 | SELECT STATEMENT | | | | 15397 (100)|
|
| 1 | TABLE ACCESS FULL| PZT_CZRK_DJ | 1 | 97 | 15397 (1)| 00:03:36
|
PLAN_TABLE_OUTPUT
--------------------------------------------
--------------------------------------------
-
14 rows selected.
PLAN_TABLE_OUTPUT
--------------------------------------------
SQL_ID cx0dsyvc8gmfp
--------------------
SELECT HJDPPBZ,HJDSSXQ,HJDXZ,HJDXQ,HJDDZBM,CLBZ FROM PZT_CZRK_DJ WHERE
RYYWBM=:B1
Plan hash value: 593801944
--------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
PLAN_TABLE_OUTPUT
--------------------------------------------
|
--------------------------------------------
-
| 0 | SELECT STATEMENT | | | | 15397 (100)|
|
| 1 | TABLE ACCESS FULL| PZT_CZRK_DJ | 1 | 97 | 15397 (1)| 00:03:36
|
PLAN_TABLE_OUTPUT
--------------------------------------------
--------------------------------------------
-
14 rows selected.
为避免绑定变量窥视带来的执行计划不稳定，可以看到RYYWBM选择性很好
引用
SQL> select count(*) from hz2004.PZT_CZRK_DJ;
COUNT(*)
----------
5003467
SQL> select count(distinct RYYWBM) from hz2004.PZT_CZRK_DJ;
COUNT(DISTINCTRYYWBM)
---------------------
&nbs
COUNT(*)
----------
5003467
SQL> select count(distinct RYYWBM) from hz2004.PZT_CZRK_DJ;
COUNT(DISTINCTRYYWBM)
---------------------
&nbs
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
