Tuning complex sub-queries with the no_unnest
This blog post by Doug Burns (an outstanding disseminator of Oracle
knowledge) shows an excellent example of an
Oracle Silver Bullet (as defined as the case where a small
change has a profound impact on performance) by introducing the
no_unnest hint to a subquery that accessed data via a database
link, resulting in a 300x performance improvement:
Burns reports that using the Oracle no_unnest hint dropped his query
from 5 hours to less than a minute:
“My favourite bit, earlier in the
evening, was improving the performance of one of the weekly batch
jobs from 5 hours down to less than a minute.”
Doug suggests that the problem was with latency form the database
link and that a CBO feature (possibly a cardinality estimation
issue) caused the query to introduce the remote subquery too early
in the SQL execution:
“The problem is that Oracle
was joining this remote table to a local table at a very early
stage of the query, impacting later stages of the execution
Doug notes that the no_unnest hint did not unnest the subquery.
Rather, he reports that the no_unnest hint forced the CBO to
disqualify the subquery as a join candidate:
“The problem is that Oracle was joining
this remote table to a local table at a very early stage of the
query, impacting later stages of the execution plan. All I really
wanted to do was to stop it doing that. Here was the particular
solution that worked for this particular query
pe.open_item_id IN (SELECT /*+ NO_UNNEST */ int_value
process_key = 'CAFTE003'
AND parameter_name LIKE 'ACCRUED%'))
This hint made the CBO treat this subquery as a seperate (sic)
subquery and not a candidate for a join. i.e. It didn't unnest the
Burns concludes about the no_unnest hint:
“Using NO_UNNEST was a pretty blunt tool because I didn't say -
'here's what that table really looks like' - I just said - 'don't
even think about joining this'.
Maybe this would be a better summary of what was tuned here ...
The CBO was getting the cardinality of the remote table wrong,
deciding it was a good join candidate, when it wasn't, so I forced
it not to consider it.”
Burns wishes to add the following clarifications to our report
on his tuning success:
1) I was concerned that readers might misinterpret my
intentions when I wrote that blog entry, to the extent of saying
the following, which Don didn't quote
"When you see the solution, the words 'Silver' and 'Bullet'
might pop into your mind.
If so, let me point out that I worked it out by poring
over execution plans, trying different approaches in full-sized
test environments and, when you look at the problem, it's
actually a very localised problem with a large query"
Burns also makes these erudite warnings that single-changes that
result in dramatic performance improvements (what I call Silver
Bullets), are not identified without great effort, nor without risk
or unintended side-effects.
In case you are a beginner, take heed of a fundamental DBA
principle: ALWAYS thoroughly test all global changes in your
test environment before placing them in your production system.
If I don't know *why* it worked, how can I be sure that it's
really fixed the problem and hasn't introduced new problems?
Even if I decide to ignore such details, an inflexible fix
like this might well cause problems when you upgrade to future
versions of Oracle, because a new and more efficient execution
plan is unavailable as a consequence.
The fact is, I always worry about such an apparently
easy solution because there are likely to be unexpected
There are few free lunches in this business.