Tuning complex sub-queries with the no_unnest hint

Updated 1/18/2007

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:

http://oracledoug.com/serendipity/index.php?/archives/820-Topsy-Turvy.html

http://oracledoug.com/serendipity/index.php?/archives/818-Topsy-Turvy-II-The-plans.html

http://oracledoug.com/serendipity/index.php?/archives/813-Topsy-Turvy-III-The-Good-Plan.html

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 plan.”

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


				AND 
				pe.open_item_id IN (SELECT /*+ NO_UNNEST */ int_value

       FROM 
				v_extn_system_parameters

       WHERE 
				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 subquery.”

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.”

Update 1/18/07:

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. Burns notes:

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 consequences.

There are few free lunches in this business.

��