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

Aug 9, 2007 / By Grégory Guillou

Tags: ,

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.

3 Responses to “When 11g SQL is faster than 10g without any plan change…”

  • Amit Poddar says:

    Hi,

    Could you help me understand what modification in the algorightm has so dramitcally brought down the consistent gets ?

    amit

  • arkzoyd says:

    More tests will help figure out how it works. I don’t know exactly the detail.

    Try to raise the index clustering factor and you’ll see the number of consistent gets raising too. From what I understand, instead of managing a row at a time from the 1st step of the NL, it deals with severals (That’s why it uses to manage more 100 000 blocks for 100 000 rows and now less). I’ve not been able to truly demonstrate it but I guess it works per block. Anyway what can been seen fom all the tests I’ve done, it’s always more efficient than 10g from a consistent gets perspective.

    Gregory

  • Hi,
    in the 10g example it needed 100556 Block accesses – means to me
    100000 Lookups fo the table
    556 for the index scan

    in the 11g example the number blocks of index scans is the same : 3373
    2817 aggregated (index rowid cached) lookups (some rowid processing …)
    556 for the index scans

    how many blocks has your table?
    Karl

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>