利用分析函数改写范围判断自关联查询

栏目: 数据库 · 发布时间: 5年前

内容简介:最近碰到一个单条SQL运行效率不佳导致数据库整体运行负载较高的问题。分析、定位数据库的主要负载是这条语句引起的过程相对简单,通过AWR报告就可以比较容易的完成定位,这里就不赘述了。现在直接看一下这个导致性能问题的SQL语句,其对应的SQL REPORT统计如下:

最近碰到一个单条 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,逻辑读减少一半。


以上所述就是小编给大家介绍的《利用分析函数改写范围判断自关联查询》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

Designing with Web Standards (2nd Edition)

Designing with Web Standards (2nd Edition)

Jeffrey Zeldman / Peachpit Press / 2006-07-06 / USD 44.99

Best-selling author, designer, and web standards evangelist Jeffrey Zeldman has updated his classic, industry-shaking guidebook. This new edition--now in full color--covers improvements in best prac......一起来看看 《Designing with Web Standards (2nd Edition)》 这本书的介绍吧!

JS 压缩/解压工具
JS 压缩/解压工具

在线压缩/解压 JS 代码

CSS 压缩/解压工具
CSS 压缩/解压工具

在线压缩/解压 CSS 代码

RGB转16进制工具
RGB转16进制工具

RGB HEX 互转工具