Saturday, September 06, 2014


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