对oracle hash join trace的研究

迈不过友情╰ 2022-08-01 03:02 175阅读 0赞

SQL> select \* from v$version;  
BANNER  
\--------------------------------------------------------------------------------  
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production  
PL/SQL Release 11.2.0.1.0 - Production  
CORE    11.2.0.1.0      Production  
TNS for Linux: Version 11.2.0.1.0 - Production  
NLSRTL Version 11.2.0.1.0 - Production

**初始化数据**

create table t1 as select \* from dba\_objects;

create table t2 as select \* from dba\_objects;  
insert into t2 select \* from  dba\_objects;  
commit;  
exec dbms\_stats.gather\_table\_stats(user,'t1');  
exec dbms\_stats.gather\_table\_stats(user,'t2');  
  
**实验1，使用object\_id关联，重复率低：**  
alter session set tracefile\_identifier = 'gg\_20150618';  
alter session set events '10104 trace name context forever, level 2';  
**select count(1) from t1,t2 where t1.object\_id = t2.object\_id;**  
alter session set events '10104 trace name context off' ;  
  
Number of buckets with   0 rows:      75222  
Number of buckets with   1 rows:      41633  
Number of buckets with   2 rows:      11624  
Number of buckets with   3 rows:       2218  
Number of buckets with   4 rows:        341  
Number of buckets with   5 rows:         32  
Number of buckets with   6 rows:          1  
Number of buckets with   7 rows:          1  
Number of buckets with   8 rows:          0  
Number of buckets with   9 rows:          0  
Number of buckets with between  10 and  19 rows:          0  
Number of buckets with between  20 and  29 rows:          0  
Number of buckets with between  30 and  39 rows:          0  
Number of buckets with between  40 and  49 rows:          0  
Number of buckets with between  50 and  59 rows:          0  
Number of buckets with between  60 and  69 rows:          0  
Number of buckets with between  70 and  79 rows:          0  
Number of buckets with between  80 and  89 rows:          0  
Number of buckets with between  90 and  99 rows:          0  
Number of buckets with 100 or more rows:          0  
\#\#\# Hash table overall statistics \#\#\#  
Total buckets: 131072 Empty buckets: 75222 Non-empty buckets: 55850  
Total number of rows: 73076  
**Maximum number of rows in a bucket: 7**  
**Average number of rows in non-empty buckets: 1.308433**  
Disabled bitmap filtering: filtered rows=0 minimum required=50 out of=1000  
qerhjFetch: max probe row length (mpl=0)  
\*\*\* RowSrcId: 2, qerhjFreeSpace(): free hash-join memory  
kxhfRemoveChunk: remove chunk 0 from slot table  
  
**实验2，使用object\_type关联，重复率高：**  
alter session set tracefile\_identifier = 'gg\_20150619';  
alter session set events '10104 trace name context forever, level 2';  
select count(1) from t1,t2 where t1.object\_type = t2.object\_type;  
alter session set events '10104 trace name context off' ;  
Number of buckets with   0 rows:     131027  
Number of buckets with   1 rows:          3  
Number of buckets with   2 rows:          2  
Number of buckets with   3 rows:          2  
Number of buckets with   4 rows:          2  
Number of buckets with   5 rows:          1  
Number of buckets with   6 rows:          0  
Number of buckets with   7 rows:          1  
Number of buckets with   8 rows:          0  
Number of buckets with   9 rows:          4  
Number of buckets with between  10 and  19 rows:          6  
Number of buckets with between  20 and  29 rows:          2  
Number of buckets with between  30 and  39 rows:          0  
Number of buckets with between  40 and  49 rows:          1  
Number of buckets with between  50 and  59 rows:          2  
Number of buckets with between  60 and  69 rows:          0  
Number of buckets with between  70 and  79 rows:          0  
Number of buckets with between  80 and  89 rows:          0  
Number of buckets with between  90 and  99 rows:          0  
Number of buckets with 100 or more rows:         19  
\#\#\# Hash table overall statistics \#\#\#  
Total buckets: 131072 Empty buckets: 131027 Non-empty buckets: 45  
Total number of rows: 73080  
**Maximum number of rows in a bucket: 27803  
Average number of rows in non-empty buckets: 1624.000000**  
Disabled bitmap filtering: filtered rows=0 minimum required=50 out of=1000  
  
\*\*\* 2015-06-18 16:35:58.344  
qerhjFetch: max probe row length (mpl=0)  
\*\*\* RowSrcId: 2, qerhjFreeSpace(): free hash-join memory  
kxhfRemoveChunk: remove chunk 0 from slot table  
  
   对比两个实验，你会发现，当Maximum number of rows in a bucket低的时候效率会高很多，当SQL中出现Maximum number of rows in a bucket高的情况，要想办法把它降低，打散。