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

玉面飞龙 · 发表于 2007-8-10 10:44

还是看看原版的链接

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.

blue_prince · 发表于 2007-8-10 13:36

我测试了一下10G下的，过会看看11G

[php]

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0    Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.2.0 - Productio
NLSRTL Version 10.2.0.2.0 - Production

Elapsed: 00:00:00.07
SQL> create table gark
  2  (id1 number not null,
  3  id2 number not null,
  4  id3 number not null);

Table created.

Elapsed: 00:00:00.10
SQL> SQL>
SQL> begin
  2  for i in 1..100000 loop
  3  insert into gark(id1, id2, id3)
  4  values (i, i, i);
  5  end loop;
  6  commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.43
SQL> create unique index gark_idx on gark(id1, id3);

Index created.

Elapsed: 00:00:00.25
SQL> begin
  2  dbms_stats.gather_table_stats(
  3  user,
  4  'GARK',
  5  cascade=>true,
  6  estimate_percent=>100,
  7  method_opt=>'FOR ALL COLUMNS SIZE 254',
  8  no_invalidate=> false);
  9  end;
10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.32
SQL> set autotrace traceonly
SQL>
SQL> select /*+ use_nl(A B) */count(a.id3)
  2  from gark a, gark b
  3  where a.id1=b.id2;

Elapsed: 00:00:00.60

Execution Plan
----------------------------------------------------------
Plan hash value: 3257338542

--------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |       |    1 | 10 | 100K  (1)| 00:23:22 |
| 1 |  SORT AGGREGATE    |       |    1 | 10 |          |       |
| 2 | NESTED LOOPS    |       | 100K| 976K| 100K  (1)| 00:23:22 |
| 3 | TABLE ACCESS FULL| GARK    | 100K| 488K| 58 (2)| 00:00:01 |
|*  4 | INDEX RANGE SCAN | GARK_IDX |    1 |    5 |    1 (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

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

Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
   100327  consistent gets
      0  physical reads
      0  redo size
      519  bytes sent via SQL*Net to client
      469  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed
..
[/php]

BTW，NND，本来都下好了，结果磁盘空间不够，最后一分钟硬生生地把我给弄没了，害我重新下载，郁闷。

hotiice · 发表于 2007-8-10 13:39

最初由 blue_prince 发布
[B]我测试了一下10G下的，过会看看11G

BTW，NND，本来都下好了，结果磁盘空间不够，最后一分钟硬生生地把我给弄没了，害我重新下载，郁闷。 [/B]

用下载工具啊

blue_prince · 发表于 2007-8-10 13:41

最初由 hotiice 发布
[B]
用下载工具啊 [/B]

公司不能用下载工具的

alantany · 发表于 2007-8-10 14:46

下的好慢，只有70K，估计全世界都在下

blue_prince · 发表于 2007-8-10 15:45

11G的结果出来了

[php]

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0    Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

Elapsed: 00:00:00.00
SQL> create table gark
  2  (id1 number not null,
  3  id2 number not null,
  4  id3 number not null);

Table created.

Elapsed: 00:00:00.15
SQL> begin
  2  for i in 1..100000 loop
  3  insert into gark(id1, id2, id3)
  4  values (i, i, i);
  5  end loop;
  6  commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.34
SQL> create unique index gark_idx on gark(id1, id3);

Index created.

Elapsed: 00:00:00.28
SQL> begin
  2  dbms_stats.gather_table_stats(
  3  user,
  4  'GARK',
  5  cascade=>true,
  6  estimate_percent=>100,
  7  method_opt=>'FOR ALL COLUMNS SIZE 254',
  8  no_invalidate=> false);
  9  end;
10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.97
SQL> set autotrace traceonly

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

Elapsed: 00:00:00.14

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

--------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |       |    1 | 10 | 100K  (1)| 00:20:02 |
| 1 |  SORT AGGREGATE    |       |    1 | 10 |          |       |
| 2 | NESTED LOOPS    |       | 100K| 976K| 100K  (1)| 00:20:02 |
| 3 | TABLE ACCESS FULL| GARK    | 100K| 488K| 103 (1)| 00:00:02 |
|*  4 | INDEX RANGE SCAN | GARK_IDX |    1 |    5 |    1 (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

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

Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
   3323  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

..
[/php]