这次的案例同样是一个省电信的数据库,只不过比《记一个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优化器成本计算,会对调优带来非常大的帮助。
学习了!
[回复]
学习了
[回复]
sql看了就眼熟。该是电信的OCS系统。:)
[回复]
呱唧呱唧~~~~
[回复]
这个帖子写得很好,有很高的借鉴价值
[回复]
您有没有看过那本
[回复]
不知道重新收集的时候是否收集了historgram?
如果是histogram的问题,从sql上看没有使用bind,因此这个问题要么是histogram过于陈旧,要么是cursor_sharing=force,导致bind peeking后的查询计划不正确,这时可以直接grant一把来invalid当前的plan,不知道我猜的是否对?
[回复]
老熊 回复:
10月 9th, 2009 at 10:53 下午
@dhhb, 重新收集统计信息的时候,histogram没有收集。
这里,serv_attr这个表上的条件是用的常量(字面量),没有使用绑定变量,cursor_sharing也是exact。
[回复]
哦,那应该是histogram太旧了造成的吧.如果对histogram也收集,plan应当可以走对的吧.
[回复]
老熊 回复:
10月 10th, 2009 at 12:26 下午
@dhhb, 这个表,每个月至少会分析一次。最主要的是,根据对这个表的分析,那个列上收集直方图是不太合适的。
[回复]
如果不是bug的因素,historgram在exact的情况下,应用没有使用bind,只要histogram信息准确,应该会帮助产生正确的plan,不知道老熊说的不太合适是什么意思?或者说每月分析一次的频度还不够,还要更细致,每天分析一次?呵呵,既然不分析histogram也能解决问题,那说明这个表的数据比较均匀,只是觉得只要不是bug,histogram在这种情况下应该不会产生错误的plan.
[回复]
哦,应该是serv_attr里的distinct serv_id太多>>254,导致了直方图没办法在限制内显示所有的数据频度.
[回复]
老熊 回复:
10月 11th, 2009 at 3:42 下午
@dhhb, 是ATTR_VAL列上不应该收集直方图,而不是serv_id
[回复]
呵呵,是是,打错了:)
[回复]
数据库DBA开始时没注意到我要求的是收集SERV_ATTR表的统计信息,而是按惯例收集了SERV_ACCT表的统计信息,当收集完SERV_ACCT表的统计信息之后发现执行计划仍然不正常,直到最后收集了SERV_ATTR表的统计信息之后才正常了
这一段是不是写错了表名,怎么都一样
[回复]
老熊 回复:
3月 9th, 2010 at 8:41 下午
@zabll, 这里没有错,有两张表,SERV_ACCT和SERV_ATTR表。
[回复]
看这个帖子看了2个月,不过还是有点不太明白
并且从执行计划的第7行可以看出,由于闭包传递,连接条件已经丢失,这种情况下,第7行的MERGE JOIN评估的行数应该与第8行显示的行数(8667K)一致才对,而这里显示的是411行
为什么说连接条件已经丢失?为什么他们现实的行数一致才对?
谢谢指点
[回复]
熊哥,能否再解释一下我的疑问? 非常感谢
[回复]