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

接: http://mikixiyou.iteye.com/blog/1491153

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

接: http://mikixiyou.iteye.com/blog/1491177

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

访问对象分析

在执行计划分析过程中，我们得出 1W 行记录访问需要 7000 毫秒的疑问。如何解释这个疑问？这需要从访问的对象上去着手分析。

这个语句执行过程中，访问的对象有 CUST_INFO 表和 BRHID 上的索引这两个对象。

系统视图 user_tables 中 num_rows 和 blocks 的记录显示， cust_info 表有约 420W 条记录，约 2.5W 个数据块。这样可得出，每个数据块约存储有 17 条记录，每个记录的字节数平均值为 403 字节。对于 8KB 的数据块而言，这也是符合数据块的存储情况的。表上没有发现行迁移或行链接的记录。

索引是新建分析过的，也没有问题。

语句中查询条件 BRHID 为 ’8088’ ，我们使用下列 SQL 查询符合条件的记录存储的数据块的数目，如下：

SQL> select count(*)
  2    from (select distinct dbms_rowid.rowid_relative_fno(rowid) f,
  3                          dbms_rowid.rowid_block_number(rowid) b
  4            from cust_info_20111125
  5           where brhid = '8088');

  COUNT(*)
----------
     10688
 

表 Cust_info_20111125 是 cust_info 的完全备份表，结果显示为 10688 。这说明 brhid 等于 ’8088’ 的记录是保存在 10688 个数据块中。

一致性读的数值为 10630 ，和这个数据块的总数 10688 是接近的。

我们初步得出这样的结论。表总共有 25W 个数据块， brhid 等于 ’8088’ 的查询需要访问到 1W 多个数据块。语句执行时，一次性读取的数据块的量很大，有 745 个数据块因不在内存中而发生磁盘读，因此性能因磁盘读的存在就明显下降。

这说明，一、内存中没有缓存住所有的数据块；二、相同 brhid 的记录散落在表的各个数据块中。

执行的 SQL 很简单，问题很明显，我们如何优化呢？