10g Subquery Unnesting Anomalies

Filed Under Oracle, Technical |

I’ve been spending quite a bit of time over the past week studying subquery optimization. It started with an anomaly I noticed at a client site and led me through a refresher on Jonathan Lewis’ chapter on transformations and through a lot of time reading trace files. I’ll probably write a few posts about subqueries… but as a quick starter here’s the anomaly that got me started with the whole thing.
First things first: my demos in this article will be from Oracle 10.2.0.2. Subquery optimization is an area of active development and there are even changes between point releases of Oracle. I saw different plans generated for some of my test cases between 10g releases 1, 2 and 3. The differences are even more dramatic between major releases - not to mention between different databases. Oracle 8i unnested nothing, 9i unnested everything, and 10g tries to work out the cost. MySQL can’t unnest and can’t use indexes on FROM clause subqueries since it always materializes them. (Just yesterday I finally listened to Timour Katchaounov’s “Query Optimizer Internals” presentation from this year’s MySQL Conference where he spent a lot of time talking about MySQL’s capabilities and limitations with subqueries.)
Oracle 10g doesn’t always get it right either, although - not surprisingly - it is far more sophisticated than MySQL in how it handles subqueries. The “anomaly” that I observed was 10g unnesting a subquery when it was clearly cheaper not to unnest it. The original query was a bit complex and the subquery existed to find the current row in an SCD type 2 table. (If you have these tables then I’d wager that you use subqueries for this same purpose and could run into this exact problem!)

Reproducing the Problem
My first thought was that 10g wasn’t actually costing the subquery and was automatically unnesting it. But in the spirit of BAAG (and since I didn’t want to jump to conclusions) I started digging a little deeper.
The client system was pretty complex so the first step was to try reproducing the problem in a simpler environment. Since Jonathan Lewis has provided test cases for query transformations - including subquery unnesting - this seemed a natural place to start. By starting with his scripts it didn’t take me long to reproduce the problem. To reproduce my environment run ch_09_transformations\unnest_cost_01a.sql from Lewis’ CBO scripts.
Here’s the bird’s eye view:
SQL> alter session set "_optimizer_cost_model"=choose;

SQL> set autotrace traceonly explain;

SQL> select *
from dept
where exists (
select 'X'
from emp outer
where outer.sal =
(select
max(inner.sal)
from emp inner
where inner.dept_no >= outer.dept_no
) and outer.dept_no=dept.dept_no
);

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | | 651 (35)| 00:00:08 |
|* 1 | FILTER | | | | | | |
| 2 | TABLE ACCESS FULL | DEPT | 6 | 30 | | 3 (0)| 00:00:01 |
|* 3 | FILTER | | | | | | |
| 4 | HASH GROUP BY | | 1 | 43 | | 216 (35)| 00:00:03 |
| 5 | MERGE JOIN | | 166K| 6998K| | 158 (11)| 00:00:02 |
| 6 | SORT JOIN | | 1000 | 8000 | | 59 (7)| 00:00:01 |
|* 7 | TABLE ACCESS FULL| EMP | 1000 | 8000 | | 58 (6)| 00:00:01 |
|* 8 | SORT JOIN | | 3333 | 113K| 360K| 92 (6)| 00:00:02 |
|* 9 | TABLE ACCESS FULL| EMP | 3333 | 113K| | 58 (6)| 00:00:01 |
---------------------------------------------------------------------------------------

SQL> select *
from dept
where exists (
select /*+ no_unnest */ 'X'
from emp outer
where outer.sal =
(select /*+ no_unnest */
max(inner.sal)
from emp inner
where inner.dept_no >= outer.dept_no
) and outer.dept_no=dept.dept_no
);

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 349 (5)| 00:00:05 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | DEPT | 6 | 30 | 3 (0)| 00:00:01 |
|* 3 | FILTER | | | | | |
|* 4 | TABLE ACCESS FULL | EMP | 3333 | 26664 | 58 (6)| 00:00:01 |
| 5 | SORT AGGREGATE | | 1 | 8 | | |
|* 6 | TABLE ACCESS FULL| EMP | 1000 | 8000 | 58 (6)| 00:00:01 |
-----------------------------------------------------------------------------
Is Jonathan Lewis perpetuating a myth that Oracle 10g will unnest subqueries based on cost?!
As it turns out… of course not. So what’s going on here?
I poked and probed this query in dozens of different ways but ultimately ended up in the 10053 trace. And although I still don’t consider myself to understand it entirely it did shed enough light on this query to figure out what was going on.
About The Cost-Based Optimizer

Oracle’s main workhorse for cost-based query optimization seems to be a function called kkoqbc() which calculates the cost of a single query block. (Kernel Kompile Optimizer Query Block Cost?) As the performance tuning guide illustrates, Oracle attempts to transform the query before generating plans. Jonathan demonstrated this in his post by showing how a query was still transformed in 9i even when the rule-based optimizer was used.
The trace file also demonstrates that transformations happen before optimization - I could always see the CBO always following the same sequence of events:
1. Predicate Move-Around (PM)
2. Pre-transform Simple Filter Push-Down
3. Subquery Unnesting (automatic and cost-based)
4. Complex View Merging
5. Set-Join Conversion
6. Predicate Move-Around
7. Predicate Pull-Up
8. Join Predicate Push-Down
9. Post-Transform Simple Filter Push-Down
10. Generate Transitive Predicates (check constraints and other parts of query)
11. Generate Plans [i.e. call kkoqbc()]
You could think of these as the “rules” for the cost-based optimizer. :) Each of the first 10 steps can transform the query in one way or another before Oracle even attempts to generate a plan!
The most interesting bit, though, is what happens when there’s a subquery that’s eligible for unnesting. You still see the same sequence of events - except that in the middle of steps 3 and 4 (subquery unnesting and complex view merging) you see calls to kkoqbc() and the corresponding trace data. You’re watching cost-based query transformations in action.
Back to the Original Problem
So what did I discover when looking at the tracefiles for the query above? This is what’s interesting… for some reason (which I haven’t fully figured out yet) kkoqbc() was generating a different cost for the untransformed subquery when called during the unnest phase. The cost of the transformed subquery after unnesting and view merging came out to 231. The cost of the untransformed subquery came out to 403 when calculated during the unnest phase - and 115 when calculated at the end (using hints to prevent unnesting).
In the case of my client a few weeks ago this unnest operation was absolutely killing query performance. Perhaps I’ll find a more elegant solution as I continue digging into subquery optimization but in the meantime the quick fix was simply using the NO_UNNEST hint to prevent subquery unnesting as I demonstrated above.
As I mentioned before, if you have SCD type 2 data - and everyone running Oracle Applications or Peoplesoft has this - then look out since you probably use nested subqueries to get current records. Unnesting is usually good - but occasionally Oracle gets it wrong. If you have a query that’s running slow then it might be worth just trying a few NO_UNNEST or UNNEST hints and seeing if it makes a difference.
Footnote: If anyone’s especially curious, you’re welcome to have a look at the trace files for yourself - with subquery unnesting and without subquery unnesting via hint. Of course you should also be able to reproduce the same results on 10.2.0.2 by using Lewis’ scripts for setup and running the my queries from this article.