这是一个开发和 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 |
|* 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
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
这个结果虽然看上去很复杂,但我们可以化繁为简,一点一点地去看。