Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








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:


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.


Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational