业务人员反映系统很慢。登录操作系统，用TOP命令查看，发现大量的CPU都飙升到了100%.根据进程PID一步步找到执行的SQL，都是一个SQL导致的，如下：
SELECT AB14.BAE204 AB14_BAE204,
AB14.BAE214 AB14_BAE214,
AB14.BAE203 AB14_BAE203,
AB13.BAZ010 AB14_AAZ010,
AB13.BAE265 AB14_BAE165,
AB14.BAE006 AB14_BAE006,
(CASE
WHEN AAE140 IN ('31', '35', '37', '3A', '3B') THEN
'31'
ELSE
AAE140
END) AB14_AAE140,
SUM(NVL(AB14.BAE101, 0)) AB14_BAE101,
MAX(TRUNC(AB14.AAB191, 'DD')) AB14_AAB191,
MAX(AB14.AAE011) AB14_AAE011
FROM AB14, AB13
WHERE AB14.BAE204 = AB13.BAE204
AND NVL(BAE171, '0') = '1'
AND NVL(BAE205, 0) = 0
AND AB13.BAE265 = '1'
AND AB14.AAE140 = '11'
AND AB13.AAE036 >= TO_DATE('2010-06-01', 'YYYY-MM-DD')
AND AB13.AAE036 < TO_DATE('2010-06-24', 'YYYY-MM-DD') + 1
GROUP BY AB14.BAE204,
AB14.BAE214,
AB14.BAE203,
AB13.BAZ010,
AB13.BAE265,
AB14.BAE006,
(CASE
WHEN AAE140 IN ('31', '35', '37', '3A', '3B') THEN
'31'
ELSE
AAE140
END)
;
查看执行计划：
执行计划
----------------------------------------------------------
Plan hash value: 2048427295
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 99 | 8 (13)| 00:00:01 |
| 1 | HASH GROUP BY | | 1 | 99 | 8 (13)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 99 | 7 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| AB13 | 1 | 22 | 4 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_AB13_AAE036 | 1 | | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| AB14 | 1 | 77 | 3 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IND_AB14 | 2 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("AB13"."BAE265"='1')
4 - access("AB13"."AAE036">=TO_DATE(' 2010-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "AB13"."AAE036" 6 - access(NVL("BAE205",0)=0 AND NVL("BAE171",'0')='1' AND "AB14"."AAE140"='11' AND
"AB14"."BAE204"="AB13"."BAE204")
从执行计划来看，这个SQL不应该出现性能问题，莫非是CBO评估出现了问题。给SQL增加HINT/*+ gather_plan_statistics */，再次执行。执行了18: 42.75，将近19分钟。
SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
Plan hash value: 2048427295
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------
| 1 | HASH GROUP BY | | 1 | 1 | 730 |00:18:14.73 | 16M| 3 |
| 2 | NESTED LOOPS | | 1 | 1 | 2974 |00:19:03.76 | 16M| 3 |
|* 3 | TABLE ACCESS BY INDEX ROWID| AB13 | 1 | 1 | 9790 |00:00:00.24 | 13301 | 3 |
|* 4 | INDEX RANGE SCAN | IDX_AB13_AAE036 | 1 | 1 | 21607 |00:00:00.06 | 125 | 0 |
|* 5 | TABLE ACCESS BY INDEX ROWID| AB14 | 9790 | 1 | 2974 |00:18:02.19 | 16M| 0 |
|* 6 | INDEX RANGE SCAN | IND_AB14 | 9790 | 2 | 582M|00:09:43.72 | 2398K| 0 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("AB13"."BAE265"='1')
4 - access("AB13"."AAE036">=TO_DATE(' 2010-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"AB13"."AAE036" 5 - filter("AB14"."BAE204"="AB13"."BAE204")
6 - access("AB14"."SYS_NC00031$"=0 AND "AB14"."SYS_NC00032$"='1' AND "AAE140"='11')
匪夷所思的事情发生了：
1）看ID=6的A-Rows列，实际通过索引IND_AB14返回的行数高达582M行。而评估出来的只有两条。查看统计信息都不缺失。
2）IND_AB14是一个函数索引。创建语句如下：create index IND_AB14 on AB14 (NVL(BAE205,0), NVL(BAE171,'0'), AAE140)，但是看执行计划的谓词部分（id为6的行），"AB14"."SYS_NC00031$"=0 AND "AB14"."SYS_NC00032$"='1'从何而来啊，索引里是没有这两个列的信息的。貌似SYS_NC00031$对于的是NVL("BAE205",0)，SYS_NC00032$对于的是NVL("BAE171",'0')。
3）我用语句查询了一下，根据索引应该返回的行数。
SQL> SELECT COUNT(*) FROM ab14 WHERE NVL(BAE171, '0') = '1' AND NVL(BAE205, 0) = 0 AND aae140='11';
COUNT(*)
----------
59623
只有不到六万的数据量，怎么实际返回了582M的数据。
不过从执行计划ID=5行可以看出根据"AB14"."BAE204"="AB13"."BAE204"可以过滤掉大量数据，于是修改了之前的函数索引增加了BAE204这个字段。
create index IND_AB14 on AB14 (NVL(BAE205,0), NVL(BAE171,'0'), AAE140,bae204)
修改索引后，重新执行，只花了2.3秒。
Plan hash value: 1600515523
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------
| 1 | HASH GROUP BY | | 1 | 1 | 743 |00:00:00.37 | 33700 | 55 |
| 2 | NESTED LOOPS | | 1 | 1 | 3073 |00:00:00.31 | 33700 | 55 |
|* 3 | TABLE ACCESS BY INDEX ROWID| AB13 | 1 | 1 | 9803 |00:00:00.06 | 13282 | 0 |
|* 4 | INDEX RANGE SCAN | IDX_AB13_AAE036 | 1 | 1 | 21624 |00:00:00.02 | 108 | 0 |
| 5 | TABLE ACCESS BY INDEX ROWID| AB14 | 9803 | 1 | 3073 |00:00:00.27 | 20418 | 55 |
|* 6 | INDEX RANGE SCAN | IND_AB14_BAE204 | 9803 | 1 | 3073 |00:00:00.25 | 19619 | 55 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("AB13"."BAE265"='1')
4 - access("AB13"."AAE036">=TO_DATE(' 2010-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"AB13"."AAE036" 6 - access("AB14"."SYS_NC00031$"=0 AND "AB14"."SYS_NC00032$"='1' AND "AAE140"='11' AND
"AB14"."BAE204"="AB13"."BAE204"
CBO评估E-Rows依然非常不准确，重新分析表两个表(仅提供一个AB14的,AB13的略)：
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'NCSI',
TABNAME => 'AB14',
ESTIMATE_PERCENT => 100,
METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY',
CASCADE => TRUE,
DEGREE => 10);
END;
重新执行查看执行计划：
Plan hash value: 1600515523
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------
| 1 | HASH GROUP BY | | 1 | 1 | 743 |00:00:00.23 | 33709 | 2 |
| 2 | NESTED LOOPS | | 1 | 1 | 3073 |00:00:00.20 | 33709 | 2 |
|* 3 | TABLE ACCESS BY INDEX ROWID| AB13 | 1 | 1 | 9806 |00:00:00.06 | 13285 | 1 |
|* 4 | INDEX RANGE SCAN | IDX_AB13_AAE036 | 1 | 1 | 21628 |00:00:00.02 | 108 | 0 |
| 5 | TABLE ACCESS BY INDEX ROWID| AB14 | 9806 | 1 | 3073 |00:00:00.11 | 20424 | 1 |
|* 6 | INDEX RANGE SCAN | IND_AB14_BAE204 | 9806 | 1 | 3073 |00:00:00.09 | 19625 | 0 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("AB13"."BAE265"='1')
4 - access("AB13"."AAE036">=TO_DATE(' 2010-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"AB13"."AAE036" 6 - access("AB14"."SYS_NC00031$"=0 AND "AB14"."SYS_NC00032$"='1' AND "AAE140"='11' AND
"AB14"."BAE204"="AB13"."BAE204")
几乎没任何改善。评估出来的依然很差。
[ 本帖最后由 wei-xh 于 2010-6-24 17:12 编辑 ]
SELECT AB14.BAE204 AB14_BAE204,
AB14.BAE214 AB14_BAE214,
AB14.BAE203 AB14_BAE203,
AB13.BAZ010 AB14_AAZ010,
AB13.BAE265 AB14_BAE165,
AB14.BAE006 AB14_BAE006,
(CASE
WHEN AAE140 IN ('31', '35', '37', '3A', '3B') THEN
'31'
ELSE
AAE140
END) AB14_AAE140,
SUM(NVL(AB14.BAE101, 0)) AB14_BAE101,
MAX(TRUNC(AB14.AAB191, 'DD')) AB14_AAB191,
MAX(AB14.AAE011) AB14_AAE011
FROM AB14, AB13
WHERE AB14.BAE204 = AB13.BAE204
AND NVL(BAE171, '0') = '1'
AND NVL(BAE205, 0) = 0
AND AB13.BAE265 = '1'
AND AB14.AAE140 = '11'
AND AB13.AAE036 >= TO_DATE('2010-06-01', 'YYYY-MM-DD')
AND AB13.AAE036 < TO_DATE('2010-06-24', 'YYYY-MM-DD') + 1
GROUP BY AB14.BAE204,
AB14.BAE214,
AB14.BAE203,
AB13.BAZ010,
AB13.BAE265,
AB14.BAE006,
(CASE
WHEN AAE140 IN ('31', '35', '37', '3A', '3B') THEN
'31'
ELSE
AAE140
END)
;
查看执行计划：
执行计划
----------------------------------------------------------
Plan hash value: 2048427295
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 99 | 8 (13)| 00:00:01 |
| 1 | HASH GROUP BY | | 1 | 99 | 8 (13)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 99 | 7 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| AB13 | 1 | 22 | 4 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_AB13_AAE036 | 1 | | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| AB14 | 1 | 77 | 3 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IND_AB14 | 2 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("AB13"."BAE265"='1')
4 - access("AB13"."AAE036">=TO_DATE(' 2010-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "AB13"."AAE036"
"AB14"."BAE204"="AB13"."BAE204")
从执行计划来看，这个SQL不应该出现性能问题，莫非是CBO评估出现了问题。给SQL增加HINT/*+ gather_plan_statistics */，再次执行。执行了18: 42.75，将近19分钟。
SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
Plan hash value: 2048427295
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------
| 1 | HASH GROUP BY | | 1 | 1 | 730 |00:18:14.73 | 16M| 3 |
| 2 | NESTED LOOPS | | 1 | 1 | 2974 |00:19:03.76 | 16M| 3 |
|* 3 | TABLE ACCESS BY INDEX ROWID| AB13 | 1 | 1 | 9790 |00:00:00.24 | 13301 | 3 |
|* 4 | INDEX RANGE SCAN | IDX_AB13_AAE036 | 1 | 1 | 21607 |00:00:00.06 | 125 | 0 |
|* 5 | TABLE ACCESS BY INDEX ROWID| AB14 | 9790 | 1 | 2974 |00:18:02.19 | 16M| 0 |
|* 6 | INDEX RANGE SCAN | IND_AB14 | 9790 | 2 | 582M|00:09:43.72 | 2398K| 0 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("AB13"."BAE265"='1')
4 - access("AB13"."AAE036">=TO_DATE(' 2010-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"AB13"."AAE036"
6 - access("AB14"."SYS_NC00031$"=0 AND "AB14"."SYS_NC00032$"='1' AND "AAE140"='11')
1）看ID=6的A-Rows列，实际通过索引IND_AB14返回的行数高达582M行。而评估出来的只有两条。查看统计信息都不缺失。
2）IND_AB14是一个函数索引。创建语句如下：create index IND_AB14 on AB14 (NVL(BAE205,0), NVL(BAE171,'0'), AAE140)，但是看执行计划的谓词部分（id为6的行），"AB14"."SYS_NC00031$"=0 AND "AB14"."SYS_NC00032$"='1'从何而来啊，索引里是没有这两个列的信息的。貌似SYS_NC00031$对于的是NVL("BAE205",0)，SYS_NC00032$对于的是NVL("BAE171",'0')。
3）我用语句查询了一下，根据索引应该返回的行数。
SQL> SELECT COUNT(*) FROM ab14 WHERE NVL(BAE171, '0') = '1' AND NVL(BAE205, 0) = 0 AND aae140='11';
COUNT(*)
----------
59623
只有不到六万的数据量，怎么实际返回了582M的数据。
不过从执行计划ID=5行可以看出根据"AB14"."BAE204"="AB13"."BAE204"可以过滤掉大量数据，于是修改了之前的函数索引增加了BAE204这个字段。
create index IND_AB14 on AB14 (NVL(BAE205,0), NVL(BAE171,'0'), AAE140,bae204)
修改索引后，重新执行，只花了2.3秒。
Plan hash value: 1600515523
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------
| 1 | HASH GROUP BY | | 1 | 1 | 743 |00:00:00.37 | 33700 | 55 |
| 2 | NESTED LOOPS | | 1 | 1 | 3073 |00:00:00.31 | 33700 | 55 |
|* 3 | TABLE ACCESS BY INDEX ROWID| AB13 | 1 | 1 | 9803 |00:00:00.06 | 13282 | 0 |
|* 4 | INDEX RANGE SCAN | IDX_AB13_AAE036 | 1 | 1 | 21624 |00:00:00.02 | 108 | 0 |
| 5 | TABLE ACCESS BY INDEX ROWID| AB14 | 9803 | 1 | 3073 |00:00:00.27 | 20418 | 55 |
|* 6 | INDEX RANGE SCAN | IND_AB14_BAE204 | 9803 | 1 | 3073 |00:00:00.25 | 19619 | 55 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("AB13"."BAE265"='1')
4 - access("AB13"."AAE036">=TO_DATE(' 2010-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"AB13"."AAE036"
"AB14"."BAE204"="AB13"."BAE204"
CBO评估E-Rows依然非常不准确，重新分析表两个表(仅提供一个AB14的,AB13的略)：
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'NCSI',
TABNAME => 'AB14',
ESTIMATE_PERCENT => 100,
METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY',
CASCADE => TRUE,
DEGREE => 10);
END;
重新执行查看执行计划：
Plan hash value: 1600515523
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------
| 1 | HASH GROUP BY | | 1 | 1 | 743 |00:00:00.23 | 33709 | 2 |
| 2 | NESTED LOOPS | | 1 | 1 | 3073 |00:00:00.20 | 33709 | 2 |
|* 3 | TABLE ACCESS BY INDEX ROWID| AB13 | 1 | 1 | 9806 |00:00:00.06 | 13285 | 1 |
|* 4 | INDEX RANGE SCAN | IDX_AB13_AAE036 | 1 | 1 | 21628 |00:00:00.02 | 108 | 0 |
| 5 | TABLE ACCESS BY INDEX ROWID| AB14 | 9806 | 1 | 3073 |00:00:00.11 | 20424 | 1 |
|* 6 | INDEX RANGE SCAN | IND_AB14_BAE204 | 9806 | 1 | 3073 |00:00:00.09 | 19625 | 0 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("AB13"."BAE265"='1')
4 - access("AB13"."AAE036">=TO_DATE(' 2010-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"AB13"."AAE036"
"AB14"."BAE204"="AB13"."BAE204")
几乎没任何改善。评估出来的依然很差。
[ 本帖最后由 wei-xh 于 2010-6-24 17:12 编辑 ]
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/22034023/viewspace-666125/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
反转索引的几个缺点。
下一篇：
医院端的数据库启动不了之解决。
请登录后发表评论
登录
全部评论
