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
Initializing data
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 ');
Experiment 1, using OBJECT_ID Association, low repetition rate:
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 and rows:0
Number of buckets with between and rows:0
Number of buckets with between and rows:0
Number of buckets with between and rows:0
Number of buckets with between and rows:0
Number of buckets with between and rows:0
Number of buckets with between and rows:0
Number of buckets with between and rows:0
Number of buckets with between and rows:0
Number of buckets with + 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
Experiment 2, using Object_type Association, high repetition rate:
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 and rows:6
Number of buckets with between and rows:2
Number of buckets with between and rows:0
Number of buckets with between and rows:1
Number of buckets with between and rows:2
Number of buckets with between and rows:0
Number of buckets with between and rows:0
Number of buckets with between and rows:0
Number of buckets with between and rows:0
Number of buckets with + 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
Comparing the two experiments, you will find that when the maximum number of rows in a bucket is low, the efficiency will be much higher, and when the maximum number of rows in a bucket is high in SQL, think of ways to reduce it and break it up.
A study of Oracle hash join trace
