Saturday, September 06, 2014

Oracle SQL Hint NO_UNNEST


One of my custom program suddenly started showing poor performance. Following sql was the culprit



This was the sql plan



The table AP_INVOICE_DISTRIBUTIONS_ALL has more than 100 million rows, so HASH JOIN did not work for me. In this case hint NO_UNNEST was very useful. (/*+ NO_UNNEST */ was added to subquery to force optimizer to not UNNEST)
I think in this case if Oracle optimizer had unnested subquery and used nested loop, it might have been better performance. i have noticed that in our instance whenever a large table is used in HASH JOIN, it causes problem.

The sql plan after adding the hint

Although total cost for sql plan with hint was much higher, it took just few seconds. But without hint, it took 30 minutes.

No comments:

Post a Comment