When 11g SQL is faster than 10g without any plan change…

11g is out !

We all know what Oracle Marketing will say : 11g is x% faster than 10g. And guess what ? I’ll need 3 more years to decipher all those changes that enhance the 11g optimizer and the query algorithms. This will probably be time for 12g then !

To avoid being too far behind you guys that are already upgrading to 11g, I’ve decided to invest on 11g during the Beta Program. This has been a lot of fun and I wish I can share some of my findings with you. For example, did you know that 11g can go faster without any change at all ? The queries below will illustrate the change made to the Nested Loop Algorithm in 11g :

1°- First, you have to create and fill a table to run your query :

create table gark
  (id1 number not null,
   id2 number not null,
   id3 number not null);

begin
for i in 1..100000 loop
insert into gark(id1, id2, id3)
values (i, i, i);
end loop;
commit;
end;
/

create unique index gark_idx on gark(id1, id3);

begin
dbms_stats.gather_table_stats(
   user,
   'GARK',
   cascade=>true,
   estimate_percent=>100,
   method_opt=>'FOR ALL COLUMNS SIZE 254',
   no_invalidate=> false);
end;
/

2°- Then, run the query below on a 10g database :

set autotrace traceonly

select /*+ use_nl(A B) */count(a.id3)
from gark a, gark b
where a.id1=b.id2;

Execution Plan
----------------------------
Plan hash value: 3137705415

--------------------------------------------------------
| Id  | Operation           | Name     | Rows  |  Cost |
--------------------------------------------------------
|   0 | SELECT STATEMENT    |          |     1 |  100K |
|   1 |  SORT AGGREGATE     |          |     1 |       |
|   2 |   NESTED LOOPS      |          |   100K|  100K |
|   3 |    TABLE ACCESS FULL| GARK     |   100K|    65 |
|*  4 |    INDEX RANGE SCAN | GARK_IDX |     1 |     1 |
--------------------------------------------------------

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

4 - access("A"."ID1"="B"."ID2")

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

3°- Run the same query on 11g (There is no trick here, the plan is the right plan, I’ve check it with dbms_xplan.display_cursor) :

set autotrace traceonly

select /*+ use_nl(A B) */count(a.id3)
from gark a, gark b
where a.id1=b.id2;

Execution Plan
----------------------------------------------------------
Plan hash value: 3137705415

-------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Cost |
-------------------------------------------------------
|   0 | SELECT STATEMENT    |          |     1 | 100K |
|   1 |  SORT AGGREGATE     |          |     1 |      |
|   2 |   NESTED LOOPS      |          |   100K| 100K |
|   3 |    TABLE ACCESS FULL| GARK     |   100K|  105 |
|*  4 |    INDEX RANGE SCAN | GARK_IDX |     1 |   11 |
-------------------------------------------------------

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

4 - access("A"."ID1"="B"."ID2")

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

If you know how the NL algorithm works in 10g, it’s quite easy to understand how it has been modified. The good news with that is that it won’t only speed up queries with optimal plans but also speed up queries with sub optimal plans that are using Nested Loops.

-Grégory

PS : Don’t dream, it won’t be so huge with a real workload and of course HASH JOIN stays the most efficient plan to solve this particular query.