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

最新推荐文章于 2022-09-16 17:38:46 发布
最新推荐文章于 2022-09-16 17:38:46 发布
阅读量3.1k 收藏 3
点赞数
分类专栏： SQL优化
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ashic/article/details/52138845
版权

 

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

表大小

tb_user_channel  --1W
tb_channel_info  --1W
base_data_login_info 19W

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

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返回多少行，结果秒杀，瞬间就出结果了

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行 之后单独跑

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

或

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提升性能了，改写如下 

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)
select (select count(distinct user_name) from t1),(select count(distinct invest_id) from t1) from dual;

查看改写后的执行计划

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' )

为了探究性能瓶颈我们继续看 慢的执行计划

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' )

快的执行计划

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，想查看等待时间分析问题，结果等待事件却是 SQL*Net message to client。。。 做个10046

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 表连接列

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里的

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？

单独跑这个是很快的

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

这样就慢了，因为视图合并了

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把它改写成了

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,举个例子

select * from dept where deptno in (select deptno from emp)
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO

可以改写为

select d.* from dept d inner join (select distinct deptno from emp ) e 
on d.deptno=e.deptno

否则如果不distinct结果集就会有问题

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

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' )



或者用materialize大法
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' )

再解释一下

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 |
----------------------------------------------------------------------------

 

Fan_-_
关注
专栏目录
影响SQL执行性能的原因
小猪的成长专栏
10-20 1023
 影响数据库执行性能的原因:1 计算机硬件问题2 数据库管理系统问题（ORACLE,SQL SERVER,MYSQL,DB2...）3 数据库设计问题(例如:索引的情况)4 SQL写法问题5 实际应用数据量的多少 作为程序员，我们一般不能决定计算机硬件，数据库系统，数据库设计，以及实际应用数据量的多少，所以，我们可以在SQL的写法上下工夫。第一个原则： 避免 LEFT JOIN 和
Hive/MaxCompute SQL性能优化(三)：数据倾斜优化实战
最新发布
王义凯 的博客
01-10 3298
前面介绍了如何定位数据倾斜，本文介绍如果遇到各种数据倾斜的情况该怎样优化代码。
视图合并、hash join连接列数据分布不均匀引发的惨案
FaN()
08-26 2464
表大小SQL> select count(*) from agent.TB_AGENT_INFO; COUNT(*) ---------- 1751SQL> select count(*) from TB_CHANNEL_INFO ; COUNT(*) ---------- 1807SQL> select count(*) from TB_USER_CHANNEL; C
PostgreSQL - 并行哈希JOIN 分析查询性能爆炸
weixin_34255055的博客
02-02 1471
标签 PostgreSQL , 并行哈希join , parall hash join 背景 两张较大的表做JOIN，并且经过WHERE条件筛选出来的量依旧很大时，通常PostgreSQL会选择HASH JOIN，提高JOIN的性能。 PostgreSQL 11增加了一个重量级特性： 并行哈希JOIN。 https://commitfest.post...
数据分布不均衡导致性能问题
热门推荐
落落的专栏 专注SQL调优 性能调优
04-14 1万+
今晚(2016/04/14)遇到一个奇葩案例，虽然之前也遇到过非常多奇葩案例， 但是限于当时条件，无法收集案例，谁叫他奶奶的银行，证券，电信不允许泄密啊。还好今晚这个案例可以拿出来分享。 故事是这样的，下面这个SQL要跑几十分钟 select count(distinct a.user_name), count(distinct a.invest_id) from base_data_lo
sql 查询强制使用HASH连接性能测试比较
weixin_30287169的博客
07-21 73
HASH JOIN 散列连接 hash join是CBO 做大数据集连接时的常用方式。优化器扫描小表（或数据源），利用连接键（也就是根据连接字段计算hash 值）在内存中建立hash表，然后扫描大表，每读到一条记录就来探测hash表一次，找出与hash表匹配的行。当小表可以全部放入内存中，其成本接近全表扫描两个表的成本之和。如果表很大不能完全放入内存，这时优化器会将它分割成若干不同的分...
hash join 原理和算法
03-21
Oracle还会使用位图向量过滤、角色互换和柱状图等技术来处理数据分布不均匀的问题，以提高Hash Join的效率。 **四、Hash Join算法的成本** 在决定是否使用Hash Join时，CBO会评估其成本，包括构建哈希表所需的内存...
hash join算法原理
05-17
Hash Join的性能取决于多种因素，包括Hash Table的大小、分区策略、内存可用性以及数据分布的均匀性。高fan-out可能导致大量小分区，增加I/O开销，而低fan-out可能导致大分区无法完全放入内存。因此，调整hash_area_...
Hash join算法原理(转)
congnen9588的博客
07-02 1786
Hash join算法原理自从oracke 7.3以来，oracle提供了一种新的join技术，就是hash join。Hash Join只能用于相等连接，且只能在CBO优化器模式下。相对于nested loop join，ha...
SQL性能变差的原因
lyjiau的专栏
12-17 943
SQL重新解析后，跟以前相比，性能突然变差，通常是下列原因： 　　1. 表和索引的优化统计信息被删除，或者重新收集后统计信息不准确。重新收集统计信息通常是由于收集策略（方法）不正确引起。比如对分区表使用analyze命令而不是用dbms_stats包、收集统计信息时采样比例过小等等。Oracle优化器严重依赖于统计信息，如果统计信息有问题，则很容易导致SQL不能使用正确的执行计划。 　　2.
一次HASH JION过慢优化（1）
cri5768的博客
02-08 323
原创 转载请注明出处 最近我发现生产有一个语句执行比较慢。需要4-5分钟。所以对其进行了优化，优化结果执行只需要不到3秒。语句如下：我发现出问题的部分是select * from (selec...
一次HASH JION过慢优化（2）
cri5768的博客
02-08 143
原创 转载请注明出处 可以看到进行了笛卡尔集，再HASH JION的时候使用了过多的临时表空间用于存储HASH值，达到了2.6M。而笛卡尔集是test1和test2做的。其实我们是有连接条件的，连接条件是filter("A"...
nested loop比hash join慢
lyjilu2008的专栏
11-20 501
今天遇见个诡异为题，情况是这样的 我有一个sql，在分页查询 为20条的时候，查询结果为100多ms，但是当分页查询为10条时，执行时间多达4000ms，最后通过执行计划发现，唯一差别是： 10条时，使用的是nested loop； 20条时，用的是hash join； 最后只有强制oracle使用 hash join。具体 nested loop与hash join区别就不多说了 ...
oracle 主键关联 效率,Hash join与nested loops join的效率比较
weixin_35710880的博客
04-04 461
创建测试表SQL> create table t as select rownum id from dba_objects;Table created.SQL> create index idx_t on t(id);Index created.SQL> exec dbms_stats.gather_table_stats('sys','t',cascade=>true);...
SQL 改写系列十：半连接转内连接
OceanBase分布式数据库的博客
09-16 667
本文主要介绍OceanBase的半连接转内连接改写，以及这个改写的优化点、容易被忽略的错误。OceanBase会把满足一定条件的半连接转换成内连接，使优化器能够尝试更多的计划，生成的查询计划可能更优。
达梦数据库SQL执行计划常用操作符
不定期分享数据库相关内容。
04-15 2941
以rowid作为key，建hash表，如果下层数据过来没有hash匹配项则插入到hash表，和TEMP TABLE SPOOL(NTTS)不同的是，它的数据集不向父亲节点传送，而是被编号，用编号和KEY来定位访问；右表上有索引， 对于左表的每一行，利用索引在右表中定位。因为t2的行数很小，tx比较大，所以自动转为右连接，在t2上建hash的代价比较小。扫描左表建立hash表，扫描右表探测HASH表，最后输出被探测到的左表的行。先做一遍右儿子，然后执行左儿子，并把左儿子的数据向上送，直到左儿子不再有数据。
SQL调优:带函数的谓词导致CBO Cardinality计算误差
weixin_33871366的博客
11-12 94
今天处理了这样一问题，where条件中存在函数fun(date)<to_date('9999-01-01','YYYY-MM-DD')这样的无实际意义谓词，导致CBO计算基数时cardinality远小于实际情况，导致优化器认为2个源数据集的基数都不大，从而选择了HASH JOIN Right SEMI+SORT ORDER BY的执行计划，但是由...
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值