数据分布不均匀走HASH JOIN导致的性能问题

阅读量119

这个案例是JAVA开发说一个存储过程跑的很慢，之后我跑这个过程，然后通过脚本抓出了慢的SQL

表大小

1
2
3
tb_user_channel  --1W
tb_channel_info  --1W
base_data_login_info 19W

就是这条SQL，跑完要7分钟。base_data_login_info本来是@db_link，但是我在本地建了一个同样的表发现还是7分钟左右，所以排除了可能是由于db_link造成问题的可能性

1
2
3
4
5
6
select 
count(distinct a.user_name),count(distinct a.invest_id)
         from base_data_login_info a
         where a.str_day <= '20160304' and a.str_day >= '20160301'
 and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
 and a.platform = a.platform

看一下这个sql返回多少行，结果秒杀，瞬间就出结果了

1
2
3
4
5
6
select 
 count(*)
         from base_data_login_info@agent a
         where a.str_day <= '20160304' and a.str_day >= '20160301'
 and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
 and a.platform = a.platform

45122行
之后单独跑
1
2
3
4
5
6
select 
count(distinct a.user_name),count( a.invest_id)
         from base_data_login_info a
         where a.str_day <= '20160304' and a.str_day >= '20160301'
 and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
 and a.platform = a.platform

或
1
2
3
4
5
6
select 
count( a.user_name),count(distinct a.invest_id)
         from base_data_login_info a
         where a.str_day <= '20160304' and a.str_day >= '20160301'
 and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
 and a.platform = a.platform

都是秒杀
单独count distinct user_name 或 invest_id 都很快 ，一起count distinct就很慢了
那么这时候其实已经可以通过改写SQL提示性能了，改写如下

1
2
3
4
5
6
7
with t1 as 
(select 
 a.user_name, a.invest_id
         from base_data_login_info@agent a
         where a.str_day <= '20160304' and a.str_day >= '20160301'
 and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
 and a.platform = a.platform)

查看改写后的执行计划
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
select (select count(distinct user_name) from t1),(select count(distinct invest_id) from t1) from dual;
Plan hash value: 3790966246
 
-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Inst   |IN-OUT|
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                          |     1 |       |       |   746   (1)| 00:00:09 |        |      |
|   1 |  SORT AGGREGATE            |                          |     1 |    27 |       |            |          |        |      |
|   2 |   VIEW                     | VM_NWVW_2                | 40660 |  1072K|       |  1589   (1)| 00:00:20 |        |      |
|   3 |    SORT GROUP BY           |                          | 40660 |  1072K|  6752K|  1589   (1)| 00:00:20 |        |      |
|   4 |     VIEW                   |                          |   190K|  5021K|       |   878   (1)| 00:00:11 |        |      |
|   5 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9D671E_EB8EA |   190K|     9M|       |   878   (1)| 00:00:11 |        |      |
|   6 |  SORT AGGREGATE            |                          |     1 |    27 |       |            |          |        |      |
|   7 |   VIEW                     | VM_NWVW_3                | 41456 |  1093K|       |  1593   (1)| 00:00:20 |        |      |
|   8 |    SORT GROUP BY           |                          | 41456 |  1093K|  6752K|  1593   (1)| 00:00:20 |        |      |
|   9 |     VIEW                   |                          |   190K|  5021K|       |   878   (1)| 00:00:11 |        |      |
|  10 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9D671E_EB8EA |   190K|     9M|       |   878   (1)| 00:00:11 |        |      |
|  11 |  TEMP TABLE TRANSFORMATION |                          |       |       |       |            |          |        |      |
|  12 |   LOAD AS SELECT           | SYS_TEMP_0FD9D671E_EB8EA |       |       |       |            |          |        |      |
|* 13 |    HASH JOIN RIGHT SEMI    |                          |   190K|    22M|       |   744   (1)| 00:00:09 |        |      |
|  14 |     VIEW                   | VW_NSO_1                 | 11535 |   304K|       |   258   (1)| 00:00:04 |        |      |
|* 15 |      HASH JOIN             |                          | 11535 |   360K|       |   258   (1)| 00:00:04 |        |      |
|* 16 |       TABLE ACCESS FULL    | TB_USER_CHANNEL          | 11535 |   157K|       |    19   (0)| 00:00:01 |        |      |
|  17 |       TABLE ACCESS FULL    | TB_CHANNEL_INFO          | 11767 |   206K|       |   238   (0)| 00:00:03 |        |      |
|  18 |     REMOTE                 | BASE_DATA_LOGIN_INFO     |   190K|    17M|       |   486   (1)| 00:00:06 |  AGENT | R->S |
|  19 |   FAST DUAL                |                          |     1 |       |       |     2   (0)| 00:00:01 |        |      |
-------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
  13 - access("A"."CHANNEL_ID"="CHANNEL_RLAT")
  15 - access("A"."CHANNEL_ID"="B"."CHANNEL_ID")
  16 - filter("A"."USER_ID"=5002)
 
Remote SQL Information (identified by operation id):
----------------------------------------------------
 
  18 - SELECT "USER_NAME","INVEST_ID","STR_DAY","CHANNEL_ID","PLATFORM" FROM "BASE_DATA_LOGIN_INFO" "A" WHERE 
        "STR_DAY"<='20160304' AND "STR_DAY">='20160301' AND "PLATFORM" IS NOT NULL (accessing 'AGENT' )

为了探究性能瓶颈我们继续看
慢的执行计划
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
select 
count(distinct a.user_name),count(distinct a.invest_id)
         from base_data_login_info@agent a
         where a.str_day <= '20160304' and a.str_day >= '20160301'
 and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
 and a.platform = a.platform
 
Plan hash value: 2367445948
 
-------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                      |     1 |   130 |   754   (2)| 00:00:10 |        |      |
|   1 |  SORT GROUP BY       |                      |     1 |   130 |            |          |        |      |
|*  2 |   HASH JOIN          |                      |  4067K|   504M|   754   (2)| 00:00:10 |        |      |
|*  3 |    HASH JOIN         |                      | 11535 |   360K|   258   (1)| 00:00:04 |        |      |
|*  4 |     TABLE ACCESS FULL| TB_USER_CHANNEL      | 11535 |   157K|    19   (0)| 00:00:01 |        |      |
|   5 |     TABLE ACCESS FULL| TB_CHANNEL_INFO      | 11767 |   206K|   238   (0)| 00:00:03 |        |      |
|   6 |    REMOTE            | BASE_DATA_LOGIN_INFO |   190K|    17M|   486   (1)| 00:00:06 |  AGENT | R->S |
-------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("A"."CHANNEL_ID"="CHANNEL_RLAT")
   3 - access("A"."CHANNEL_ID"="B"."CHANNEL_ID")
   4 - filter("A"."USER_ID"=5002)
 
Remote SQL Information (identified by operation id):
----------------------------------------------------
 
   6 - SELECT "USER_NAME","INVEST_ID","STR_DAY","CHANNEL_ID","PLATFORM" FROM "BASE_DATA_LOGIN_INFO" 
       "A" WHERE "STR_DAY"<='20160304' AND "STR_DAY">='20160301' AND "PLATFORM" IS NOT NULL (accessing 
       'AGENT' )

快的执行计划
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
explain plan for
select 
count( a.user_name),count(distinct a.invest_id)
         from base_data_login_info@agent a
         where a.str_day <= '20160304' and a.str_day >= '20160301'
 and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
 and a.platform = a.platform
Plan hash value: 4282421321
 
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name                 | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Inst   |IN-OUT|
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                      |     1 |    40 |       |  2982   (1)| 00:00:36 |        |      |
|   1 |  SORT AGGREGATE         |                      |     1 |    40 |       |            |          |        |      |
|   2 |   VIEW                  | VW_DAG_0             | 41456 |  1619K|       |  2982   (1)| 00:00:36 |        |      |
|   3 |    HASH GROUP BY        |                      | 41456 |  4250K|    20M|  2982   (1)| 00:00:36 |        |      |
|*  4 |     HASH JOIN RIGHT SEMI|                      |   190K|    19M|       |   744   (1)| 00:00:09 |        |      |
|   5 |      VIEW               | VW_NSO_1             | 11535 | 80745 |       |   258   (1)| 00:00:04 |        |      |
|*  6 |       HASH JOIN         |                      | 11535 |   360K|       |   258   (1)| 00:00:04 |        |      |
|*  7 |        TABLE ACCESS FULL| TB_USER_CHANNEL      | 11535 |   157K|       |    19   (0)| 00:00:01 |        |      |
|   8 |        TABLE ACCESS FULL| TB_CHANNEL_INFO      | 11767 |   206K|       |   238   (0)| 00:00:03 |        |      |
|   9 |      REMOTE             | BASE_DATA_LOGIN_INFO |   190K|    17M|       |   486   (1)| 00:00:06 |  AGENT | R->S |
------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("A"."CHANNEL_ID"="CHANNEL_RLAT")
   6 - access("A"."CHANNEL_ID"="B"."CHANNEL_ID")
   7 - filter("A"."USER_ID"=5002)
 
Remote SQL Information (identified by operation id):
----------------------------------------------------
 
   9 - SELECT "USER_NAME","INVEST_ID","STR_DAY","CHANNEL_ID","PLATFORM" FROM "BASE_DATA_LOGIN_INFO" "A" WHERE 
       "STR_DAY"<='20160304' AND "STR_DAY">='20160301' AND "PLATFORM" IS NOT NULL (accessing 'AGENT' )

注意到快的执行计划用的是HASH JOIN SEMI 而 慢的执行计划用的是 HASH JOIN
我又跑慢的SQL，想查看等待时间分析问题，结果等待事件却是 SQLNet message to client。。。
做个10046
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
alter session set events ‘10046 trace name context forever, level 8’;
alter session set tracefile_identifier='fan';
alter session set max_dump_file_size=unlimited;
alter session set events '10046 trace name context forever, level 8';
SQL>.............
alter session set events '10046 trace name context off';
tkprof  channel_ora_4917_fan.trc  hehe sys=no waits=yes
 
select
count(distinct a.user_name),count(distinct a.invest_id)
         from base_data_login_info a
         where a.str_day <= '20160304' and a.str_day >= '20160301'
 and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
 and a.platform = a.platform
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2   1092.09    1236.55          0       3643          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4   1092.09    1236.56          0       3643          0           1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 84  
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT GROUP BY (cr=3643 pr=0 pw=0 time=1236559678 us)
 410996039  410996039  410996039   HASH JOIN  (cr=3643 pr=0 pw=0 time=406365130 us cost=1006 size=66968010 card=458685)
     11535      11535      11535    HASH JOIN  (cr=945 pr=0 pw=0 time=199182 us cost=258 size=369120 card=11535)
     11535      11535      11535     TABLE ACCESS FULL TB_USER_CHANNEL (cr=67 pr=0 pw=0 time=21452 us cost=19 size=161490 card=11535)
     11771      11771      11771     TABLE ACCESS FULL TB_CHANNEL_INFO (cr=878 pr=0 pw=0 time=30291 us cost=238 size=211806 card=11767)
     45122      45122      45122    TABLE ACCESS FULL BASE_DATA_LOGIN_INFO (cr=2698 pr=0 pw=0 time=218144 us cost=747 size=2447922 card=21473)
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2       50.71         50.71
注意执行计划第二行 变成了40亿！
关联列数据分布
19W 表连接列
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SQL> select channel_id,count(*) from base_data_login_info group by channel_id order by 2;
CHANNEL_ID               COUNT(*)
-------------------------------------------------- ----------
011a1                 2
003a1                 3
021a1                 3
006a1                12
024h2                16
013a1                19
007a1                24
012a1                25
005a1                27
EPT01                36
028h2               109
008a1               139
029a1               841
009a1               921
014a1              1583
000a1              1975
a0001              2724
004a1              5482
001a1             16329
026h2             160162
20 rows selected.

in里的
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
select channel_rlat,count(*) from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002 group by channel_rlat order by 2 desc 
channel_rlat  count(*)
026h2         10984
024h2         7
002h2         6
023a2         2
007s001022001 1
007s001022002 1
007s001024007 1
007s001024009 1
007s001022009 1
001s001006    1
001s001008    1
001s001001001 1
001s001001003 1
001s001001007 1
001s001001014 1
007s001018003 1
007s001018007 1
007s001019005 1
007s001019008 1
001s001002011 1
007s001011003 1
007s001034    1
007s001023005 1
007s001011008 1
HASH JOIN 只适合数据分布均匀的列做关联，而这个关联列数据分布极度不均衡，相当于一个小笛卡尔及，在bucket里找死了*

继续深入，为什么本来是半连接，CBO用了HASH JOIN？

单独跑这个是很快的

1
2
3
4
5
6
select 
 a.user_name,a.invest_id
         from base_data_login_info@agent a
         where a.str_day <= '20160304' and a.str_day >= '20160301'
 and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
 and a.platform = a.platform

这样就慢了，因为视图合并了
1
2
3
4
5
6
7
select count(distinct a.user_name),count(distinct a.invest_id) from (
select 
 a.user_name,a.invest_id
         from base_data_login_info@agent a
         where a.str_day <= '20160304' and a.str_day >= '20160301'
 and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
 and a.platform = a.platform ) a

CBO把它改写成了
1
2
3
4
5
select 
 count(distinct a.user_name),count(distinct a.invest_id)
         from base_data_login_info@agent a,(select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002) b
         where a.channel_id=b.channel_rlat
         and a.str_day <= '20160304' and a.str_day >= '20160301'

半连接本身就可以改写成inner join,但是in里的表要distinct,举个例子
1
2
3
4
5
6
select * from dept where deptno in (select deptno from emp)
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO

可以改写为
1
2
select d.* from dept d inner join (select distinct deptno from emp ) e 
on d.deptno=e.deptno

否则如果不distinct结果集就会有问题
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SQL> select d.* from dept d inner join (select  deptno from emp ) e 
  2  on d.deptno=e.deptno ;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        10 ACCOUNTING     NEW YORK
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        20 RESEARCH       DALLAS
        20 RESEARCH       DALLAS
        20 RESEARCH       DALLAS
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        30 SALES          CHICAGO
        30 SALES          CHICAGO
        30 SALES          CHICAGO
        30 SALES          CHICAGO
        30 SALES          CHICAGO
已选择14行。

但是巧就巧的是，我这个SQL是 count(distinct a.user_name),count(distinct a.invest_id)
所以CBO把它改写成了inner join,还不用把in里面先去重，CBO真TM聪明，可惜聪明反被聪明误。我们这个关联列数据分布极度不均匀
那我们不想让CBO改写咋办呢

那么我们不让视图合并用 rownum>0

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
select count(distinct a.user_name),count(distinct a.invest_id) from (
select 
 a.user_name,a.invest_id
         from base_data_login_info@agent a
         where a.str_day <= '20160304' and a.str_day >= '20160301'
 and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
 and a.platform = a.platform and rownum>0) a
Plan hash value: 3295380261
 
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                      |     1 |    54 |   744   (1)| 00:00:09 |        |      |
|   1 |  SORT GROUP BY           |                      |     1 |    54 |            |          |        |      |
|   2 |   VIEW                   |                      |   190K|     9M|   744   (1)| 00:00:09 |        |      |
|   3 |    COUNT                 |                      |       |       |            |          |        |      |
|*  4 |     FILTER               |                      |       |       |            |          |        |      |
|*  5 |      HASH JOIN RIGHT SEMI|                      |   190K|    22M|   744   (1)| 00:00:09 |        |      |
|   6 |       VIEW               | VW_NSO_1             | 11535 |   304K|   258   (1)| 00:00:04 |        |      |
|*  7 |        HASH JOIN         |                      | 11535 |   360K|   258   (1)| 00:00:04 |        |      |
|*  8 |         TABLE ACCESS FULL| TB_USER_CHANNEL      | 11535 |   157K|    19   (0)| 00:00:01 |        |      |
|   9 |         TABLE ACCESS FULL| TB_CHANNEL_INFO      | 11767 |   206K|   238   (0)| 00:00:03 |        |      |
|  10 |       REMOTE             | BASE_DATA_LOGIN_INFO |   190K|    17M|   486   (1)| 00:00:06 |  AGENT | R->S |
-----------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - filter(ROWNUM>0)
   5 - access("A"."CHANNEL_ID"="CHANNEL_RLAT")
   7 - access("A"."CHANNEL_ID"="B"."CHANNEL_ID")
   8 - filter("A"."USER_ID"=5002)
 
Remote SQL Information (identified by operation id):
----------------------------------------------------
 
  10 - SELECT "USER_NAME","INVEST_ID","STR_DAY","CHANNEL_ID","PLATFORM" FROM "BASE_DATA_LOGIN_INFO" "A" 
        WHERE "STR_DAY"<='20160304' AND "STR_DAY">='20160301' AND "PLATFORM" IS NOT NULL (accessing 'AGENT' )
with t1 as 
(select /*+ materialize */
 a.user_name, a.invest_id
         from base_data_login_info@agent a
         where a.str_day <= '20160304' and a.str_day >= '20160301'
 and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
 and a.platform = a.platform)
select count(distinct user_name) ,count(distinct invest_id) from t1;
Plan hash value: 901326807
 
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                          |     1 |    54 |  1621   (1)| 00:00:20 |        |      |
|   1 |  TEMP TABLE TRANSFORMATION |                          |       |       |            |          |        |      |
|   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6720_EB8EA |       |       |            |          |        |      |
|*  3 |    HASH JOIN RIGHT SEMI    |                          |   190K|    22M|   744   (1)| 00:00:09 |        |      |
|   4 |     VIEW                   | VW_NSO_1                 | 11535 |   304K|   258   (1)| 00:00:04 |        |      |
|*  5 |      HASH JOIN             |                          | 11535 |   360K|   258   (1)| 00:00:04 |        |      |
|*  6 |       TABLE ACCESS FULL    | TB_USER_CHANNEL          | 11535 |   157K|    19   (0)| 00:00:01 |        |      |
|   7 |       TABLE ACCESS FULL    | TB_CHANNEL_INFO          | 11767 |   206K|   238   (0)| 00:00:03 |        |      |
|   8 |     REMOTE                 | BASE_DATA_LOGIN_INFO     |   190K|    17M|   486   (1)| 00:00:06 |  AGENT | R->S |
|   9 |   SORT GROUP BY            |                          |     1 |    54 |            |          |        |      |
|  10 |    VIEW                    |                          |   190K|     9M|   878   (1)| 00:00:11 |        |      |
|  11 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6720_EB8EA |   190K|     9M|   878   (1)| 00:00:11 |        |      |
-----------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("A"."CHANNEL_ID"="CHANNEL_RLAT")
   5 - access("A"."CHANNEL_ID"="B"."CHANNEL_ID")
   6 - filter("A"."USER_ID"=5002)
 
Remote SQL Information (identified by operation id):
----------------------------------------------------
 
   8 - SELECT "USER_NAME","INVEST_ID","STR_DAY","CHANNEL_ID","PLATFORM" FROM "BASE_DATA_LOGIN_INFO" "A" WHERE 
       "STR_DAY"<='20160304' AND "STR_DAY">='20160301' AND "PLATFORM" IS NOT NULL (accessing 'AGENT' )

再解释一下

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
create table emp2 as select * from emp;
insert into emp2 select * from emp2;
create table emp4 as select * from emp2;
SQL> select count(distinct a.job),count(distinct a.ename) from emp2 a where a.deptno in (select deptno from emp4); 
COUNT(DISTINCTA.JOB) COUNT(DISTINCTA.ENAME)
-------------------- ----------------------
                   4                     11
等价 于
SQL> select count(distinct a.job),count(distinct a.ename) from emp2 a,emp4 b where a.deptno=b.deptno;
COUNT(DISTINCTA.JOB) COUNT(DISTINCTA.ENAME)
-------------------- ----------------------
                   4                     11
CBO把它改写成了这个 a.deptno=b.deptno hash join
Execution Plan
----------------------------------------------------------
Plan hash value: 3097773013
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    39 |     8  (13)| 00:00:01 |
|   1 |  SORT GROUP BY      |      |     1 |    39 |            |          |
|*  2 |   HASH JOIN         |      |   242 |  9438 |     8  (13)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP2 |    22 |   572 |     4   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| EMP4 |    22 |   286 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Powered by Hexo and Hexo-theme-hiker

Copyright © 2013 - 2022 Fan() All Rights Reserved.

访客数 : 55671 | 访问量 : 70860