前言
最近碰到一个单条SQL运行效率不佳导致数据库整体运行负载较高的问题。
分析、定位数据库的主要负载是这条语句引起的过程相对简单,通过AWR报告就可以比较容易的完成定位,这里就不赘述了。
现在直接看一下这个导致性能问题的SQL语句,其对应的SQL REPORT统计如下:
Stat Name | Statement Total | Per Execution | % Snap Total |
Elapsed Time (ms) | 363,741 | 363,740.78 | 8 .42 |
CPU Time (ms) | 362,770 | 362,770.00 | 8 .81 |
Executions | 1 | | |
Buffer Gets | 756 | 756.00 | 0.00 |
Disk Reads | 0 | 0.00 | 0.00 |
Parse Calls | 1 | 1.00 | 0.01 |
Rows | 50,825 | 50,825.00 | |
User I/O Wait Time (ms) | 0 | |
|
Cluster Wait Time (ms) | 0 | | |
Application Wait Time (ms) | 0 | | |
Concurrency Wait Time (ms) | 0 | | |
Invalidations | 0 | | |
Version Count | 1 | | |
Sharable Mem(KB) | 28 | | |
从SQL的性能指标上看,其单次执行需要6分钟左右,处理5万多条记录,逻辑度只有756,主要消耗时间在CPU上。而这里就存在疑点,逻辑读如此之低,而CPU时间花费又如此之高,那么这些CPU都消耗在哪里呢?当然这个问通过SQL的统计信息中是找不到答案的,我们下面关注SQL的执行计划:
Id | Operation | Name | Rows | Bytes | TempSpc | Cost (%CPU) | Time |
0 | SELECT STATEMENT | | | |
| 1226 (100) | |
1 | SORT ORDER BY | | 49379 | 3375K | 3888K | 1226 (2) | 00:00:05 |
2 | HASH JOIN ANTI | | 49379 | 3375K | 2272K | 401 (3) | 00:00:02 |
3 | TABLE ACCESS FULL | T_NUM | 49379 | 1687K | | 88 (4) | 00:00:01 |
4 | TABLE ACCESS FULL | T_NUM | 49379 | 1687K | | 88 (4) | 00:00:01 |
从执行计划看,Oracle选择了HASH JOIN ANTI,JOIN的两张表都是T_NUM,且都采用了全表扫描,并未选择索引。仅靠执行计划也只等得到上面的结论,至于为什么不选择索引,以及为什么执行时间过长,还需要进一步的分析。
将原SQL进行简单脱密改写后, SQL文本类似如下:
- SELECT BEGIN, END, ROWID, LENGTH(BEGIN)
- FROM T_NUM A
- WHERE NOT EXISTS (
- SELECT 1
- FROM T_NUM B
- WHERE B.BEGIN <= A.BEGIN
- AND B.END >= A.END
- AND B.ROWID != A.ROWID
- AND LENGTH(B.BEGIN) = LENGTH(A.BEGIN));
如果分析SQL语句,会发现这是一个自关联语句,在BEGIN字段长度相等的前提下,想要找到哪些不存在BEGIN比当前记录BEGIN小且END比当前记录END大的记录。
简单一点说,表中的记录表示的是由BEGIN开始到END截至的范围,那么当前想要获取的结果是找出哪些没有范围所包含的范围。需要注意的是,对于当前的SQL逻辑,如果存在两条范围完全相同的记录,那么最终这两条记录都会被舍弃。
业务的逻辑并不是特别复杂,但是要解决一条记录与其他记录进行比较,多半采用的方法是自关联,而在这个自关联中,既有大于等于又有小于等于,还有不等于,仅有的一个等于的关联条件,来自范围段BEGIN的长度的比较。
显而易见的是,如果是范围段本身的比较,其选择度一般还是不错的,但是如果只是比较其长度,那么无疑容易产生大量的重复,比如在这个例子中:
- SQL> select length(begin), count(*) from t_num group by length(begin) order by 2 desc;
-
-
-
- LENGTH(BEGIN) COUNT(*)
-
- ————- ———-
-
- 12 22096
-
- 11 9011
-
- 13 8999
-
- 14 8186
-
- 16 49
-
- 9 45
-
- 8 41
-
- 7 27
大量重复的数据出现在长度为11到14的范围上,在这种情况下,仅有的一个等值判断条件LENGTH(BEGIN)是非常低效的,这时一条记录根据这个等值条件会关联到近万条记录,设置关联到两万多条记录,显然大量的实践消耗在低效的连接过程中。
再来看一下具体的SQL语句,会发现几乎没有办法建立索引,因为LENGTH(BEGIN)的选择度非常查,而其他的条件都是不等查询,选择度也不会好,即使建立索引,强制执行选择索引,效率也不会好。
那么如果想要继续优化这个SQL,就只剩下一个办法,那就是SQL的改写。对于自关联查询而言,最佳的改写方法是利用分析函数,其强大的行级处理能力,可以在一次扫描过程中获得一条记录与其他记录的关系,从而消除了自关联的必要性。
SQL改写结果如下:
- SELECT BEGIN, OLDEND END, LENGTH(BEGIN)
- FROM (
- SELECT BEGIN, OLDEND, END, LENGTH(BEGIN), COUNT(*) OVER(PARTITION BY LENGTH(BEGIN), BEGIN, OLDEND) CN,
- ROW_NUMBER() OVER(PARTITION BY LENGTH(BEGIN), END ORDER BY BEGIN) RN
- FROM
- (
- SELECT BEGIN, END OLDEND, MAX(END) OVER(PARTITION BY LENGTH(BEGIN) ORDER BY BEGIN, END DESC) END
- FROM T_NUM
- )
- )
- WHERE RN = 1
- AND CN = 1;
简单的说,内层的分析函数MAX用来根据BEGIN从小到大,END从大到小的条件,确定每个范围对应的最大的END的值。而外层的两个分析函数,COUNT用来去掉完全重复的记录,而ROW_NUMBER用来获取范围最大的记录(也就是没有被其他记录的范围所涵盖)。
改写后,这个SQL避免对自关联,也就不存在关联条件重复值过高的性能隐患了。在模拟环境中,性能对比如下:
- SQL> SELECT BEGIN, END, ROWID, LENGTH(BEGIN)
-
- 2 FROM T_NUM A
-
- 3 WHERE NOT EXISTS (
-
- 4 SELECT 1
-
- 5 FROM T_NUM B
-
- 6 WHERE B.BEGIN <= A.BEGIN
-
- 7 AND B.END >= A.END
-
- 8 AND B.ROWID != A.ROWID
-
- 9 AND LENGTH(B.BEGIN) = LENGTH(A.BEGIN))
-
- 10 ;
-
-
-
- 48344 rows selected.
-
-
-
- Elapsed: 00:00:57.68
-
-
-
- Execution Plan
-
- ———————————————————-
-
- Plan hash value: 2540751655
-
-
-
- ————————————————————————————
-
- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-
- ————————————————————————————
-
- | 0 | SELECT STATEMENT | | 48454 | 1703K| | 275 (1)| 00:00:04 |
-
- |* 1 | HASH JOIN ANTI | | 48454 | 1703K| 1424K| 275 (1)| 00:00:04 |
-
- | 2 | TABLE ACCESS FULL| T_NUM | 48454 | 851K| | 68 (0)| 00:00:01 |
-
- | 3 | TABLE ACCESS FULL| T_NUM | 48454 | 851K| | 68 (0)| 00:00:01 |
-
- ————————————————————————————
-
-
-
- Predicate Information (identified by operation id):
-
- —————————————————
-
-
-
- 1 – access(LENGTH(TO_CHAR(“B”.”BEGIN”))=LENGTH(TO_CHAR(“A”.”BEGIN”)))
-
- filter(“B”.”BEGIN”<=”A”.”BEGIN” AND “B”.”END”>=”A”.”END” AND
-
- “B”.ROWID<>”A”.ROWID)
-
-
-
-
-
- Statistics
-
- ———————————————————-
-
- 0 recursive calls
-
- 0 db block gets
-
- 404 consistent gets
-
- 0 physical reads
-
- 0 redo size
-
- 2315794 bytes sent via SQL*Net to client
-
- 35966 bytes received via SQL*Net from client
-
- 3224 SQL*Net roundtrips to/from client
-
- 0 sorts (memory)
-
- 0 sorts (disk)
-
- 48344 rows processed
-
-
-
- SQL> SELECT BEGIN, OLDEND END, LENGTH(BEGIN)
-
- 2 FROM (
-
- 3 SELECT BEGIN, OLDEND, END, LENGTH(BEGIN), COUNT(*) OVER(PARTITION BY LENGTH(BEGIN), BEGIN, OLDEND) CN,
-
- 4 ROW_NUMBER() OVER(PARTITION BY LENGTH(BEGIN), END ORDER BY BEGIN) RN
-
- 5 FROM
-
- 6 (
-
- 7 SELECT BEGIN, END OLDEND, MAX(END) OVER(PARTITION BY LENGTH(BEGIN) ORDER BY BEGIN, END DESC) END
-
- 8 FROM T_NUM
-
- 9 )
-
- 10 )
-
- 11 WHERE RN = 1
-
- 12 AND CN = 1;
-
-
-
- 48344 rows selected.
-
-
-
- Elapsed: 00:00:00.72
-
-
-
- Execution Plan
-
- ———————————————————-
-
- Plan hash value: 1546715670
-
-
-
- ——————————————————————————————
-
- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-
- ——————————————————————————————
-
- | 0 | SELECT STATEMENT | | 48454 | 2460K| | 800 (1)| 00:00:10 |
-
- |* 1 | VIEW | | 48454 | 2460K| | 800 (1)| 00:00:10 |
-
- |* 2 | WINDOW SORT PUSHED RANK| | 48454 | 1845K| 2480K| 800 (1)| 00:00:10 |
-
- | 3 | WINDOW BUFFER | | 48454 | 1845K| | 800 (1)| 00:00:10 |
-
- | 4 | VIEW | | 48454 | 1845K| | 311 (1)| 00:00:04 |
-
- | 5 | WINDOW SORT | | 48454 | 662K| 1152K| 311 (1)| 00:00:04 |
-
- | 6 | TABLE ACCESS FULL | T_NUM | 48454 | 662K| | 68 (0)| 00:00:01 |
-
- ——————————————————————————————
-
-
-
- Predicate Information (identified by operation id):
-
- —————————————————
-
-
-
- 1 – filter(“RN”=1 AND “CN”=1)
-
- 2 – filter(ROW_NUMBER() OVER ( PARTITION BY LENGTH(TO_CHAR(“BEGIN”)),”END”
-
- ORDER BY “BEGIN”)<=1)
-
-
-
-
-
- Statistics
-
- ———————————————————-
-
- 0 recursive calls
-
- 0 db block gets
-
- 202 consistent gets
-
- 0 physical reads
-
- 0 redo size
-
- 1493879 bytes sent via SQL*Net to client
-
- 35966 bytes received via SQL*Net from client
-
- 3224 SQL*Net roundtrips to/from client
-
- 3 sorts (memory)
-
- 0 sorts (disk)
-
- 48344 rows processed
原SQL运行时间接近1分钟,而改写后的SQL语句只需要0.72秒,执行时间变为原本的1/80,逻辑读减少一半。
总结
以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对w3xue的支持。