Oracle Scratchpad

March 20, 2008


Filed under: ANSI Standard,CBO,Infrastructure,Troubleshooting — Jonathan Lewis @ 4:26 pm GMT Mar 20,2008

Someone sent me an email a little while ago about a problem they were having with two databases that were using different execution plans for the same query. 

But the two databases were believed to be identical, and the optimizer was running rule-based in both cases, so it shouldn’t have been possible to get different execution plans.

Here’s a little catch (cut and paste from a session running under

SQL> alter session set optimizer_mode = rule; 

Session altered. 

SQL> set autotrace traceonly explain
SQL> select t1.n2, t2.n2
  2  from t1 left join t1 t2 on t1.n1 = t2.n1
  3  ; 

Execution Plan
   0      SELECT STATEMENT Optimizer=RULE (Cost=192 Card=450000 Bytes=7200000)
   1    0   HASH JOIN (OUTER) (Cost=192 Card=450000 Bytes=7200000)
   2    1     TABLE ACCESS (FULL) OF 'T1' (Cost=75 Card=150000 Bytes=1200000)
   3    1     INDEX (FAST FULL SCAN) OF 'T1_I1' (NON-UNIQUE) (Cost=25 Card=150000 Bytes=1200000)

Notice how line 0 of the plan shows that we are running RULE based. But every line has a cost, and the join method is a hash join – which is an option only available to the cost based optimizer.

The use of ANSI syntax has caused the RBO to hand off to the CBO. Once you’re running cost-based with no statistics, there are all sorts of little things that might be enough to cause unexpected behaviour in execution plan.


  1. Jonathan, probably you are aware of this, but even the RBO can have different execution plans; see

    Comment by Frits Hoogland — March 20, 2008 @ 8:27 pm GMT Mar 20,2008 | Reply

  2. Both cases are quite interesting. Thanks for sharing.

    Comment by Dion Cho — March 20, 2008 @ 11:58 pm GMT Mar 20,2008 | Reply

  3. Yes, that’s quite an eye-opener.

    Comment by Robert V — April 30, 2008 @ 7:28 pm BST Apr 30,2008 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by