还是看看原版的链接
http://www.pythian.com/blogs/542/my-work-before-12g-goes-out
« Oracle 11g (11.1.0.6) and Documentation Available on OTNWhen 11g SQL is faster than 10g without any plan change…
August 9th, 2007 - by Gregory Guillou
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 processed3°- 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 processedIf 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.
