前言
最近同事發(fā)現(xiàn)了一個(gè)問(wèn)題，在12c中跑的buffer get很高，但是在10g中跑的buffer很低。懷疑是不是12c的優(yōu)化器有問(wèn)題。
這個(gè)10g的環(huán)境和12c的環(huán)境，數(shù)據(jù)量大致一樣，只是有很少部分的不同，但是就是這個(gè)很少部分不同，造成了not exists中的子查詢(xún)返回不同的值，進(jìn)而對(duì)外層查詢(xún)產(chǎn)生不同的影響。
我們來(lái)用如下的代碼模擬一下。
初始化數(shù)據(jù)：
--10g
drop 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;
--12c
drop 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ù)。
--10g
SQL> 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 1
kk 1000000
SQL>
--12c
SQL> 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語(yǔ)句是：
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');
我們先來(lái)看執(zhí)行情況的差距，10g的bufferget小，12c多：
--10g
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='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 not
exists (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>
--12c
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='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 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')
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中運(yùn)行時(shí)，buffers是0，而在12c中，即78，79行，buffer是2083+6496。
也就是說(shuō)在10g中，外層查詢(xún)不進(jìn)行t1和t2的掃描，直接返回結(jié)果了，而在12c中，外層查詢(xún)還要進(jìn)行t1表和t2表層掃描才返回結(jié)果。
這其實(shí)不是10g和12c的差別，而是not exists的返回?cái)?shù)據(jù)對(duì)外層的影響。子查詢(xún)要返回0行記錄，才滿(mǎn)足not exist的條件，從而返回外層查詢(xún)結(jié)果。
在10g中，子查詢(xún)返回了一行記錄
--10g
SQL> select 1 from t1,t2 where t1.id=t2.id and t2.dep_id='mm';
1
----------
1
SQL>
不滿(mǎn)足not exists（即0行才滿(mǎn)足），所以，也就不用在外層繼續(xù)查詢(xún)了。直接返回記錄0行。
在12c中，子查詢(xún)返回0行記錄，滿(mǎn)足not exist的條件，所以還需要在外層查詢(xún)中繼續(xù)查詢(xún)。
--12c
SQL> select count(*) from t1,t2 where t1.id=t2.id and t2.dep_id='kk';
COUNT(*)
----------
1000000
SQL> set line 1000
SQL> set pages 1000
SQL> col PLAN_TABLE_OUTPUT for a250
SQL>
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 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')
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é)
以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作能帶來(lái)一定的幫助，如果有疑問(wèn)大家可以留言交流。
您可能感興趣的文章:- Oracle In和exists not in和not exists的比較分析
- Plsql Developer連接Oracle時(shí)出現(xiàn)Could not initialize oci.dll解決方案
- Oracle minus用法詳解及應(yīng)用實(shí)例
- linux系統(tǒng)oracle數(shù)據(jù)庫(kù)出現(xiàn)ora12505問(wèn)題的解決方法
- 簡(jiǎn)述Oracle中in和exists的不同
