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

这是一个开发和  DBA   双方合作下，从开发的角度去思考如何优化  SQL   的完整的过程。在本文中，介绍如何阅读执行计划、如何理解位图索引、如果使用  10046 event   分析、记录是如何保存在数据块中。相信这篇文章能帮助你理解数据库的数据块结构、理解  SQL   的执行计划。希望能对大家有帮助。

 

事情的过程是这样的。应用系统的某一个查询功能无法出结果，导致了某关键业务不能完成。在开发人员的协助下，我们将该查询功能调用的  SQL   语句取出来。如下所示：

SELECT COUNT(*)

   FROM CUST_INFO   t1 

   WHERE 1 = 1

    and t1.lockflag = 0

    and T1.ASSET >= 50000

    and T1.MOBILE IS NOT NULL

    and t1.brhid='8088' ；

这个  SQL   中的  Brhid   ，  lockflag   ，  asset   都是输入变量。

从  SQL   结构上看，它属于统计类型的查询语句，这里是根据  brhid (   营业部代码  )   进行分组求和。

首先，我们在生产环境和测试环境对这个  SQL   做验证性测试，以证实客户说法是否属实。

测试结果显示它第一次执行很慢，时间超过  20   秒，甚至  100   秒。而第二次、第三次后就变快。每换一个  brhid   的值，执行速度又从慢到快。

在  WEB   类型的应用中，超过  20   秒以上响应时间，是不可接受的。其实，对于用户而言，这个功能已经完全不能用了。

SQL   看上去非常简单，这个能从什么方面着手优化呢？

分析

语句分析

在数据库系统中，任何一个  SQL   出现的性能问题，首先是分析它的执行计划和统计信息。

因为这个  SQL   的现象是可以问题重现的，所以我们使用  ”set autotrace trace exp stat”   来进行分析。

打开  cmd   窗口，执行  SQLPLUS   ，登录数据库后，执行  set autotrace   ，如下所示：

set autotrace trace exp stat

set timing on

这句是将  trace   功能打开，这是  sqlplus   的一个调试功能。然后执行我们要分析的  SQL   语句。

结果如下：

Execution Plan

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

Plan hash value: 840048105

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

| 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 |     |     |      |           |

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

 

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' )

Statistics

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

           1   recursive calls

           0   db block gets

       10630   consistent gets

         745   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

           0   sorts (memory)

           0   sorts (disk)

           1   rows processed

这个结果虽然看上去很复杂，但我们可以化繁为简，一点一点地去看。