Tony’s Oracle Tips

April 19, 2009

A Fancy CBO Shortcut in Hash and Merge Joins

Filed under: Uncategorized — tonyhasler @ 6:16 pm

I came across a new CBO optimisation last week. It is quite straightforward and obvious but not something I was previously aware of. To demonstrate the point you can run the following script from SQL*PLUS:


set feedback off
drop table t1;
drop table t2;
drop table t3;

set lines 132
column operation format a30
column object_alias format a10
column sid new_value sid noprint
select sys_context('USERENV','SID') sid from dual;

variable var1 number;
create table t1 (col1 integer) pctfree 99;
create table t2 (col2 integer) pctfree 99;
create table t3 (col3 integer) pctfree 99;

insert into t1 select 2*rownum from dual connect by level <=10000;
insert into t2 select 2*rownum+1 from dual connect by level <=10000;
insert into t3 select rownum from dual connect by level <=10) ;
exec dbms_stats.gather_table_stats(user,'T1',estimate_percent=>100) ;
exec dbms_stats.gather_table_stats(user,'T2',estimate_percent=>100) ;
exec dbms_stats.gather_table_stats(user,'T3',estimate_percent=>100) ;

set autotrace on

exec :var1 := 7;

select * from t1 join t2 on col1=col2
join t3 on col3=col1+col2 and col3=:var1;

exec :var1 := 2001 ;

select * from t1 join t2 on col1=col2
join t3 on col3=col1+col2 and col3=:var1;

select * from t1 join t2 on col1=col2
join t3 on col3=col1+col2 and col3=2001;

Here is the output I got using a 280M SGA on 10.2.0.3:


Execution Plan
----------------------------------------------------------
Plan hash value: 2828770442

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |   100 |  1100 |   668   (1)| 00:00:09 |
|*  1 |  HASH JOIN            |      |   100 |  1100 |   668   (1)| 00:00:09 |
|   2 |   TABLE ACCESS FULL   | T2   | 10000 | 40000 |   332   (1)| 00:00:04 |
|   3 |   MERGE JOIN CARTESIAN|      | 10000 | 70000 |   335   (1)| 00:00:05 |
|*  4 |    TABLE ACCESS FULL  | T3   |     1 |     3 |     3   (0)| 00:00:01 |
|   5 |    BUFFER SORT        |      | 10000 | 40000 |   332   (1)| 00:00:04 |
|   6 |     TABLE ACCESS FULL | T1   | 10000 | 40000 |   332   (1)| 00:00:04 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("COL1"="COL2")
       filter("T1"."COL1"+"T2"."COL2"=TO_NUMBER(:var1))
   4 - filter("COL3"=TO_NUMBER(:var1))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       2909  consistent gets
          0  physical reads
          0  redo size
        381  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed


Execution Plan
----------------------------------------------------------
Plan hash value: 2828770442

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |   100 |  1100 |   668   (1)| 00:00:09 |
|*  1 |  HASH JOIN            |      |   100 |  1100 |   668   (1)| 00:00:09 |
|   2 |   TABLE ACCESS FULL   | T2   | 10000 | 40000 |   332   (1)| 00:00:04 |
|   3 |   MERGE JOIN CARTESIAN|      | 10000 | 70000 |   335   (1)| 00:00:05 |
|*  4 |    TABLE ACCESS FULL  | T3   |     1 |     3 |     3   (0)| 00:00:01 |
|   5 |    BUFFER SORT        |      | 10000 | 40000 |   332   (1)| 00:00:04 |
|   6 |     TABLE ACCESS FULL | T1   | 10000 | 40000 |   332   (1)| 00:00:04 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("COL1"="COL2")
       filter("T1"."COL1"+"T2"."COL2"=TO_NUMBER(:var1))
   4 - filter("COL3"=TO_NUMBER(:var1))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1458  consistent gets
          0  physical reads
          0  redo size
        381  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed


Execution Plan
----------------------------------------------------------
Plan hash value: 3204703634

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |    50 |   550 |   668   (1)| 00:00:09 |
|*  1 |  HASH JOIN            |      |    50 |   550 |   668   (1)| 00:00:09 |
|   2 |   MERGE JOIN CARTESIAN|      |  5000 | 35000 |   335   (1)| 00:00:05 |
|*  3 |    TABLE ACCESS FULL  | T3   |     1 |     3 |     3   (0)| 00:00:01 |
|   4 |    BUFFER SORT        |      | 10000 | 40000 |   332   (1)| 00:00:04 |
|   5 |     TABLE ACCESS FULL | T1   | 10000 | 40000 |   332   (1)| 00:00:04 |
|   6 |   TABLE ACCESS FULL   | T2   | 10000 | 40000 |   332   (1)| 00:00:04 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("COL1"="COL2")
       filter("T1"."COL1"+"T2"."COL2"=2001)
   3 - filter("COL3"=2001)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        381  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

We can see that there can never be any rows returned but what is interesting is that the number of consistent gets decreases each time from 2909 to 1458 and finally to 7.

For an explanation we can have a look at the LAST_STARTS column from V$SQL_PLAN_STATISTICS_ALL for each of the queries. Here is the script to run:


set autotrace off
set verify off
alter session set statistics_level=all;

exec :var1 := 7;

select * from t1 join t2 on col1=col2
join t3 on col3=col1+col2 and col3=:var1;

select rpad(' ',4*depth) || operation operation,options,
object_alias,last_starts
from v$sql_plan_statistics_all a
join v$session s
on s.prev_sql_id=a.sql_id and s.prev_child_number=a.child_number
and s.sid=&&sid
order by id;

exec :var1 := 2001 ;

select * from t1 join t2 on col1=col2
join t3 on col3=col1+col2 and col3=:var1;

select rpad(' ',4*depth) || operation operation,options,
object_alias,last_starts
from v$sql_plan_statistics_all a
join v$session s
on s.prev_sql_id=a.sql_id and s.prev_child_number=a.child_number
and s.sid=&&sid
order by id;

select * from t1 join t2 on col1=col2
join t3 on col3=col1+col2 and col3=2001;

select rpad(' ',4*depth) || operation operation,options,
object_alias,last_starts
from v$sql_plan_statistics_all a
join v$session s
on s.prev_sql_id=a.sql_id and s.prev_child_number=a.child_number
and s.sid=&&sid
order by id;

and here is the output



OPERATION                      OPTIONS                        OBJECT_ALI LAST_STARTS
------------------------------ ------------------------------ ---------- -----------
    HASH JOIN                                                                      1
        TABLE ACCESS           FULL                           T2@SEL$1             1
        MERGE JOIN             CARTESIAN                                           1
            TABLE ACCESS       FULL                           T3@SEL$2             1
            BUFFER             SORT                                                1
                TABLE ACCESS   FULL                           T1@SEL$1             1

OPERATION                      OPTIONS                        OBJECT_ALI LAST_STARTS
------------------------------ ------------------------------ ---------- -----------
    HASH JOIN                                                                      1
        TABLE ACCESS           FULL                           T2@SEL$1             1
        MERGE JOIN             CARTESIAN                                           1
            TABLE ACCESS       FULL                           T3@SEL$2             1
            BUFFER             SORT                                                0
                TABLE ACCESS   FULL                           T1@SEL$1             0

OPERATION                      OPTIONS                        OBJECT_ALI LAST_STARTS
------------------------------ ------------------------------ ---------- -----------
    HASH JOIN                                                                      1
        MERGE JOIN             CARTESIAN                                           1
            TABLE ACCESS       FULL                           T3@SEL$2             1
            BUFFER             SORT                                                0
                TABLE ACCESS   FULL                           T1@SEL$1             0
        TABLE ACCESS           FULL                           T2@SEL$1             0

In the first case all steps in the plan are executed. In the second case, where no matching row in table T3 is found, the merge join does not attempt to evaluate the second/probe row source at all. Quite sensible really as no row could possible match an element of an empty set. In the third case a new execution plan has been derived that results in T3 being read first. In this case the hash join exhibits the same behaviour and the result is derived even quicker.

Advertisements

Leave a Comment »

No comments yet.

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

Blog at WordPress.com.

%d bloggers like this: