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

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

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

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

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

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

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

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

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

测试

我们在新建的表 cust_info­_bk2 进行测试。新表的记录是按照 brhid 排序插入，相同 brhid 的记录就近可能地保存在相邻的数据块上。

我们使用 brhid=’8088’ 的条件进行测试，符合这个条件的记录有 1.1W 条，占用的存储空间的数据块有 686 个。测试的操作过程如下所示：

SQL> set timing on

SQL> set autotrace trace exp stat

SQL>   SELECT COUNT(*)

    FROM CUST_INFO_bk2 t1

   WHERE 1 = 1

     and t1.lockflag = 0

     and T1.ASSET >= 50001

     and t1.brhid = '8088';  2    3    4    5    6 

 Elapsed: 00:00:00.72

 Execution Plan

----------------------------------------------------------

Plan hash value: 974315855

 

--------------------------------------------------------------------------------

---------------------

 

| Id  | Operation                     | Name                | Rows  | Bytes | Co

st (%CPU)| Time     |

 

--------------------------------------------------------------------------------

---------------------

 

|   0 | SELECT STATEMENT              |                     |     1 |    20 |

391   (2)| 00:00:05 |

 

|   1 |  SORT AGGREGATE               |                     |     1 |    20 |

         |          |

 

|*  2 |   TABLE ACCESS BY INDEX ROWID | CUST_INFO_BK2       |     6 |   120 |

391   (2)| 00:00:05 |

 

|   3 |    BITMAP CONVERSION TO ROWIDS|                     |       |       |

         |          |

 

|*  4 |     BITMAP INDEX SINGLE VALUE | IND_CUST_INFO_BK2_1 |       |       |

         |          |

 

--------------------------------------------------------------------------------

---------------------

 

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter("T1"."ASSET">=50001 AND TO_NUMBER("T1"."LOCKFLAG")=0)

   4 - access("T1"."BRHID"='8088')

 

Statistics

----------------------------------------------------------

        332  recursive calls

          0  db block gets

        723  consistent gets

        689  physical reads

          0  redo size

        516  bytes sent via SQL*Net to client

        492  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          5  sorts (memory)

          0  sorts (disk)

          1  rows processed

从分析结果中，可以很明确地看到，执行计划没有任何变化，磁盘读和逻辑读下降到 689 和 723 。

注：这里的物理读也是 600 多，和上次取样的物理读差不多，为什么时间差异这么大？难道是磁盘的 IO 性能在两个时间段有很大的差异？

总而言之，语句执行所需要的数据块是大量降低了， 700 个数据块的请求，执行时间比 1W 个是要相差很大的。因此生产环境优化操作按照这个方法进行优化，应该是有明显收益。

语句执行的读取的数据块的数量我们通过重组表的方式使其减少了。这样即使这些块都在内存里，也比没重组前也是要快的。这个完全是可以成线性关系的，同在内存里，读取的数据块少，时间就会短。

因为需要调整初始化参数，所以这里就没有测试缓存表的优化方案了。

 

实施

根据测试结果，我们实施的主要内容是将 cust_info 的表的记录按照 brhid 排序重组。

实施步骤如下：

1.         备份表的数据，使用方法 CTAS

2.         备份表及其索引的 DDL 语句

3.         删除源表

4.         根据 DDL 重建表和索引

5.         使用 insert into select order by 方式插入记录数

6.         分析表和索引

在实施过程中因为网络不稳定，导致操作中断，所以我们采用了 ”BUCK INTO” 技术，即避免 CTAS 类似操作的大回滚段的操作压力和风险，又避免单条插入的性能问题。

该技术实现方式如下：

 declare

  cursor c_source is

    select * from cust_info_20111125;

  type type_arr_fectch is table of c_source%rowtype;

  arr_source type_arr_fectch;

begin

  open c_source;

  loop

    fetch c_source bulk collect

      into arr_source limit 1000;

    forall i in 1 .. arr_source.count

      insert into cust_info values arr_source (i);

    commit;

    exit when c_source%notfound;

  end loop;

  close c_source;

  commit;

end;

该方式实现每 1000 条插入一次，是按块的方式写入数据库的，速度是传统的单条插入的 5 倍以上。

总结

这个优化方案中涉及到众多知识点。理解这些知识点，更能有利于我们开发出更高效的程序。现将知识点罗列如下：

1.         数据块结构

2.         BITMAP 索引

3.         SQL 中判断条件

4.         输入变量的类型和字段类型必须一致；

a)         判断字段上不做任何转换如将 date 转换成 char 再比较；

5.         Forall bulk connect  技术实现批量快速插入