老熊的三分地-Oracle、UNIX、数据恢复

这次的案例同样是一个省电信的数据库，只不过比《记一个SQL优化案例》中提到的数据库规模要大得多。先简单地介绍一下环境，运行在AIX 5300 TL05上的Oracle 9.2.0.8。系统维护人员发现一个应用的中间件队列全部堵塞。检查数据库的等待事件，发现这个应用连接的会话，基本上都是在等待latch free，latch#为98，很明显是SQL性能出现了问题。因此，检查几个会话正在运行的SQL，都是下面类似的SQL：

select c.acct_id,
       a.serv_id,
       d.cust_code,
       d.cust_id,
       a.acc_nbr,
       c.acct_name,
       c.acct_nbr_97,
       e.name serv_acct_state,
       to_char(b.state_date, ’yyyymmdd’) state_date,
       f.name serv_state,
       h.product_name
  from serv a,
       serv_acct b,
       acct c,
       cust d,
       (select domain, name
          from v_domain
         where table_name = ’SERV_ACCT’
           and field_name = ’STATE’) e,
       (select domain, name
          from v_domain
         where table_name = ’SERV’
           and field_name = ’STATE’) f,
       product h
 where a.serv_id = b.serv_id
   and b.acct_id = c.acct_id
   and a.cust_id = d.cust_id
   and b.state = e.domain
   and a.state = f.domain
   and a.product_id = h.product_id
   and b.state = ’10A’
   and c.state = ’10A’
   and a.state in (’2HA’, ’2HC’, ’2HD’, ’2HE’, ’2HH’, ’2HN’, ’2HS’)
   and a.serv_id in
       (SELECT distinct serv_id
          FROM serv_attr
         WHERE attr_val = ’0xx833xxxxx’
           AND attr_id IN (SELECT attr_id
                             FROM a_query_acct_attr
                            WHERE state = ’A0A’
                              and attr_type = ’ACT’))

我隐去了代码中那ATTR_VAL=条件后真实的值，以’0xx833xxxxx’代替。

这个SQL咋一看跟《记一个SQL优化案例》提到的SQL都很相似，想想也能明白，都是一家开发商开发的系统^_^。

我在这条SQL中看到下面这样的代码，我就头痛：

SELECT distinct serv_id
          FROM serv_attr
         WHERE attr_val = ’0xx833xxxxx’
           AND attr_id IN (SELECT attr_id
                             FROM a_query_acct_attr
                            WHERE state = ’A0A’
                              and attr_type = ’ACT’)

对于SERV_ATTR这个表，我们可理解为这个表存储了所有用户的属性，每一个用户有多行，每一行有一个ATTR_ID，表示属性ID，也就是表示是什么属性，而ATTR_VAL则是属性的值。这样可以很方便地进行扩展，比如增加属性类型，甚至是自定义属性等。但是这样的设计，虽然具有了很大的灵活性，但不得不说，这样的设计，放在数据库中，基本上违背了关系型数据库的初衷。ATTR_VAL虽然定义为VARCHAR2类型，但实际存储的数据也可能是数值，日期，只不过都以字符串来表示。这样的表，对于Oracle的优化器来说，可以说是一个巨大的挑战。

虽然如此，不得不先接受现实，得想办法让这个SQL正常。第一步当然还是看执行计划了：
Read the rest of this entry »

其实这篇文章，我也不知道用什么标题为好。只是对今天发生的一个案例的思考。

今天是6号，是电信每个新帐期收费的第1天（可能各地有所差异）。某个省电信的收费系统Oracle数据库不堪压力，前台已经不能正常进行收费。而我登上数据库主机时，发现CPU使用率已经达到100%，90%以上的CPU使用率是user模式。这个主机有40个CPU（按逻辑CPU算是80个），而运行队列一直在130以上。

由于我之前没有接触过这套系统，对系统的能力，平时系统的状况都不是很了解。没有发现明显消耗CPU的进程，检查等待事件、以及使用statspack做了个10分钟左右的报告，没有发现特别的异常。唯一的异常是，活动会话太多。

为了解决此问题，客户立即给主机增加了16个CPU（这些CPU本来就在主机上，只是没有划给系统使用），同时重启了应用中间件。这个问题就这样暂时解决了，“火被扑灭了”。

下面分析一下今天这个问题：

大家应该都会有一个共识，应用服务器是很容易水平（横向）扩展的，一台应用服务器不够，可以再增加一台，对于很多应用，应用服务器几乎具有无限制的水平扩展能力。所以一个系统，其甁颈往往出现在数据库。RAC数据库几乎不能够线性扩展，比如两个节点的RAC，其处理能力达到单个节点库的1.6倍已经算是不错了。同时数据库往往受限于IO子系统的能力极限，扩展能力有限。因此，通过增加应用服务器，应用服务器可以接受无限制的请求，但是数据库的处理能力则是有限制的。

回到今天这个问题上，由于今天是业务高峰期，数据库接收到大量来自于应用服务器的请求，达到了某一个临界值以后，系统资源效率下降，比如，IOPS达到一定程度，IO响应时间大幅下降（当然这个系统IO子系统还没表现明显的瓶颈），CPU效率也会下降，同一个事务，以前只需要消耗CPU时间1s的，现在可能需要1.1s，一些等待也会加剧。这样应用服务器的请求队列越来越长，导致数据库的请求也越来越多，活动会话数越来越高。数据库处理的速度已经跟不上请求的速度了。CPU也就长时间保持在100%的使用率，也不足为奇了。

重启应用中间件，就强制中止了所有的请求，消除了排队，避免了给数据库增加更多的压力。重启应用中间件以及增加CPU（增加处理能力）之后，系统就恢复了正常。不过之后一段时间的观察，CPU使用率按之前40个CPU折算，也是在85%左右，也是一个比较高的值。

经过后面一段时间分析，数据库中也存在一些性能问题，存在着较大的优化余地。但是，对于这样一个成熟的庞大的系统来说，不要期望像像网上很多文章介绍的那样，优化极个别的SQL就能解决问题。存在性能不够优化的SQL很多（注意这里说的不够优化，并不是说存在严重的性能问题），并且反映出来的问题很多，涉及面相当广，比如有些表的统计信息缺失，有些SQL写法有问题，有些是索引使用不够合理，执行计划经常变动，不够稳定、有些表有碎片等等。解决这样的系统的性能问题，非一日之功。

所以今天对这个案例，让我思考的是另一方面，对于容量规划。某些系统，像电信的收费（销帐）系统，在业务高峰期，其交易量可以达到平时交易量的2-4倍（只是个粗略估计）。对于这样的系统，我们是不是应该让系统保证足够的处理能力？特别是CPU以及IO子系统能力。对于RAC数据库来说，对每个节点，是不是应该让系统足够在另一个节点DOWN掉的情况下能够支持所有的业务量，如果不能，那么RAC的高可用性就不能得到保证，一个节点DOWN掉以后，其他节点，如果不能承受DOWN掉节点转移过来的压力，也将会不堪重负而垮掉。

同时随着业务量的增加和数据量的增长，系统的压力也会越来越大。因此容量规划也需要考虑这个因素。

对于今天这样的一个系统，如果需要系统能够支撑业务高峰期的压力，通过优化系统，使之在业务低谷期，CPU的利用率应该控制在30%以下，否则今天这样的事情难免会重演。

一篇杂乱的文章，没有详细讨论容量规划，这是一个很大的话题，欢迎讨论。

大家都知道Oracle 10g的dbms_stats包与Oracle 9i相比，功能增强了很多，比如增加了display_cursor这个过程，能够查看V$SQL_PLAN视图中的执行计划，如果在statistics_level参数设置为ALL，或者执行的sql使用了gather_plan_statistics hint，则在sql执行后，会在v$sql_plan_statistics_all视图中查到SQL的执行统计信息，例如逻辑读，物理读等等。这些数据对于性能诊断有着非常大的帮助。同时v$sql_plan中的执行计划，与通过EXPLAIN PLAN得到的执行计划相比，前者是oracle执行sql时真正使用的执行计划，而后者则可能不是真正的执行计划；同时有的时候，执行过的sql使用了绑定变量，而oracle在解析sql时通常会进行绑定变量窥探，这个时候我们不能使用EXPLAIN PLAN来得到那个sql的执行计划，就算得到的跟那个sql的真实的执行计划是不一样的，所以有时我们更愿意直接从v$sql_plan中得到执行计划。

但是在oracle 9i中的dbms_xplan包没有display_cursor这个过程。不过，本文根据一个开源软件SQLT中得到的一段脚本，经过修改后，能够显示v$sql_plan和v$sql_plan_statistics中的执行计划和sql的执行统计数据。点击此处下载display_cursor_9i代码

下面是使用这个代码的示例：

SQL> select /*+ sqla */ count(*) from t1 where a<13;

  COUNT(*)
----------
     40000

在另一个会话中，得到这个SQL的hash_value , child_number以及在v$sql_plan中的执行计划。

SQL> select hash_value,child_number from v$sql where sql_text like ‘%sqla%’ and sql_text not like ‘%v$sql%’;

HASH_VALUE CHILD_NUMBER
---------- ------------
1742773495            0

SQL> @display_cursor_9i 1742773495 0
原值  268:   s_hash_value := &1;
新值  268:   s_hash_value := 1742773495;
原值  269:   s_child_num := &2;
新值  269:   s_child_num := 0;

HASH_VALUE: 1742773495   CHILD_NUMBER: 0
---------------------------------------------------------------------------------------------
select /*+ sqla */ count(*) from t1 where a<13

Plan hash value: 3724264953

------------------------------------------------------------
| Id   | Operation           | Name |  Rows | Bytes | Cost |
------------------------------------------------------------
|    0 | SELECT STATEMENT    |      |       |       |   25 |
|    1 |  SORT AGGREGATE     |      |     1 |     3 |      |
| *  2 |   TABLE ACCESS FULL | T1   | 44444 |  133K |   25 |
------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(”A”<13)

PL/SQL 过程已成功完成。

如果我们将statistics_level设置为ALL（注意：在oracle 9i中gather_plan_statistics这个hint无效），重新执行这个SQL：

SQL> alter session set statistics_level=all;

会话已更改。

SQL> select /*+ sqla */ count(*) from t1 where a<13;

  COUNT(*)
----------
     40000

在会话2中重新进行之前的查询，只不过由于参数的参数，这个SQL有两个子游标，这次执行的游标其child_number为1：

SQL> select hash_value,child_number from v$sql where sql_text like ‘%sqla%’ and sql_text not like ‘%v$sql%’;

HASH_VALUE CHILD_NUMBER
---------- ------------
1742773495            0
1742773495            1

SQL> @display_cursor_9i 1742773495 1
原值  268:   s_hash_value := &1;
新值  268:   s_hash_value := 1742773495;
原值  269:   s_child_num := &2;
新值  269:   s_child_num := 1;

HASH_VALUE: 1742773495   CHILD_NUMBER: 1
-------------------------------------------------------------------------------------------------------------------
select /*+ sqla */ count(*) from t1 where a<13

Plan hash value: 3724264953

----------------------------------------------------------------------------------------------------------------
| Id   | Operation          | Name | Starts | E-Rows | A-Rows | A-Time      | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|    1 | SORT AGGREGATE     |      |      0 |      1 |      0 | 00:00:00.00 |       0 |    0 |    0 |    0 (0) |
| *  2 |  TABLE ACCESS FULL | T1   |      0 |  44444 |      0 | 00:00:00.00 |       0 |    0 |    0 |    0 (0) |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(”A”<13)

PL/SQL 过程已成功完成。

不幸的是，在另一个会话中查询v$sql_plan_statistics_all的一些结果并不正确。只有在那个执行SQL的会话（就是例子中的会话1）中，才能得到正确的结果：

----------------------------------------------------------------------------------------------------------------
| Id   | Operation          | Name | Starts | E-Rows | A-Rows | A-Time      | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|    1 | SORT AGGREGATE     |      |      1 |      1 |      1 | 00:00:00.39 |     155 |    0 |    0 |    0 (0) |
| *  2 |  TABLE ACCESS FULL | T1   |      1 |  44444 |  40000 | 00:00:00.21 |     155 |    0 |    0 |    0 (0) |
----------------------------------------------------------------------------------------------------------------

如果v$sql_plan_statistics_all有数据，则这个脚本会生成上面的第2个示例的结果，否则，会得到示例1的结果。从输出的结果来看，朋友们，是不是与dbms_xplan的输出惊人地相似啊！

在这里只是测试了最简单的SQL，实际上这个脚本对于并行，CPU成本，TEMP临时表空间使用等数据都能够显示。有兴趣的朋友可以自己试试。

某省电信在做批扣（批销）时，出现严重的性能问题，发现下面这一条SQL性能非常低下：

SELECT A.ACCT_BALANCE_ID,
       A.BALANCE_TYPE_ID,
       A.ACCT_ID,
       NVL(A.SERV_ID, -1) SERV_ID,
       NVL(A.ITEM_GROUP_ID, -1) ITEM_GROUP_ID,
       A.OBJECT_TYPE_ID,
       F.PRIORITY,
       A.BALANCE,
       NVL(A.CYCLE_UPPER, -1) CYCLE_UPPER,
       NVL(A.CYCLE_LOWER, -1) CYCLE_LOWER,
       NVL(A.CYCLE_UPPER_TYPE, ’ ’) CYCLE_UPPER_TYPE,
       NVL(A.CYCLE_LOWER_TYPE, ’ ’) CYCLE_LOWER_TYPE,
       B.ADJUST_FLAG ADJUST_FLAG,
       B.ALLOW_TRANS ALLOW_TRANS,
       B.CORPUS_FLAG,
       NVL(TO_CHAR(A.EFF_DATE, ’YYYYMMDDHH24MISS’), ’ ’) EFF_DATE,
       NVL(TO_CHAR(A.EXP_DATE, ’YYYYMMDDHH24MISS’), ’ ’) EXP_DATE,
       A.STATE,
       TO_CHAR(A.STATE_DATE, ’YYYYMMDDHH24MISS’) STATE_DATE,
       B.BALANCE_TYPE_NAME,
       NVL(C.ACCT_NAME, ’ ’) ACCT_NAME,
       NVL(D.ACC_NBR, ’ ’) SERV_NAME,
       NVL(E.ITEM_GROUP_NAME, ’ ’) ITEM_GROUP_NAME
  FROM (SELECT ACCT_BALANCE_ID,
               BALANCE_TYPE_ID,
               ACCT_ID,
               NVL(SERV_ID, -1) SERV_ID,
               NVL(ITEM_GROUP_ID, -1) ITEM_GROUP_ID,
               OBJECT_TYPE_ID,
               BALANCE,
               NVL(CYCLE_UPPER, -1) CYCLE_UPPER,
               NVL(CYCLE_LOWER, -1) CYCLE_LOWER,
               NVL(CYCLE_UPPER_TYPE, ’ ’) CYCLE_UPPER_TYPE,
               NVL(CYCLE_LOWER_TYPE, ’ ’) CYCLE_LOWER_TYPE,
               EFF_DATE,
               EXP_DATE,
               STATE,
               STATE_DATE,
               0 SHARE_RULE_PRIORITY
          FROM ACCT_BALANCE
         WHERE ACCT_ID = :LACCTID
           AND BALANCE > 0
           AND BALANCE_TYPE_ID != 1
           AND STATE = ’10A’
        UNION
        SELECT A1.ACCT_BALANCE_ID,
               A1.BALANCE_TYPE_ID,
               B1.ACCT_ID,
               NVL(B1.SERV_ID, -1) SERV_ID,
               NVL(B1.ITEM_GROUP_ID, -1) ITEM_GROUP_ID,
               A1.OBJECT_TYPE_ID,
               A1.BALANCE,
               NVL(B1.UPPER_AMOUNT, -1) CYCLE_UPPER,
               NVL(B1.LOWER_AMOUNT, -1) CYCLE_LOWER,
               NVL(A1.CYCLE_UPPER_TYPE, ’ ’) CYCLE_UPPER_TYPE,
               NVL(A1.CYCLE_LOWER_TYPE, ’ ’) CYCLE_LOWER_TYPE,
               A1.EFF_DATE,
               A1.EXP_DATE,
               A1.STATE,
               A1.STATE_DATE,
               B1.PRIORITY SHARE_RULE_PRIORITY
          FROM ACCT_BALANCE A1, BALANCE_SHARE_RULE B1
         WHERE A1.ACCT_BALANCE_ID = B1.ACCT_BALANCE_ID
           AND B1.ACCT_ID = :LACCTID
           AND A1.BALANCE > 0
           AND A1.BALANCE_TYPE_ID != 1
           AND A1.STATE = ’10A’
           AND NVL(B1.EFF_DATE, SYSDATE) < = SYSDATE
           AND NVL(B1.EXP_DATE, SYSDATE) >= SYSDATE) A,
       BALANCE_TYPE B,
       ACCT C,
       SERV D,
       A_BALANCE_ITEM_GROUP E,
       A_BALANCE_OBJECT_TYPE F
 WHERE A.BALANCE_TYPE_ID = B.BALANCE_TYPE_ID
   AND A.OBJECT_TYPE_ID = F.OBJECT_TYPE_ID
   AND A.ACCT_ID = C.ACCT_ID
   AND A.SERV_ID = D.SERV_ID(+)
   AND A.ITEM_GROUP_ID = E.ITEM_GROUP_ID(+)
 ORDER BY F.PRIORITY,
          B.PRIORITY,
          A.SHARE_RULE_PRIORITY ASC,
          A.EXP_DATE ASC,
          A.EFF_DATE ASC,
          A.BALANCE ASC

查看执行计划：

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    |  Name                       | Rows  | Bytes |TempSpc| Cost  | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |                             |  2723G|   696T|       |  7776M|       |       |
|   1 |  SORT ORDER BY                               |                             |  2723G|   696T|  1503T|  7776M|       |       |
|*  2 |   HASH JOIN                                  |                             |  2723G|   696T|    59M| 39355 |       |       |
|*  3 |    HASH JOIN                                 |                             |   228K|    56M|       | 23918 |       |       |
|   4 |     TABLE ACCESS FULL                        | BALANCE_TYPE                |     8 |   184 |       |     7 |       |       |
|*  5 |     HASH JOIN                                |                             |   228K|    51M|       | 23907 |       |       |
|   6 |      TABLE ACCESS FULL                       | A_BALANCE_OBJECT_TYPE       |     4 |    16 |       |     7 |       |       |
|*  7 |      HASH JOIN OUTER                         |                             |   228K|    50M|    43M| 23896 |       |       |
|*  8 |       HASH JOIN OUTER                        |                             |   228K|    40M|    38M| 23199 |       |       |
|   9 |        VIEW                                  |                             |   228K|    36M|       |  2043 |       |       |
|  10 |         SORT UNIQUE                          |                             |   228K|    11M|    38M|  2043 |       |       |
|  11 |          UNION-ALL                           |                             |       |       |       |       |       |       |
|* 12 |           TABLE ACCESS BY GLOBAL INDEX ROWID | ACCT_BALANCE                |   228K|    11M|       |    50 | ROWID | ROW L |
|* 13 |            INDEX RANGE SCAN                  | IDX_ACCT_BALANCE_ACCT_ID42  |   121K|       |       |     3 |       |       |
|  14 |           NESTED LOOPS                       |                             |     1 |   146 |       |     4 |       |       |
|* 15 |            TABLE ACCESS FULL                 | BALANCE_SHARE_RULE          |     1 |   109 |       |     2 |       |       |
|* 16 |            TABLE ACCESS BY GLOBAL INDEX ROWID| ACCT_BALANCE                |     1 |    37 |       |     2 | ROWID | ROW L |
|* 17 |             INDEX UNIQUE SCAN                | PK_P_ACCT_BALANCE2          |     1 |       |       |     1 |       |       |
|  18 |        PARTITION RANGE ALL                   |                             |       |       |       |       |     1 |    63 |
|  19 |         TABLE ACCESS FULL                    | SERV                        |    12M|   258M|       | 14070 |     1 |    63 |
|  20 |       TABLE ACCESS FULL                      | A_BALANCE_ITEM_GROUP        |   244 | 11224 |       |     7 |       |       |
|  21 |    PARTITION RANGE ALL                       |                             |       |       |       |       |     1 |    63 |
|  22 |     TABLE ACCESS FULL                        | ACCT                        |    11M|   239M|       |  8505 |     1 |    63 |
------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access(”A”.”ACCT_ID”=”C”.”ACCT_ID”)
3 - access(”A”.”BALANCE_TYPE_ID”=”B”.”BALANCE_TYPE_ID”)
5 - access(”A”.”OBJECT_TYPE_ID”=”F”.”OBJECT_TYPE_ID”)
7 - access(”A”.”ITEM_GROUP_ID”=”E”.”ITEM_GROUP_ID”(+))
8 - access(”A”.”SERV_ID”=”D”.”SERV_ID”(+))
12 - filter(”ACCT_BALANCE”.”BALANCE”>0 AND “ACCT_BALANCE”.”BALANCE_TYPE_ID”<>1 AND “ACCT_BALANCE”.”STATE”=’10A’)
13 - access(”ACCT_BALANCE”.”ACCT_ID”=TO_NUMBER(:Z))
15 - filter(”B1″.”ACCT_ID”=TO_NUMBER(:Z) AND NVL(”B1″.”EFF_DATE”,SYSDATE@!)< =SYSDATE@! AND
NVL("B1"."EXP_DATE",SYSDATE@!)>=SYSDATE@!)
16 - filter(”A1″.”BALANCE”>0 AND “A1″.”BALANCE_TYPE_ID”<>1 AND “A1″.”STATE”=’10A’)
17 - access(”A1″.”ACCT_BALANCE_ID”=”B1″.”ACCT_BALANCE_ID”)

Read the rest of this entry »

故障发生时间：12月6日早上
系统环境：HP Superdome系列，128G内存，64CPU,Oracle 9.2.0.8
故障现象：CPU占用将近100%，运行队列达到60-80，应用反应速度很慢。

这是一个省级电信的核心系统。

在用户反映速度很慢后，在主机上检查发现CPU很高，将近100%，而运行队列达到了60-80。检查Oracle，发现很多的会话在等待latch free，latch#为98

SQL> select * from v$latchname where latch#=98;

    LATCH# NAME
---------- ----------------------------------------------------------------
        98 cache buffers chains

检查正在等待latch free的会话正在执行的SQL，大部分都在执行类似于下面的SQL：

SELECT SUM(cnt),
       to_char(nvl(SUM(nvl(amount, 0)) / 100, 0), ’FM9999999999990.90′) amount
  FROM (select count(payment_id) cnt, SUM(amount) amount
          from payment
         where staff_id = 592965
           and CREATED_DATE >= trunc(sysdate)
           and state = ’C0C’
           and operation_type in (’5KA’, ’5KB’, ’5KC’, ’5KP’))

看起来这个SQL并不复杂，查看其执行计划：
Read the rest of this entry »