about me:oracle ace,optimistic,passionate and harmonious. focus on oracle programming,peformance tuning,db design, j2ee,linux/aix,web2.0 tech,etc
（145）
（28）
（43）
（62）
（3）
（9）
分类： 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) |