Oracle Scratchpad

March 20, 2008

ANSI SQL

Filed under: ANSI Standard,CBO,Infrastructure,Troubleshooting — Jonathan Lewis @ 4:26 pm BST 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 9.2.0.6):


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.

4 Comments »

  1. Jonathan, probably you are aware of this, but even the RBO can have different execution plans; see http://frits.homelinux.com/wordpress/?p=13

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

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

    Comment by Dion Cho — March 20, 2008 @ 11:58 pm BST 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

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,530 other followers