我们对这个SQL执行做抽丝剥茧的分析。 (接一个分组统计SQL的优化过程(1) http://mikixiyou.iteye.com/blog/1491153 )
分析执行计划
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 540 (1)| 00:00:07 |
| 1 | SORT AGGREGATE | | 1 | 21 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID | CUST_INFO | 1866 | 39186 | 540 (1)|00:00:07 |
| 3 | BITMAP CONVERSION TO ROWIDS| | | | | |
|* 4 | BITMAP INDEX SINGLE VALUE|IND_CUST_INFO_5 | | | | |
执行计划是 SQL 在数据库系统的执行轨迹,是从下向上,从右向左,从里向外的执行。
因此,我们首先看 ID 为 4 的这行操作记录。
在对象“ ND_CUST_INFO_5 “上执行“ BITMAP INDEX SINGLE VALUE ”操作。这个对象根据 dba_objects 可以查出它是一个位图索引,而操作是取出一个符合条件的位图。
位图索引的结构我这里先大概描述一下。
位图索引的物理结构和普通索引一样,也是 B-tree 结构。它存储的数据记录的逻辑结构为“ key_value,start_rowid,end_rowid,bitmap “。
其内容类似这样:
“ ’8088’,00000000000,10000034441,1001000100001111000 “
Bitmap 是一个二进制,表示 START_ROWID 到 END_ROWID 的记录, 1 表示等于 key_value 即‘ 8088 ‘的 ROWID 记录, 0 则表示不是这个记录。
我们了解 bitmap 的结构,就很容易理解,根据 key_value ,系统遍历一下,就能找到符合条件的记录的 ROWID 。
执行计划中 ID 为“ 3 ”的行,就是这种转换过程,转换出来的 ROWID 就是符合 BRHID=’8088’ 的记录的 ROWID 。
结果集就是这个语句“ select ROWID from cust_info where brhid=’8088’; “的执行结果。
在 ID 为“ 4 ”的那行,我们还注意到,“ 4 ”前面加了个“ * ”。此“ * “表示它有条件判断。
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ASSET">50000 AND "MOBILE" IS NOT NULL AND TO_NUMBER("T1"."LOCKFLA
G")=0)
4 - access("BRHID"='8088')
根据位图索引的结构,只要 key_value 的值很少,那么位图索引里保存的记录就会很小。业务上这里保存营业部代码,表中营业部代码也只有几百个,所以位图索引里的记录数就很小。
执行计划遍历位图索引时,只需要扫描有限的几个数据块。
位图索引中的 ROWID 去查找 CUST_INFO 表,得出所有 ROWID 相同的表的数据记录。此记录再按照过滤条件“ filter("ASSET">50000 AND "MOBILE" IS NOT NULL AND TO_NUMBER("T1"."LOCKFLAG")=0) “ 进行过滤,即得出所求的结果集。
大家注意一下:
这里的过滤条件,系统做了一个 to_number 的转换。表的字段 lockflag 数据类型为 varchar ,参数值却是 number ,所以不得不转换。
这种写法也是不合理的,额外增加 CPU 运行。若此字段上有索引,如此操作也将导致索引不会被使用。
回到执行计划中,可看出 rows 一列中,显示符合条件的记录数为 1866 条,但执行时间为 7 秒。
这个时间是指过滤操作和 ROWID 查找的时间之和。
在位图索引中得出的 ROWID 数量和“ select count(*) from cust_info where brhid=’8088’; “查询结果相等,约 1W 条,查询出来的结果将过滤成 1866 条记录。总计时间为 7 秒。
对于 WEB 应用的数据库而言, 7 秒种就是 7,000,000 微秒。这是一个非常漫长的时间。
正常情况下,一个数据块从内存读取所需的时间约是 10 微秒到 100 微秒不等,从磁盘读取所需的时间约 10 毫秒左右。若磁盘读超过 10 毫秒,则表示磁盘很忙或磁盘性能很差。
注:这个数值是我估计的,不一定正确,具体值还是要看环境。
最后一步是将将得到的记录进行聚合统计。
执行计划为
| 1 | SORT AGGREGATE | | 1 | 21 | | |
这步很简单,仅是将过滤出来的 1866 条记录做记录数求和,使用的时间应该是微秒级别的,以至于这里都没有显示。
经过以上对执行计划的分析,我们可以判定,语句执行在“ TABLE ACCESS BY INDEX ROWID ”操作上消耗了绝大部分的时间。
这个操作是根据 ROWID 来读取记录,一共读取记录数约 1W 多条,使用时间为 7 秒钟。我们算一下,每条时间约 739 微秒。
这个 SQL 的运行成本统计信息显示一致性读( consistent gets
,也叫内存读)为 10630*8KB ,物理读(磁盘读)为 745*8KB 。
745 个物理读, 10630 个一致性读。如果按照每个物理读消耗 10 毫秒,则这些物理读就需要约 7 秒的时间。
我们分析的执行计划是第一次查询操作时生成的执行计划。在第二次执行时, SQL 快是因为没有物理读,所有记录都缓存在内存中。
我们有个疑问,为什么读取个 1W 行记录的操作,需要 7000 毫秒时间?