A hash join can be either an optimal hash join, a one-pass hash join, or a multi-pass hash join.
- Optimal hash joins – The entire build table fits nicely into memory
- One-pass hash joins – The build table did not fit entirely into memory, but we were table to swap partitions of the hash join around, and as a result we only read the table 1 time.
- Multi-pass hash joins – Not only did the build table not fit in memory, but the individual partitions for the hash join didn’t fit into memory. As a result, we had to process the hash join by loading portions of the partition into memory and re-scanning the probe table. The result is the probe table is read multiple times. Fortunately, these are usually rare unless your work area is grossly undersized.
If you have a slow-running hash join, it’s often useful to determine whether or not you have an optimal, one-pass, or multi-pass hash join on your hands.
There are multiple ways of telling what kind of hash join you used.
Check v$sql_workarea
This view contains the columns SQL_ID, CHILD_NUMBER, and ADDRESS, which is enough to uniquely identify a single execution plan.
Using the OPERATION_ID, you can match it with the line in the execution plan. This allows you to determine exactly which operation in the execution plan we’re concerned with.
This view shows the ESTIMATED_OPTIMAL_SIZE along with the ESTIMATED_ONEPASS_SIZE to display what the optimizer thought the estimated sizes were at parse time. The truly interesting bits come from the columns OPTIMAL_EXECUTIONS, ONEPASS_EXECUTIONS, and MULTIPASSES_EXECUTIONS.
Notice that this view doesn’t offer information about a specific execution of the query, since this view is an aggregate of all executions for a given plan. There is, however, a LAST_EXECUTION column which will show whether the last execution was optimal, single-pass, or multi-pass
Check v$mystat or v$sesstat
You can use either of these views to identify the number of optimal, single-pass, and multi-pass operations on a hash join for a session.
The v$mystat view only shows statistics for the current session you’re in.
The v$sesstat will show you session statistics for any database session that’s currently logged in. This might be useful when you have multiple sessions working towards the same goal (e.g. a job that uses DBMS_PARALLEL_EXECUTE). For v$sesstat, you need to find the session you care about using the SID column.
Either of these views will join to v$statname using the STATISTIC# column. From there, you can look for the following statistics:
workarea executions - optimalworkarea executions - onepassworkarea executions - multipass
Conclusion
Remember that a hash join doesn’t have to be all one-pass or all multi-pass. You can have a hash join where some partitions are joined with single-pass operations, some with multi-pass operations, and some with optimal executions. Also remember that sort operations can have single-pass, multi-pass, or optimal executions as well. This means the above views may refer to sort operations as well.
Free Oracle SQL Tuning Guide
Checkout my FREE guide, 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!
Get it here: tuningsql.com/secrets
