一个分组统计SQL的优化过程(2)

我们对这个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 。

       10630   consistent gets

         745   physical reads

 

745 个物理读， 10630 个一致性读。如果按照每个物理读消耗 10 毫秒，则这些物理读就需要约 7 秒的时间。

我们分析的执行计划是第一次查询操作时生成的执行计划。在第二次执行时， SQL 快是因为没有物理读，所有记录都缓存在内存中。

我们有个疑问，为什么读取个 1W 行记录的操作，需要 7000 毫秒时间？