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

这次的案例同样是一个省电信的数据库，只不过比《记一个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正常。第一步当然还是看执行计划了：

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                |  Name                       | Rows  | Bytes | Cost  | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                             |     1 |   220 | 17191 |       |       |
|   1 |  NESTED LOOPS                            |                             |     1 |   220 | 17191 |       |       |
|   2 |   NESTED LOOPS                           |                             |     1 |   199 | 17189 |       |       |
|   3 |    NESTED LOOPS                          |                             |     1 |   169 | 17187 |       |       |
|   4 |     NESTED LOOPS                         |                             |     1 |   155 | 17186 |       |       |
|*  5 |      HASH JOIN SEMI                      |                             |     1 |   116 | 17184 |       |       |
|   6 |       NESTED LOOPS                       |                             |   411 | 42333 | 17017 |       |       |
|   7 |        MERGE JOIN                        |                             |   411 | 27126 | 16195 |       |       |
|   8 |         PARTITION RANGE ALL              |                             |       |       |       |     1 |    14 |
|*  9 |          TABLE ACCESS FULL               | SERV_ACCT                   |  8667K|   223M| 16177 |     1 |    14 |
|* 10 |         SORT JOIN                        |                             |     1 |    39 |    18 |       |       |
|  11 |          TABLE ACCESS BY INDEX ROWID     | SYS_DOMAIN                  |     1 |    39 |     2 |       |       |
|* 12 |           INDEX RANGE SCAN               | IDX_SYS_DOMAIN_1            |     1 |       |     1 |       |       |
|  13 |        PARTITION RANGE ITERATOR          |                             |       |       |       |   KEY |   KEY |
|* 14 |         TABLE ACCESS BY LOCAL INDEX ROWID| SERV                        |     1 |    37 |     2 |   KEY |   KEY |
|* 15 |          INDEX UNIQUE SCAN               | PK_SERV                     |     1 |       |     1 |   KEY |   KEY |
|  16 |       VIEW                               | VW_NSO_1                    |  4977 | 64701 |   166 |       |       |
|  17 |        TABLE ACCESS BY LOCAL INDEX ROWID | SERV_ATTR                   |  1991 | 37829 |    53 |     1 |     1 |
|  18 |         NESTED LOOPS                     |                             |  4977 |   136K|   166 |       |       |
|* 19 |          TABLE ACCESS FULL               | A_QUERY_ACCT_ATTR           |     3 |    27 |     7 |       |       |
|  20 |          PARTITION RANGE ALL             |                             |       |       |       |     1 |    14 |
|* 21 |           INDEX RANGE SCAN               | IDX_SERV_ATTR_INTERNET_NEW  |  1991 |       |    42 |     1 |    14 |
|  22 |      TABLE ACCESS BY INDEX ROWID         | SYS_DOMAIN                  |     1 |    39 |     2 |       |       |
|* 23 |       INDEX RANGE SCAN                   | IDX_SYS_DOMAIN_1            |     1 |       |     1 |       |       |
|  24 |     TABLE ACCESS BY INDEX ROWID          | PRODUCT                     |     1 |    14 |     1 |       |       |
|* 25 |      INDEX UNIQUE SCAN                   | PK_PRODUCT                  |     1 |       |       |       |       |
|* 26 |    TABLE ACCESS BY GLOBAL INDEX ROWID    | ACCT                        |     1 |    30 |     2 | ROWID | ROW L |
|* 27 |     INDEX UNIQUE SCAN                    | PK_ACCT                     |     1 |       |     1 |       |       |
|  28 |   TABLE ACCESS BY GLOBAL INDEX ROWID     | CUST                        |     1 |    21 |     2 | ROWID | ROW L |
|* 29 |    INDEX UNIQUE SCAN                     | PK_CUST                     |     1 |       |     1 |       |       |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("A"."SERV_ID"="VW_NSO_1"."SERV_ID")
   9 - filter("B"."STATE"='10A')
  10 - access("B"."STATE"="SYS_DOMAIN"."DOMAIN")
       filter("B"."STATE"="SYS_DOMAIN"."DOMAIN")
  12 - access("SYS_DOMAIN"."TABLE_NAME"='SERV_ACCT' AND "SYS_DOMAIN"."FIELD_NAME"='STATE' AND
              "SYS_DOMAIN"."DOMAIN"='10A')
  14 - filter("A"."STATE"='2HA' OR "A"."STATE"='2HC' OR "A"."STATE"='2HD' OR "A"."STATE"='2HE' OR "A"."STATE"='2HH' OR
              "A"."STATE"='2HN' OR "A"."STATE"='2HS')
  15 - access("A"."SERV_ID"="B"."SERV_ID")
  19 - filter("A_QUERY_ACCT_ATTR"."STATE"='A0A' AND "A_QUERY_ACCT_ATTR"."ATTR_TYPE"='ACT')
  21 - access("SERV_ATTR"."ATTR_VAL"='0xx833xxxxx' AND "SERV_ATTR"."ATTR_ID"="A_QUERY_ACCT_ATTR"."ATTR_ID")
  23 - access("SYS_ALIAS_0000"."TABLE_NAME"='SERV' AND "SYS_ALIAS_0000"."FIELD_NAME"='STATE' AND
              "A"."STATE"="SYS_ALIAS_0000"."DOMAIN")
       filter("SYS_ALIAS_0000"."DOMAIN"='2HA' OR "SYS_ALIAS_0000"."DOMAIN"='2HC' OR "SYS_ALIAS_0000"."DOMAIN"='2HD' OR
              "SYS_ALIAS_0000"."DOMAIN"='2HE' OR "SYS_ALIAS_0000"."DOMAIN"='2HH' OR "SYS_ALIAS_0000"."DOMAIN"='2HN' OR
              "SYS_ALIAS_0000"."DOMAIN"='2HS')
  25 - access("A"."PRODUCT_ID"="H"."PRODUCT_ID")
  26 - filter("C"."STATE"='10A')
  27 - access("B"."ACCT_ID"="C"."ACCT_ID")
  29 - access("A"."CUST_ID"="D"."CUST_ID")

从执行计划来看，很容易发现有异常，有一个在SERV_ACCT表上的全表扫描，对这个表用”STATE”=’10A’这个条件过滤之后，仍然有860多万行，其次是在执行计划标志为17和21的那2行，优化器评估有1991行。而优化器整个SQL最终评估出来的行数只有1行。从这个SQL要查询的数据来看，返回1条数据，相差并不大，因为这个SQL看上去是在查某个用户（客户）的资料。

询问数据库DBA，最近有没有什么变动，回答说没有。又问这个SQL以前有没有出现类似的情况的，回答说有过，看起来还不止一次。然后又询问DBA，那出现这样的问题时，咋解决的，回答说有时候重新分析一下SERV_ACCT表就好了。不过，出现这样的故障，没有进行适当程度的诊断就贸然地重新收集SERV_ACCT表显然不妥（不过就算是重新收集了这个表的统计信息，仍然不会解决问题，后文有提及）。

从执行计划来看，SERV_ACCT表嫌疑比较大。SQL中SERV_ACCT表上有一个过滤条件和一个与SERV表的连接条件，分别是STATE='10A'和SERV_ACCT.SERV_ID=SERV.SERV_ID。STATE='10A'这个条件很明显，过滤掉的数据非常少。所以先检查SERV_ACCT表，特别是要检查SERV_ACCT表上的SERV_ID列，是否有索引，索引的状态是否正常。通过查询DBA_TAB_PARTITIONS、DBA_INDEXES、DBA_IND_COLUMNS、DBA_IND_PARTITIONS等视图，发现SERV_ACCT表有十几个分区，存在SERV_ID列上的单列本地索引，索引状态都是有效的。排除了索引无效的问题。

接下来，需要检查SERV_ACCT表与SERV表的关联。看看SERV表和SERV_ATTR上的数据会返回多少行：

SQL> SELECT distinct serv_id
  2            FROM acct.serv_attr
  3           WHERE attr_val = '0xx833xxxxx’
  4             AND attr_id IN (SELECT attr_id
  5                               FROM acct.a_query_acct_attr
  6                              WHERE state = 'A0A'
  7                                and attr_type = 'ACT');

   SERV_ID
----------
x.x000E+11

查询结果返回只有1行，由于SERV_ID是SERV表上的主键，所以SERV表也会只返回一行。因此，合适的执行计划应该是SERV表过滤后，作为驱动表与SERV_ACCT进行嵌套循环连接NESTED LOOPS JOIN，但是为什么这里却是SERV_ACCT表与SYS_DOMAIN做MERGE JOIN之后的结果集作为驱动与SERV表作NESTED LOOPS JOIN？不过这里有一点值得注意，由于闭包传递和谓词推入(push predicate)---见执行计划12行及下面的注释，SYS_DOMAIN表在过滤之后，并且从执行计划的第7行可以看出，由于闭包传递，连接条件已经丢失，这种情况下，第7行的MERGE JOIN评估的行数应该与第8行显示的行数（8667K）一致才对，而这里显示的是411行。这里连接不用SERV表做驱动表，比较合理的推测是Oracle优化器评估出来的SERV表作驱动和SERV_ACCT嵌套循环连接的成本高于执行计划中给出的连接产生的成本。

下面来粗略分析一下如果使用SERV表作驱动的成本。

    嵌套循环连接成本=外部表（驱动表）访问成本+外部表返回行数*每次访问内部表的成本

这里内部表就是SERV_ACCT表，我们这里只估算一下上面公式中后面一部分：

4977*14*(2+1)=209034
4977是SERV表过滤后的行数，也就是执行计划中第16行列出的cardinality
14是SERV_ACCT表的索引分区数
2是访问索引的成本
1经过索引回表，访问表上数据的成本

显然这个值已经大大超过了前面列出的执行计划的总成本了。

前面对SERV_ATTR表的查询中只返回1行，而实际上执行计划给出的行数却是4977行，显然存在问题。检查SERV_ATTR表ATTR_VAL列的统计信息，num_rows / num_distinct 约为10，也就是说评估的行数应该在10行左右才对。不过，观察发现，这个列上收集有直方图，这应该就是造成优化器对该Row Source评估的行数偏高的原因。

让数据库DBA以10%的抽样比例重新收集了SERV_ATTR表的统计信息，为加快速度，没有收集索引的统计信息（经过检查发现，表和索引的统计信息相对比较准确，这里只是因为有直方图才需要重新收集表的统计信息）。完成之后，重新生成执行计划，执行计划已经正常。最后重启中间件，清除堵塞的队列，应用恢复正常。

在收集表的统计信息时，发生了一个小插曲，数据库DBA开始时没注意到我要求的是收集SERV_ATTR表的统计信息，而是按惯例收集了SERV_ACCT表的统计信息，当收集完SERV_ACCT表的统计信息之后发现执行计划仍然不正常，直到最后收集了SERV_ATTR表的统计信息之后才正常了。这也说明了，这个性能问题并不是如往常的那样是由于SERV_ACCT统计信息不准引起的。

这个SQL的性能问题暂时就这样得到解决了，然后这个SQL反映出来的问题，不光是设计上，还包括SERV_ACCT表在SERV_ID上的索引问题。这个索引是一个本地索引，然而系统中的SQL基本上没有使用分区键来进行分区排除，显然使用全局索引更为合适。

总结：

• 这个案例至少证明了一点，直方图并不总是带来好处，相反可能会引起性能问题。之前没有出故障，可能是之前某个环节的成本在临界值，这样的情况下，就容易出现执行计划改变。由于在处理故障时时间比较紧，没有收集到足够的信息，很难判断之前的状态。
• 保存系统中关键SQL的执行计划，会对性能故障处理有非常大的帮助。然而要做到这一点，按目前的情况来说，比较难。很多系统根本没有计划做这样的事情。
• 执行计划的第7行，看起来是个BUG。我准备找个时间测试一下，看能否重现这种现象。
• 熟悉ORACLE优化器成本计算，会对调优带来非常大的帮助。