Question & Answer
Question
How can I identify the snippet where the plan is performing a disk-based hash join (DHJ)?
Answer
A disk-based hash join (DHJ) is a costly operation from the perspective of performance and should be avoided.
A query may perform DHJ if the operation performed by a snippet of the plan does not have enough memory for processing. When this occurs, the original plan will be modified. The modified plan will have the old plan followed by the new plan. The modified plan will have "DHJ" at the start of the new plan being appended to the original plan.
To identify the snippet and node which is using a DHJ, search for the string, diskBased in the plan file. The following is an excerpt of the plan file in which DHJ is performed:
- 21[12]: spu HashJoinNode diskBased=true nParts=1456
table 1005805850 numHashCols=1 joinType=0 joinQual=1634d690
otherQual=0 flags=0
In this example, node 12 of snippet 21 is the operation using DHJ. Using this information, you must analyze whether the plan is bad, and if so, whether this is due to outdated statistics or a poorly-formed query.
Historical Number
NZ406289
Was this topic helpful?
Document Information
Modified date:
17 October 2019
UID
swg21578081