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)
分类: Oracle
2022-04-14 11:19:14
案例如下:
--在t2表中插入1024行数据
DROP TABLE t2;
CREATE TABLE t2 AS SELECT LEVEL ID FROM dual CONNECT BY LEVEL<=1024;
--反复执行5次insert 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内容可以知道,表T2的ID只有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
);
通过本文可以了解到,FILTER往往是导致SQL执行性能缓慢的元凶,主要是由于子查询未做UNNEST SUBQUERY查询转换,未UNNEST的原因有很多,比如统计信息不准,如果发现统计信息准确了还是无法UNNEST,那么要考虑SQL写法是否遇到优化器的限制或BUG,比如OR子查询。
当然,FILTER由于内部构建HASH表,有缓存和HASH算法,对特定的查询效率可能不错,很显然是要求关联条件的重复值较少,这样子节点执行次数少,从而提高效率,所以在实际应用和优化中,还需要具体问题具体分析。