About me:Oracle ACE,optimistic,passionate and harmonious. Focus on oracle programming,peformance tuning,db design, j2ee,Linux/AIX,web2.0 tech,etc
全部博文（148）
分类： Oracle
2023-05-04 16:32:57
FILTER操作中子查询执行次数问题
1. 一般情况下，FILTER操作会根据条件保存已匹配的结果，{BANNED}最佳多保存1024个
2. 因此会根据值的distinct数目决定子查询执行次数，但是如果比较列是无序存储的，则数目可能大于distinct数目（HASH表可能内部剔除已缓存的值），这就导致可能同类型表，有的SQL快，有的SQL慢。
3. 解决这个问题，就是通过改写或者增加约束，让其走HASH JOIN，特别是NOT IN情况下。
例子：
1.
|
DROPTABLE t2; CREATETABLE t2 ASSELECTLEVELIDFROM dual CONNECTBYLEVEL<=1024; --多执行几次 INSERTINTO t2 SELECT*FROM t2; SELECTCOUNT(*) FROM t2 WHEREIDNOTIN (SELECTID FROM(SELECTID FROM t2 ORDERBYIDDESC)WHEREROWNUM<=1024); |
T2有8192行，虽然ID只有1024个，按理子查询被驱动1024次，但是因为插入多次，ID不是按照顺序存储，子查询实际执行3600次。
执行计划如下：
|
Plan hash value: 1172034112 ------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:09.24 | 57616 | | | | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:09.24 | 57616 | | | | |* 2 | FILTER | | 1 | | 7168 |00:00:09.24 | 57616 | | | | | 3 | TABLE ACCESS FULL | T2 | 1 | 2048 | 8192 |00:00:00.01 | 16 | | | | |* 4 | FILTER | | 3600 | | 583 |00:00:09.23 | 57600 | | | | |* 5 | COUNT STOPKEY | | 3600 | | 3391K|00:00:08.96 | 57600 | | | | | 6 | VIEW | | 3600 | 2048 | 3391K|00:00:08.58 | 57600 | | | | |* 7 | SORT ORDER BY STOPKEY| | 3600 | 2048 | 3391K|00:00:08.21 | 57600 | 66560 | 66560 |59392 (0)| | 8 | TABLE ACCESS FULL | T2 | 3600 | 2048 | 29M|00:00:01.93 | 57600 | | | | ------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter( IS NULL) 4 - filter(LNNVL("ID"<>:B1)) 5 - filter(ROWNUM<=1024) 7 - filter(ROWNUM<=1024) Note ----- - dynamic sampling used for this statement (level=2) |
2.根据t2表按照id顺序重新组织
|
DROPTABLE t3;
CREATETABLE t3 ASSELECT*FROM t2ORDERBYID;--按照关联列ID重新组织顺序 SELECTCOUNT(*) FROM t3 WHEREIDNOTIN (SELECTID FROM(SELECTID FROM t3 ORDERBYIDDESC)WHEREROWNUM<=1024); |
子查询被驱动1024次，正常。
执行计划如下：
|
Plan hash value: 2856703331 ------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:03.25 | 15375 | | | | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:03.25 | 15375 | | | | |* 2 | FILTER | | 1 | | 7168 |00:00:03.25 | 15375 | | | | | 3 | TABLE ACCESS FULL | T3 | 1 | 8192 | 8192 |00:00:00.01 | 15 | | | | |* 4 | FILTER | | 1024 | | 128 |00:00:03.25 | 15360 | | | | |* 5 | COUNT STOPKEY | | 1024 | | 982K|00:00:03.18 | 15360 | | | | | 6 | VIEW | | 1024 | 8192 | 982K|00:00:03.07 | 15360 | | | | |* 7 | SORT ORDER BY STOPKEY| | 1024 | 8192 | 982K|00:00:02.97 | 15360 | 70656 | 70656 |63488 (0)| | 8 | TABLE ACCESS FULL | T3 | 1024 | 8192 | 8388K|00:00:00.54 | 15360 | | | | ------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter( IS NULL) 4 - filter("ID"=:B1) 5 - filter(ROWNUM<=1024) 7 - filter(ROWNUM<=1024) |
结论：
FILTER除非子查询次数确定很少，否则{BANNED}最佳好修改语句或通过其它方式（比如BUG和参数有关）让其走HASH JOIN来提升效率。比如以上问题可以修改为NOT EXISTS：逻辑读从1.5w减少为30,执行时间从3.25s降低为0.01s,极大提升效率。
|
------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 30 | | | | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 30 | | | | |* 2 | HASH JOIN RIGHT ANTI | | 1 | 82 | 7168 |00:00:00.01 | 30 | 1517K| 1517K| 1545K (0)| | 3 | VIEW | | 1 | 1024 | 1024 |00:00:00.01 | 15 | | | | |* 4 | COUNT STOPKEY | | 1 | | 1024 |00:00:00.01 | 15 | | | | | 5 | VIEW | | 1 | 8192 | 1024 |00:00:00.01 | 15 | | | | |* 6 | SORT ORDER BY STOPKEY| | 1 | 8192 | 1024 |00:00:00.01 | 15 | 70656 | 70656 |63488 (0)| | 7 | TABLE ACCESS FULL | T3 | 1 | 8192 | 8192 |00:00:00.01 | 15 | | | | | 8 | TABLE ACCESS FULL | T3 | 1 | 8192 | 8192 |00:00:00.01 | 15 | | | | ------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("X"."ID"="T3"."ID") 4 - filter(ROWNUM<=1024) 6 - filter(ROWNUM<=1024) |
