某省电信在做批扣（批销）时，出现严重的性能问题，发现下面这一条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")
从执行计划来看，Oracle评估出来的COST是相当的惊人（7776M），而返回的行数是2723G。看起来应该是统计信息不准确导致了选择了错误的执行计划。
对于一个SQL，如果其执行计划错误地采用于full table scan和hash join，则一般是由于评估表访问返回的数据行数过多，使其高估了成本。反之，如果是错误地采用了索引扫描，则一般是由于评估表访问返回的数据行数太少，使其低估了成本。
我们来分析这个执行计划。首先查看访问表时，Oracle评估返回的行的数目是否有偏大的情况。我们发现看以下的几行，返回的结果集行数很高：
|* 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 | | |
| 19 | TABLE ACCESS FULL | SERV | 12M| 258M| | 14070 | 1 | 63 |
| 22 | TABLE ACCESS FULL | ACCT | 11M| 239M| | 8505 | 1 | 63 |
最值得怀疑的首先是第12行和第13行。第13行是一个索引范围扫描，返回的结果集行数居然有121K行，而这个访问条件只是“access(”ACCT_BALANCE”.”ACCT_ID”=TO_NUMBER(:Z))”，从业务上来看，看起来这条SQL是查询某个帐户的余额及其他信息的。输入的条件为ACCT_ID，而一般来说，帐户余额表中，一个ACCT_ID的对应的行，只有很少的行（10行以下），而不会返回这么多行。
IDX_ACCT_BALANCE_ACCT_ID42这个索引，是一个在ACCT_BALANCE表（这是个分区表）的ACCT_ID上的全局普通索引(GLOBAL NORMAL INDEX)。检查统计信息，这个表是当天才收集的，ACCT_ID这个字段没有任何统计信息（已经被删除）。同时索引中的统计信息如下：
Index Tree Leaf Distinct Number Leaf Blocks Data Blocks Cluster
Name Unique Level Blks Keys of Rows Per Key Per Key Factor
--------------- --------- ----- ---- -------------- -------------- ----------- ----------- ------------
IDX_ACCT_BALANC NONUNIQUE 2 #### 580,079 30,742,660 1 47 27,447,720
E_ACCT_ID42
看起来索引的Distinct Keys值太小，才580,079。索引的总行数为30,742,660。重新分析该索引后，Distinct Keys的值才是正确的值：10,540,493。
再看看ACCT_BALANCE表中的ACCT_ID字段，由于没有统计信息，那么Oracle会使用一个默认的选择率（对于表的单个字段条件过滤，一般是1%）。对于这样一个3000多万行的表来说，默认的选择率造成评估的返回行数过高。由于正是出帐期间，重新分析这样一个表，影响是非常大的，而这个表之前刚分析过。我们这里可以使用DBMS_STATS.SET_COLUMN_STATS过程手工设置统计信息：
SQL> exec dbms_stats.set_column_stats(ownname=>user,tabname=>'ACCT_BALANCE',colname=>'ACCT_ID',
NULLCNT=>0,DENSITY=>1/10540393,DISTCNT=>10540393);
这里的统计信息来自于ACCT_ID字段上的索引（ACCT_ID字段定义为NOT NULL，所以比较方便地手工计算出这些统计信息）。
我们再次查看执行计划：
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 35M| 9603M| | 1472K| | |
| 1 | SORT ORDER BY | | 35M| 9603M| 20G| 1472K| | |
| 2 | NESTED LOOPS | | 35M| 9603M| | 62 | | |
| 3 | NESTED LOOPS OUTER | | 3 | 780 | | 56 | | |
| 4 | NESTED LOOPS OUTER | | 3 | 717 | | 50 | | |
| 5 | NESTED LOOPS | | 3 | 579 | | 47 | | |
| 6 | NESTED LOOPS | | 3 | 510 | | 44 | | |
| 7 | VIEW | | 3 | 498 | | 42 | | |
| 8 | SORT UNIQUE | | 3 | 248 | | 42 | | |
| 9 | UNION-ALL | | | | | | | |
|* 10 | TABLE ACCESS BY GLOBAL INDEX ROWID | ACCT_BALANCE | 2 | 102 | | 6 | ROWID | ROW L |
|* 11 | INDEX RANGE SCAN | IDX_ACCT_BALANCE_ACCT_ID42 | 3 | | | 3 | | |
| 12 | NESTED LOOPS | | 1 | 146 | | 4 | | |
|* 13 | TABLE ACCESS FULL | BALANCE_SHARE_RULE | 1 | 109 | | 2 | | |
|* 14 | TABLE ACCESS BY GLOBAL INDEX ROWID| ACCT_BALANCE | 1 | 37 | | 2 | ROWID | ROW L |
|* 15 | INDEX UNIQUE SCAN | PK_P_ACCT_BALANCE2 | 1 | | | 1 | | |
| 16 | TABLE ACCESS BY INDEX ROWID | A_BALANCE_OBJECT_TYPE | 1 | 4 | | 1 | | |
|* 17 | INDEX UNIQUE SCAN | PK_A_BALANCE_OBJECT_TYPE | 1 | | | | | |
| 18 | TABLE ACCESS BY INDEX ROWID | BALANCE_TYPE | 1 | 23 | | 1 | | |
|* 19 | INDEX UNIQUE SCAN | PK_BALANCE_TYPE | 1 | | | | | |
| 20 | TABLE ACCESS BY INDEX ROWID | A_BALANCE_ITEM_GROUP | 1 | 46 | | 1 | | |
|* 21 | INDEX UNIQUE SCAN | PK_ITEM_GROUP_ID_HN | 1 | | | | | |
| 22 | TABLE ACCESS BY GLOBAL INDEX ROWID | SERV | 1 | 21 | | 2 | ROWID | ROW L |
|* 23 | INDEX UNIQUE SCAN | PK_SERV | 1 | | | 1 | | |
| 24 | TABLE ACCESS BY GLOBAL INDEX ROWID | ACCT | 11M| 239M| | 2 | ROWID | ROW L |
|* 25 | INDEX UNIQUE SCAN | PK_ACCT | 1 | | | 1 | | |
------------------------------------------------------------------------------------------------------------------------------------
现在可以看到，执行计划已经是改善了很多，只有唯一的一个全表扫描--BALANCE_SHARE_RULE，不过那个表的行数为0。
这个时候，客户反映系统正常了。虽然Oracle在ACCT表上评估返回的行数仍然很高，但是从系统故障处理这个角度上讲目的已经达到了。接下来就是处理这个ACCT表，类似的问题，类似的过程，这里不再细述。
总结：
此次性能问题的处理，从接到客户电话，到拨号连接，到最终处理完成，不到1小时。个人认为，这个处理速度还是比较快速的。做一个如下的总结：
- 了解SQL是干什么的（也就是理解SQL的业务），非常有助于调优。
- 调优需要善于与客户和开发沟通。
- 很多时候，现在ORACLE的优化器已经非常智能和完善，SQL性能问题是由于优化统计信息不准确。使用hint那个是治标不治本。只有在非常复杂的SQL（比如有非常多的内联视图，子查询等等），才应该考虑HINT。这只是个人观点。当然还会有其他需要使用HINT才能解决的地方。
- 善用脚本，这个案例中，我再次使用了sosi.sql这个脚本来查看表的优化统计信息，能够大大节约时间，同时查看的优化统计信息非常完整。
- 善用DBMS_STATS，在这个案例中，使用set_column_stats，大大节省了收集优化统计信息的时间。
熊哥哥，厉害！！
收藏了。
呵呵。
[回复]
总结写的很到位，支持这样的文章！！
[回复]
帅就一个字,我要说N次.
[回复]
很不错 :)
[回复]
[…] 这次的案例同样是一个省电信的数据库，只不过比《记一个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’)) […]
受教了！学习！
[回复]
不错。 特别是问题定位的过程。
[回复]
讲得真是非常的清楚.牛
[回复]
好文,学习了,谢谢老熊!
[回复]
老熊，看你的执行计划感觉有点问题，是不是你的执行计划没有贴完，最外面一个HASH好像缺少一个操作对象。
谢谢
[回复]
老熊 回复:
3月 26th, 2010 at 5:59 下午
@zabll, 执行计划是完整的，最外层的HASH JOIN，也就是Id=2的那一行，这个操作是将Id=3的结果与Id=21的结果进行HASH连接。
[回复]
还有一个问题就是你判断第13步出现问题
* 13 | INDEX RANGE SCAN | IDX_ACCT_BALANCE_ACCT_ID42
但是我奇怪的是，你发现由于数据陈旧造成执行计划出现问题，你为什么再分析完索引之后还要继续对这个字段做修改？在执行计划里面我们看不到有任何计划和这个字段有关。我的意思是，只要你分析了索引就OK了
[回复]
老熊 回复:
3月 26th, 2010 at 6:27 下午
@zabll, 这个索引很显然是在ACCT_ID列上的索引：
13 – access(”ACCT_BALANCE”.”ACCT_ID”=TO_NUMBER(:Z))
对于CARD的评估，你可以注意到id=12和13的两行，看上去12行评估的返回行数居然比13行还多，这是不符合逻辑的（通过索引访问表然后再进行过滤，应该比仅通过索引访问返回的行数更少）。
值得注意的是，Oracle 9i对于使用索引的选择率的计算，是用的表上的列统计进行计算的，所以不仅仅分析索引。
[回复]
15+16的结果和11做HASH,应该是这样的吧？只是我以前一直认为HASH的话，至少要有一个是FULL扫描，但是这个里面看不到有FULL扫描，这个就是我当初读执行计划错误的地方
[回复]
老熊 回复:
3月 29th, 2010 at 10:55 上午
@zabll, 连接的row source可以来自于表，也可以来自于其他操作。
[回复]
老熊，最近一直看CBO这一块的东西，不过发现你在BLOG中的东西不是很准确，我在PUB里面给你发消息了
[回复]