oracle 执行计划为什么不走hash join？（转载）

langgufu

浏览: 2309716 次
性别:
来自: 北京

最近访客更多访客>>

geeksun

LiYunpeng

wx888jack

lifengxu

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

ORACLE数据库

hash join 执行计划 oracle

今天，某省的同事来告诉我，表重组后，他用于统计的一个sql脚本运行变慢了，之前只需要17、8分钟能出来的结果，现在1小时40分钟左右才能出来结果。

我们一起来看看脚本中的一个sql：

SQL> explain plan for
  2  select a.startdate,b.subsid from tab_1 a,tab_2 b where
  3  a.servid='025001003681' and a.status!='C' and a.mid=b.mid;

Explained.

Elapsed: 00:00:00.03

SQL> select * from table(dbms_xplan.display)
SQL> /

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 369 | 23985 | 980 | | |
| 1 |  NESTED LOOPS | | 369 | 23985 | 980 | | |
| 2 | PARTITION HASH ALL | | | | | 1 | 4 |
|*  3 |   TABLE ACCESS BY LOCAL INDEX ROWID| tab_1 | 369 | 14022 | 242 | 1 | 4 |
|*  4 | INDEX RANGE SCAN | IDX_tab_1_SERVID | 492 | |   10 | 1 | 4 |
| 5 | PARTITION HASH ITERATOR | | | | | KEY | KEY |
| 6 |   TABLE ACCESS BY LOCAL INDEX ROWID| tab_2 | 1 |   27 | 2 | KEY | KEY |
|*  7 | INDEX UNIQUE SCAN | PK_tab_2_MID | 1 | | 1 | KEY | KEY |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("A"."STATUS"<>'C')
   4 - access("A"."SERVID"='025001003681')
   7 - access("A"."MID"="B"."MID")

Note: cpu costing is off

22 rows selected.

Elapsed: 00:00:00.56

我们看到这个sql是通过索引后在走nested loops，我们做一个sqltrace来观察一下它的执行时间和consistent gets：

SQL> set timing on
SQL> set autotrace traceonly
SQL> select a.startdate,b.subsid from tab_1 a,tab_2 b where
  2  a.servid='025001003681' and a.status!='C' and a.mid=b.mid;

627965 rows selected.

Elapsed: 00:36:13.04

Execution Plan
----------------------------------------------------------
   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=980 Card=369 Bytes=2
   3985)

   1   0 NESTED LOOPS (Cost=980 Card=369 Bytes=23985)
   2   1 PARTITION HASH (ALL)
   3   2 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'tab_1'
   (Cost=242 Card=369 Bytes=14022)

   4   3 INDEX (RANGE SCAN) OF 'IDX_tab_1_SERVID' (NON
   -UNIQUE) (Cost=10 Card=492)

   5   1 PARTITION HASH (ITERATOR)
   6   5 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'tab_2' (C
   ost=2 Card=1 Bytes=27)

   7   6 INDEX (UNIQUE SCAN) OF 'PK_tab_2_MID' (UNIQUE)
   (Cost=1 Card=1)

Statistics
----------------------------------------------------------
   0 recursive calls
   0  db block gets
   2725638  consistent gets
     406711  physical reads
     1120  redo size
   20890925  bytes sent via SQL*Net to client
     461160  bytes received via SQL*Net from client
   41866  SQL*Net roundtrips to/from client
   0  sorts (memory)
   0  sorts (disk)
     627965  rows processed

SQL>

我们看到有270多万个consistent gets，并且这个sql也确实要36分钟才能运行出来，那为什么同事说之前10多分钟就能跑出来？我们来试试hash连接：

SQL> select /*+ use_hash(a,b) */a.startdate,b.subsid from tab_1 a,tab_2 b where
a.servid='025001003681' and a.status!='C' and a.mid=b.mid;  2

628514 rows selected.

Elapsed: 00:04:21.90

Execution Plan
----------------------------------------------------------
   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=20594 Card=369 Bytes
   =23985)

   1   0 HASH JOIN (Cost=20594 Card=369 Bytes=23985)
   2   1 PARTITION HASH (ALL)
   3   2 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'tab_1'
   (Cost=242 Card=369 Bytes=14022)

   4   3 INDEX (RANGE SCAN) OF 'IDX_tab_1_SERVID' (NON
   -UNIQUE) (Cost=10 Card=492)

   5   1 PARTITION HASH (ALL)
   6   5 TABLE ACCESS (FULL) OF 'tab_2' (Cost=20251 Card=7
   199100 Bytes=194375700)

Statistics
----------------------------------------------------------
   0 recursive calls
   0  db block gets
     400061  consistent gets
     308119  physical reads
   560  redo size
   20909078  bytes sent via SQL*Net to client
     461556  bytes received via SQL*Net from client
   41902  SQL*Net roundtrips to/from client
   0  sorts (memory)
   0  sorts (disk)
     628514  rows processed

SQL>

呀，走hash连接的话，只要4分多钟就能跑出来，且consistent gets也仅仅只有40万，物理读也小了不少。看来oracle确实没有选择合适的执行计划了。
可oracle为什么没有选择合适的执行计划？难道是统计信息的问题？

SQL> select TABLE_NAME,LAST_ANALYZED from user_tables where table_name in ('tab_2','tab_1');

TABLE_NAME LAST_ANAL
------------------------------ ---------
tab_2 29-JAN-08
tab_1 11-JUN-08

难道是tab_2的统计信息太久了导致执行计划出错？尝试备份统计信息并重新分析tab_2之后，再次查看执行计划：

SQL> exec dbms_stats.gather_table_stats(OWNNAME=>'MISC',tabname=>'tab_2',cascade=>TRUE,estimate_percent=> 50);

PL/SQL procedure successfully completed.

Elapsed: 00:18:37.95
SQL>
SQL>
SQL>
SQL> explain plan for
  2  select a.startdate,b.subsid from tab_1 a,tab_2 b where
  3  a.servid='025001003681' and a.status!='C' and a.mid=b.mid;

Explained.

Elapsed: 00:00:00.00

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 369 | 23985 | 980 | | |
| 1 |  NESTED LOOPS | | 369 | 23985 | 980 | | |
| 2 | PARTITION HASH ALL | | | | | 1 | 4 |
|*  3 |   TABLE ACCESS BY LOCAL INDEX ROWID| tab_1 | 369 | 14022 | 242 | 1 | 4 |
|*  4 | INDEX RANGE SCAN | IDX_tab_1_SERVID | 492 | |   10 | 1 | 4 |
| 5 | PARTITION HASH ITERATOR | | | | | KEY | KEY |
| 6 |   TABLE ACCESS BY LOCAL INDEX ROWID| tab_2 | 1 |   27 | 2 | KEY | KEY |
|*  7 | INDEX UNIQUE SCAN | PK_tab_2_MID | 1 | | 1 | KEY | KEY |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("A"."STATUS"<>'C')
   4 - access("A"."SERVID"='025001003681')
   7 - access("A"."MID"="B"."MID")

Note: cpu costing is off

22 rows selected.

Elapsed: 00:00:00.02

天，竟然还是走nl！！

难道是因为servid中的倾斜度的问题？再次查了一下tab_1表中servid=’025001003681′

SQL> select count(*) from tab_1 a where a.servid='025001003681'
  2 /

  COUNT(*)
----------
   1564299

#### 而其他的servid的值：
SQL> select servid from tab_1 where rownum<=10;

SERVID
------------
010001000983
010001001058
010001001327
010001001327
010001001327
010001001327
010001001327
010001001327
010001001327
010001001327

10 rows selected.

Elapsed: 00:00:00.02
SQL> select count(*) from tab_1 a where a.servid='010001001327';

  COUNT(*)
----------
   33

Elapsed: 00:00:00.06
SQL> select count(*) from tab_1 a where a.servid='010001000983';

  COUNT(*)
----------
   2

Elapsed: 00:00:00.00
SQL> select count(*) from tab_1 a where a.servid='010001000983';

  COUNT(*)
----------
   2

可以看到其他值的返回数据量确实很小，在返回数据量小的情况下，我们走索引确实是一种高效的查询方式，但是当返回数据很多时，由于nl的比较要花大量的时间，因此时间也就基本消耗在这里了。

对于某一列中的不同数值有不同的倾斜度（skew），要选择不同的执行计划，我们需要在收集直方图：

SQL> exec dbms_stats.gather_table_stats(OWNNAME=>'MISC',tabname=>'tab_1',degree=>4,cascade=>TRUE,estimate_percent=> 50,method_opt => 'FOR COLUMNS servid SIZE auto');

PL/SQL procedure successfully completed.

Elapsed: 00:12:48.22

此时，我们再次检查其执行计划的时候，看到已经走了hash连接了：

SQL> explain plan for
  2  select a.startdate,b.subsid from tab_1 a,tab_2 b where
  3  a.servid='025001003681' and a.status!='C' and a.mid=b.mid;

Explained.

Elapsed: 00:00:00.01
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1172K| 72M| | 39071 | | |
|*  1 |  HASH JOIN | |  1172K|   72M|   55M| 39071 | | |
| 2 | PARTITION HASH ALL | | | | | | 1 | 4 |
|*  3 |   TABLE ACCESS FULL | tab_1 |  1172K|   42M| | 13663 | 1 | 4 |
| 4 | PARTITION HASH ALL | | | | | | 1 | 4 |
| 5 |   TABLE ACCESS FULL | tab_2 |  7199K| 185M| | 20251 | 1 | 4 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."MID"="B"."MID")
   3 - filter("A"."SERVID"='025001003681' AND "A"."STATUS"<>'C')

Note: cpu costing is off

19 rows selected.

Elapsed: 00:00:00.02
SQL>
SQL>

这个时候，我们不加hints，oracle已经正确的选择了执行计划。至此，不走hash join是因为索引列的倾斜度的问题，收集直方图后，问题搞定。

分享到：

spring配置datasource的三种方式（转载） | 详细介绍ORACLE sqlplus命令(转)

2015-04-07 14:34
浏览 5586
评论(0)
分类:数据库
查看更多

发表评论

您还没有登录,请您登录后再发表评论

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论