这次的案例同样是一个省电信的数据库,只不过比《记一个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优化器成本计算,会对调优带来非常大的帮助。
,
Trackback

18 comments untill now

  1. 学习了!

  2. netpinaster @ 2009-05-18 09:43

    学习了

  3. sql看了就眼熟。该是电信的OCS系统。:)

  4. 呱唧呱唧~~~~

  5. david3389 @ 2009-05-19 10:31

    这个帖子写得很好,有很高的借鉴价值

  6. 您有没有看过那本

  7. 不知道重新收集的时候是否收集了historgram?

    如果是histogram的问题,从sql上看没有使用bind,因此这个问题要么是histogram过于陈旧,要么是cursor_sharing=force,导致bind peeking后的查询计划不正确,这时可以直接grant一把来invalid当前的plan,不知道我猜的是否对?

    老熊 Reply:

    @dhhb, 重新收集统计信息的时候,histogram没有收集。
    这里,serv_attr这个表上的条件是用的常量(字面量),没有使用绑定变量,cursor_sharing也是exact。

  8. 哦,那应该是histogram太旧了造成的吧.如果对histogram也收集,plan应当可以走对的吧.

    老熊 Reply:

    @dhhb, 这个表,每个月至少会分析一次。最主要的是,根据对这个表的分析,那个列上收集直方图是不太合适的。

  9. 如果不是bug的因素,historgram在exact的情况下,应用没有使用bind,只要histogram信息准确,应该会帮助产生正确的plan,不知道老熊说的不太合适是什么意思?或者说每月分析一次的频度还不够,还要更细致,每天分析一次?呵呵,既然不分析histogram也能解决问题,那说明这个表的数据比较均匀,只是觉得只要不是bug,histogram在这种情况下应该不会产生错误的plan.

  10. 哦,应该是serv_attr里的distinct serv_id太多>>254,导致了直方图没办法在限制内显示所有的数据频度.

    老熊 Reply:

    @dhhb, 是ATTR_VAL列上不应该收集直方图,而不是serv_id

  11. 呵呵,是是,打错了:)

  12. 数据库DBA开始时没注意到我要求的是收集SERV_ATTR表的统计信息,而是按惯例收集了SERV_ACCT表的统计信息,当收集完SERV_ACCT表的统计信息之后发现执行计划仍然不正常,直到最后收集了SERV_ATTR表的统计信息之后才正常了

    这一段是不是写错了表名,怎么都一样

    老熊 Reply:

    @zabll, 这里没有错,有两张表,SERV_ACCT和SERV_ATTR表。

  13. 看这个帖子看了2个月,不过还是有点不太明白

    并且从执行计划的第7行可以看出,由于闭包传递,连接条件已经丢失,这种情况下,第7行的MERGE JOIN评估的行数应该与第8行显示的行数(8667K)一致才对,而这里显示的是411行

    为什么说连接条件已经丢失?为什么他们现实的行数一致才对?

    谢谢指点

  14. 熊哥,能否再解释一下我的疑问? 非常感谢

Add your comment now