伏龙阁资源网

如何利用分析函数改写范围判断自关联查询详解
站长资源 2024/11/22 佚名
2 0

前言

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

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对的支持。

广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除！
www.wwsws.com 伏龙阁资源网
39,976影音资源
44,792技术资源
21,817软件资源
651,128站长资源

更新日志

友情链接

杰晶网络 DDR爱好者之家 桃源资源网 杰网资源 富贵资源网 南强小屋 铁雪资源网 幽灵资源网 万梅资源网 狼山资源网 白云岛资源网 昆仑资源网 相思资源网 明霞山资源网 内蒙古资源网 黑松山资源网 茶园资源网 饿虎岗资源网 大旗谷资源网 常春岛资源网 岱庙资源网 兴国资源网 快活林资源网 蝙蝠岛资源网 帝王谷资源网 白云城资源网 伏龙阁资源网 清风细雨楼 天枫庄资源网 圆月山庄资源网 无争山庄资源网 神水资源网 移花宫资源网 神剑山庄资源网 无为清净楼资源网 金钱帮资源网 丐帮资源网 华山资源网 极乐门资源网 小李飞刀资源网 凤求凰客栈 风云阁资源网 金狮镖局 鸳鸯亭资源网 千金楼资源网 更多链接