Research on 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
Initialize 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 ');
In experiment 1, object_id is used for association, with a 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 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
In experiment 2, object_type is used for association, with a 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 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
* ** 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 the efficiency is much higher when Maximum number of rows in a bucket is low. When Maximum number of rows in a bucket is high in SQL, try to reduce it and eliminate it.
