前言
最近同事發(fā)現(xiàn)了一個問題，在12c中跑的buffer get很高，但是在10g中跑的buffer很低。懷疑是不是12c的優(yōu)化器有問題。
這個10g的環(huán)境和12c的環(huán)境，數(shù)據(jù)量大致一樣，只是有很少部分的不同，但是就是這個很少部分不同，造成了not exists中的子查詢返回不同的值，進而對外層查詢產(chǎn)生不同的影響。
我們來用如下的代碼模擬一下。
初始化數(shù)據(jù)：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
--10gdrop table t1;drop table t2; create table t1 (id number,name varchar2(20),dep_id varchar2(10));create table t2 (id number,name varchar2(20),dep_id varchar2(10)); insert into t1 select rownum,'a','kk' from dual connect by level <=3000000;insert into t2 select rownum,'a','kk' from dual connect by level <=1000000;insert into t2 select rownum,'a','mm' from dual; commit; --12cdrop table t1;drop table t2; create table t1 (id number,name varchar2(20),dep_id varchar2(10));create table t2 (id number,name varchar2(20),dep_id varchar2(10)); insert into t1 select rownum,'a','kk' from dual connect by level <=3000000;insert into t2 select rownum,'a','kk' from dual connect by level <=1000000; commit; |
我們看到，12c的數(shù)據(jù)和10g只是有很少的差別，t1表12c和10g都一樣，t2表在12c只是少了一行數(shù)據(jù)。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
--10gSQL> select dep_id,count(*) from t1 group by dep_id; DEP_ID COUNT(*)-------------------- ----------kk 3000000 SQL> select dep_id,count(*) from t2 group by dep_id; DEP_ID COUNT(*)-------------------- ----------mm 1kk 1000000 SQL> --12cSQL> select dep_id,count(*) from t1 group by dep_id; DEP_ID COUNT(*)-------------------- ----------kk 3000000 SQL> select dep_id,count(*) from t2 group by dep_id; DEP_ID COUNT(*)-------------------- ----------kk 1000000 SQL> |
我們將要執(zhí)行的sql語句是：
|
1
2
3
4
5
6
7
8
|
select count(*) from t1, t2 where t1.id = t2.id and t1.dep_id = 'kk' and not exists (select 1 from t1, t2 where t1.id = t2.id and t2.dep_id = 'mm'); |
我們先來看執(zhí)行情況的差距，10g的bufferget小，12c多：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
|
--10gSQL> select /*+ gather_plan_statistics */ count(*) from t1,t2 where t1.id=t2.id and t1.dep_id='kk' and not exists (select 1 from t1,t2 where t1.id=t2.id and t2.dep_id='mm'); COUNT(*)---------- 0 SQL> select* from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID 22t5mb43w55pr, child number 0-------------------------------------select /*+ gather_plan_statistics */ count(*) from t1,t2 where t1.id=t2.id and t1.dep_id='kk' and notexists (select 1 from t1,t2 where t1.id=t2.id and t2.dep_id='mm') Plan hash value: 3404612428 ------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.02 | 2086 | | | || 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.02 | 2086 | | | ||* 2 | FILTER | | 1 | | 0 |00:00:00.02 | 2086 | | | ||* 3 | HASH JOIN | | 0 | 901K| 0 |00:00:00.01 | 0 | 39M| 5518K| || 4 | TABLE ACCESS FULL| T2 | 0 | 901K| 0 |00:00:00.01 | 0 | | | ||* 5 | TABLE ACCESS FULL| T1 | 0 | 2555K| 0 |00:00:00.01 | 0 | | | ||* 6 | HASH JOIN | | 1 | 23 | 1 |00:00:00.02 | 2086 | 1517K| 1517K| 612K (0)||* 7 | TABLE ACCESS FULL| T2 | 1 | 23 | 1 |00:00:00.02 | 2082 | | | || 8 | TABLE ACCESS FULL| T1 | 1 | 2555K| 1 |00:00:00.01 | 4 | | | |------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter( IS NULL) 3 - access("T1"."ID"="T2"."ID") 5 - filter("T1"."DEP_ID"='kk') 6 - access("T1"."ID"="T2"."ID") 7 - filter("T2"."DEP_ID"='mm') Note----- - dynamic sampling used for this statement 34 rows selected. SQL> --12cSQL> select /*+ gather_plan_statistics */ count(*) from t1,t2 where t1.id=t2.id and t1.dep_id='kk' and not exists (select 1 from t1,t2 where t1.id=t2.id and t2.dep_id='mm'); COUNT(*)---------- 1000000 SQL> select* from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID 22t5mb43w55pr, child number 0-------------------------------------select /*+ gather_plan_statistics */ count(*) from t1,t2 wheret1.id=t2.id and t1.dep_id='kk' and not exists (select 1 from t1,t2where t1.id=t2.id and t2.dep_id='mm') Plan hash value: 1692274438 --------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |--------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.79 | 10662 | | | || 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.79 | 10662 | | | ||* 2 | FILTER | | 1 | | 1000K|00:00:00.74 | 10662 | | | ||* 3 | HASH JOIN | | 1 | 1215K| 1000K|00:00:00.52 | 8579 | 43M| 6111K| 42M (0)|| 4 | TABLE ACCESS FULL | T2 | 1 | 1215K| 1000K|00:00:00.01 | 2083 | | | ||* 5 | TABLE ACCESS FULL | T1 | 1 | 2738K| 3000K|00:00:00.07 | 6496 | | | ||* 6 | HASH JOIN RIGHT SEMI| | 1 | 35 | 0 |00:00:00.02 | 2083 | 1245K| 1245K| 461K (0)||* 7 | TABLE ACCESS FULL | T2 | 1 | 23 | 0 |00:00:00.02 | 2083 | | | || 8 | TABLE ACCESS FULL | T1 | 0 | 2738K| 0 |00:00:00.01 | 0 | | | |-------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter( IS NULL) 3 - access("T1"."ID"="T2"."ID") 5 - filter("T1"."DEP_ID"='kk') 6 - access("T1"."ID"="T2"."ID") 7 - filter("T2"."DEP_ID"='mm') Note----- - dynamic statistics used: dynamic sampling (level=2) 35 rows selected. SQL>SQL> |
可以看到第23，24行，在10g中運行時，buffers是0，而在12c中，即78，79行，buffer是2083+6496。
也就是說在10g中，外層查詢不進行t1和t2的掃描，直接返回結(jié)果了，而在12c中，外層查詢還要進行t1表和t2表層掃描才返回結(jié)果。
這其實不是10g和12c的差別，而是not exists的返回數(shù)據(jù)對外層的影響。子查詢要返回0行記錄，才滿足not exist的條件，從而返回外層查詢結(jié)果。
在10g中，子查詢返回了一行記錄
|
1
2
3
4
5
6
7
8
|
--10gSQL> select 1 from t1,t2 where t1.id=t2.id and t2.dep_id='mm'; 1---------- 1 SQL> |
不滿足not exists（即0行才滿足），所以，也就不用在外層繼續(xù)查詢了。直接返回記錄0行。
在12c中，子查詢返回0行記錄，滿足not exist的條件，所以還需要在外層查詢中繼續(xù)查詢。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
|
--12cSQL> select count(*) from t1,t2 where t1.id=t2.id and t2.dep_id='kk'; COUNT(*)---------- 1000000 SQL> set line 1000SQL> set pages 1000SQL> col PLAN_TABLE_OUTPUT for a250SQL>SQL>SQL> select /*+ gather_plan_statistics */ count(*) from t1,t2 where t1.id=t2.id and t1.dep_id='kk' and not exists (select 1 from t1,t2 where t1.id=t2.id and t2.dep_id='kk'); COUNT(*)---------- 0 SQL> select* from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID c5hj2p2jt1fxf, child number 0-------------------------------------select /*+ gather_plan_statistics */ count(*) from t1,t2 wheret1.id=t2.id and t1.dep_id='kk' and not exists (select 1 from t1,t2where t1.id=t2.id and t2.dep_id='kk') Plan hash value: 1692274438 --------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |--------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.28 | 2087 | | | || 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.28 | 2087 | | | ||* 2 | FILTER | | 1 | | 0 |00:00:00.28 | 2087 | | | ||* 3 | HASH JOIN | | 0 | 1215K| 0 |00:00:00.01 | 0 | 69M| 7428K| || 4 | TABLE ACCESS FULL | T2 | 0 | 1215K| 0 |00:00:00.01 | 0 | | | ||* 5 | TABLE ACCESS FULL | T1 | 0 | 2738K| 0 |00:00:00.01 | 0 | | | ||* 6 | HASH JOIN RIGHT SEMI| | 1 | 2738K| 1 |00:00:00.28 | 2087 | 43M| 6111K| 42M (0)||* 7 | TABLE ACCESS FULL | T2 | 1 | 1215K| 1000K|00:00:00.12 | 2083 | | | || 8 | TABLE ACCESS FULL | T1 | 1 | 2738K| 1 |00:00:00.01 | 4 | | | |-------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter( IS NULL) 3 - access("T1"."ID"="T2"."ID") 5 - filter("T1"."DEP_ID"='kk') 6 - access("T1"."ID"="T2"."ID") 7 - filter("T2"."DEP_ID"='kk') Note----- - dynamic statistics used: dynamic sampling (level=2) 35 rows selected. SQL> |
可以看到第38，39行的buffer為0.
總結(jié)
以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學習或者工作能帶來一定的幫助，如果有疑問大家可以留言交流。
原文鏈接：https://oracleblog.org/study-note/not-exists-effect-outer-side-select/
