对oraclehashjointrace的研究

对oraclehashjointrace的研究

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高的情况，要想办法把它降低，打散。