如何利用分析函数改写范围判断自关联查询详解
前言
最近碰到一个单条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,逻辑读减少一半。
总结
以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对的支持。
下一篇: 女人保养应趁早 祛皱恢复肌肤弹性