ITPub博客

不同的连接方式性能对比!

原创 作者:warehouse 时间:2008-10-13 22:48:47 0 删除 编辑

表与表之间错误的连接方式会对性能产生巨大影响。

[@more@]

SQL> create table t(id int , name char(10));

表已创建。

SQL> create table tt(id int , name char(2000));

表已创建。
SQL> begin
2 for i in 1..10000 loop
3 insert into t values(i,'test');
4 end loop;
5 commit;
6 end;
7 /

PL/SQL 过程已成功完成。

SQL> begin
2 for i in 1..10000 loop
3 insert into tt values(i,'test1');
4 end loop;
5 commit;
6 end;
7 /

PL/SQL 过程已成功完成。

SQL> exec dbms_stats.gather_table_stats('TEST','T');

PL/SQL 过程已成功完成。

SQL> exec dbms_stats.gather_table_stats('TEST','TT');

PL/SQL 过程已成功完成。

SQL>
SQL> set autotrace traceonly
SQL> set time on
22:19:10 SQL> select t.id,tt.name from t , tt where t.id=tt.id;

已选择10000行。


执行计划
----------------------------------------------------------
Plan hash value: 2424494595

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 19M| 755 (1)| 00:00:10 |
|* 1 | HASH JOIN | | 10000 | 19M| 755 (1)| 00:00:10 |
| 2 | TABLE ACCESS FULL| T | 10000 | 40000 | 8 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TT | 10000 | 19M| 746 (1)| 00:00:09 |
---------------------------------------------------------------------------

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

1 - access("T"."ID"="TT"."ID")


统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4069 consistent gets
0 physical reads
0 redo size
176872 bytes sent via SQL*Net to client
7711 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed

22:19:17 SQL> select /*+ use_hash(t tt) */ t.id,tt.name from t , tt where t.id=t
t.id;

已选择10000行。


执行计划
----------------------------------------------------------
Plan hash value: 2424494595

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 19M| 755 (1)| 00:00:10 |
|* 1 | HASH JOIN | | 10000 | 19M| 755 (1)| 00:00:10 |
| 2 | TABLE ACCESS FULL| T | 10000 | 40000 | 8 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TT | 10000 | 19M| 746 (1)| 00:00:09 |
---------------------------------------------------------------------------

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

1 - access("T"."ID"="TT"."ID")


统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
4069 consistent gets
0 physical reads
0 redo size
176872 bytes sent via SQL*Net to client
7711 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed

22:20:07 SQL>
22:20:59 SQL> select /*+ use_hash(t tt) leading(tt) */ t.id,tt.name from t , tt
where t.id=tt.id;

已选择10000行。


执行计划
----------------------------------------------------------
Plan hash value: 3792434616

--------------------------------------------------------------------------------

---

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time
|

--------------------------------------------------------------------------------

---

| 0 | SELECT STATEMENT | | 10000 | 19M| | 1717 (1)| 00:00:2

1 |

|* 1 | HASH JOIN | | 10000 | 19M| 19M| 1717 (1)| 00:00:2

1 |

| 2 | TABLE ACCESS FULL| TT | 10000 | 19M| | 746 (1)| 00:00:0

9 |

| 3 | TABLE ACCESS FULL| T | 10000 | 40000 | | 8 (0)| 00:00:0

1 |

--------------------------------------------------------------------------------

---


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

1 - access("T"."ID"="TT"."ID")


统计信息
----------------------------------------------------------
24 recursive calls
0 db block gets
3527 consistent gets
2821 physical reads
0 redo size
176872 bytes sent via SQL*Net to client
7711 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed

22:21:17 SQL>
22:23:40 SQL> select /*+ use_nl(t tt) */ t.id,tt.name from t , tt where t.id=tt.
id;

已选择10000行。


执行计划
----------------------------------------------------------
Plan hash value: 2329402909

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 19M| 63237 (2)| 00:12:39 |
| 1 | NESTED LOOPS | | 10000 | 19M| 63237 (2)| 00:12:39 |
| 2 | TABLE ACCESS FULL| TT | 10000 | 19M| 746 (1)| 00:00:09 |
|* 3 | TABLE ACCESS FULL| T | 1 | 4 | 6 (0)| 00:00:01 |
---------------------------------------------------------------------------

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

3 - filter("T"."ID"="TT"."ID")


统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
314705 consistent gets
0 physical reads
0 redo size
176872 bytes sent via SQL*Net to client
7711 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed

22:24:09 SQL>
22:24:09 SQL> select /*+ use_nl(t tt) leading(t) */ t.id,tt.name from t , tt whe
re t.id=tt.id;

已选择10000行。


执行计划
----------------------------------------------------------
Plan hash value: 3115954764

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 19M| 7443K (1)| 24:48:40 |
| 1 | NESTED LOOPS | | 10000 | 19M| 7443K (1)| 24:48:40 |
| 2 | TABLE ACCESS FULL| T | 10000 | 40000 | 8 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| TT | 1 | 2004 | 744 (1)| 00:00:09 |
---------------------------------------------------------------------------

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

3 - filter("T"."ID"="TT"."ID")


统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
33711291 consistent gets
0 physical reads
0 redo size
176872 bytes sent via SQL*Net to client
7711 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed

22:25:48 SQL>
22:38:00 SQL> set timing on
22:38:12 SQL> select /*+ use_merge(t tt) */ t.id,tt.name from t , tt where t.id=
tt.id;

已选择10000行。

已用时间: 00: 00: 09.10

执行计划
----------------------------------------------------------
Plan hash value: 10480094

--------------------------------------------------------------------------------

----

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time
|

--------------------------------------------------------------------------------

----

| 0 | SELECT STATEMENT | | 10000 | 19M| | 4975 (1)| 00:01:

00 |

| 1 | MERGE JOIN | | 10000 | 19M| | 4975 (1)| 00:01:

00 |

| 2 | SORT JOIN | | 10000 | 40000 | 248K| 41 (5)| 00:00:

01 |

| 3 | TABLE ACCESS FULL| T | 10000 | 40000 | | 8 (0)| 00:00:

01 |

|* 4 | SORT JOIN | | 10000 | 19M| 39M| 4934 (1)| 00:01:

00 |

| 5 | TABLE ACCESS FULL| TT | 10000 | 19M| | 746 (1)| 00:00:

09 |

--------------------------------------------------------------------------------

----


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

4 - access("T"."ID"="TT"."ID")
filter("T"."ID"="TT"."ID")


统计信息
----------------------------------------------------------
21 recursive calls
15 db block gets
3402 consistent gets
7797 physical reads
0 redo size
176872 bytes sent via SQL*Net to client
7711 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
1 sorts (memory)
1 sorts (disk)
10000 rows processed

22:38:23 SQL>
SQL> connect/ as sysdba
已连接。
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 62915940 bytes
Database Buffers 100663296 bytes
Redo Buffers 2945024 bytes
数据库装载完毕。
数据库已经打开。
SQL> exit
从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 断开

C:>sqlplus test/test

SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 10月 13 22:43:25 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.


连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> set autotrace traceonly
SQL> set timing on
SQL> select /*+ use_merge(t tt) leading(tt) */ t.id,tt.name from t , tt where t.
id=tt.id;

已选择10000行。

已用时间: 00: 00: 06.98

执行计划
----------------------------------------------------------
Plan hash value: 4287342720

--------------------------------------------------------------------------------

----

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time
|

--------------------------------------------------------------------------------

----

| 0 | SELECT STATEMENT | | 10000 | 19M| | 4975 (1)| 00:01:

00 |

| 1 | MERGE JOIN | | 10000 | 19M| | 4975 (1)| 00:01:

00 |

| 2 | SORT JOIN | | 10000 | 19M| 39M| 4934 (1)| 00:01:

00 |

| 3 | TABLE ACCESS FULL| TT | 10000 | 19M| | 746 (1)| 00:00:

09 |

|* 4 | SORT JOIN | | 10000 | 40000 | 248K| 41 (5)| 00:00:

01 |

| 5 | TABLE ACCESS FULL| T | 10000 | 40000 | | 8 (0)| 00:00:

01 |

--------------------------------------------------------------------------------

----


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

4 - access("T"."ID"="TT"."ID")
filter("T"."ID"="TT"."ID")


统计信息
----------------------------------------------------------
470 recursive calls
7 db block gets
3457 consistent gets
5875 physical reads
0 redo size
176872 bytes sent via SQL*Net to client
7711 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
9 sorts (memory)
1 sorts (disk)
10000 rows processed

SQL>
SQL> select /*+ use_merge(t tt) leading(tt) */ t.id,tt.name from t , tt where t.
id=tt.id;

已选择10000行。

已用时间: 00: 00: 05.35

执行计划
----------------------------------------------------------
Plan hash value: 4287342720

--------------------------------------------------------------------------------

----

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time
|

--------------------------------------------------------------------------------

----

| 0 | SELECT STATEMENT | | 10000 | 19M| | 4975 (1)| 00:01:

00 |

| 1 | MERGE JOIN | | 10000 | 19M| | 4975 (1)| 00:01:

00 |

| 2 | SORT JOIN | | 10000 | 19M| 39M| 4934 (1)| 00:01:

00 |

| 3 | TABLE ACCESS FULL| TT | 10000 | 19M| | 746 (1)| 00:00:

09 |

|* 4 | SORT JOIN | | 10000 | 40000 | 248K| 41 (5)| 00:00:

01 |

| 5 | TABLE ACCESS FULL| T | 10000 | 40000 | | 8 (0)| 00:00:

01 |

--------------------------------------------------------------------------------

----


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

4 - access("T"."ID"="TT"."ID")
filter("T"."ID"="TT"."ID")


统计信息
----------------------------------------------------------
20 recursive calls
7 db block gets
3402 consistent gets
2480 physical reads
0 redo size
176872 bytes sent via SQL*Net to client
7711 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
1 sorts (memory)
1 sorts (disk)
10000 rows processed

SQL>

下一篇: x$表的解释!
请登录后发表评论 登录
全部评论

注册时间:2007-12-07

  • 博文量
    714
  • 访问量
    5048262