Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1369677
  • 博文数量: 172
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 3831
  • 用 户 组: 普通用户
  • 注册时间: 2011-03-30 13:00
个人简介

About me:Oracle ACE pro,optimistic,passionate and harmonious. Focus on ORACLE,MySQL and other database programming,peformance tuning,db design, j2ee,Linux/AIX,Architecture tech,etc

文章分类

全部博文(172)

文章存档

2024年(27)

2023年(28)

2022年(43)

2020年(62)

2014年(3)

2013年(9)

分类: Oracle

2022-04-14 11:19:14


接:深入了解SQL性能杀手FILTER操作_PART4

1.4 数据物理分布对FILTER节点执行次数的影响

         案例如下:

--t2表中插入1024行数据

DROP TABLE t2;

CREATE TABLE t2 AS SELECT LEVEL ID FROM dual CONNECT BY LEVEL<=1024;

--反复执行5insert into

INSERT INTO t2 SELECT*FROM t2;

COMMIT;


对应的SQL如下:


SELECT COUNT(*)

FROM   t2

WHERE ID NOT IN

(SELECT ID

FROM(SELECT ID

FROM   t2

ORDER BY ID DESC)

WHERE ROWNUM<=100);


  以上SQL的含义很简单,也就是对T2表中的ID降序排列,查询不在前100的数据量,然而执行却比正常的慢很多(这里作为演示,只是构造少量数据,实际数据量很多,SQL执行非常慢)。执行计划如下所示:



      通过前面有关FILTER内容可以知道,表T2ID只有1024个不同值,由于FILTER有缓存,那么这个子节点正常的执行次数应该是1024次,但是上述执行计划的执行次数却是12432次,子节点的执行次数增加N倍是导致SQL变慢的主要原因。

       解决这个问题,主要要搞清楚为什么执行次数不是1024次,在实际的应用中,表是按天分表的,每天的数据累计到前一天,执行计划未变,但是突然某一天变慢,通过分析,出现这种情况的原因,大概率是数据的分布不同。由于这里是按照ID构造HASH表,可以测试下按照ID顺序重新组织是什么情况。


DROP TABLE t3;

CREATE TABLE t3 AS SELECT*FROM t2 ORDER BY ID;--按照关联列ID重新组织顺序


执行下列语句:

SELECT COUNT(*)

FROM   t3

WHERE ID NOT IN

(SELECT ID

FROM(SELECT ID

FROM   t3

ORDER BY ID DESC)WHERE ROWNUM<=100);


执行计划如下:

      现在执行计划中子节点执行次数是1024次了,的确是数据的物理分布原因,看来FITER算法还不够完善,这点值得注意。

      如果要进一步优化上述语句,可以通过改写成NOT EXISTS避免FILTER来提高效率,改写如下:

SELECT COUNT(*)

FROM   t3 a

WHERE not exists

(SELECT 1 from

(SELECT ID

FROM(SELECT ID

FROM   t3

ORDER BY ID DESC)WHERE ROWNUM<=100) b

where a.ID = b.ID

);


执行计划如下:


通过执行计划可以看出,改写为NOT EXISTS后走HASH JOIN效率得到极大提升。


1.5 总结

      通过本文可以了解到,FILTER往往是导致SQL执行性能缓慢的元凶,主要是由于子查询未做UNNEST SUBQUERY查询转换,未UNNEST的原因有很多,比如统计信息不准,如果发现统计信息准确了还是无法UNNEST,那么要考虑SQL写法是否遇到优化器的限制或BUG,比如OR子查询。

    当然,FILTER由于内部构建HASH表,有缓存和HASH算法,对特定的查询效率可能不错,很显然是要求关联条件的重复值较少,这样子节点执行次数少,从而提高效率,所以在实际应用和优化中,还需要具体问题具体分析。

阅读(463) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~