我有一段语句执行需要3分多钟，而在另一台服务器上执行则只需要10几秒。
10秒能执行完的服务器临时表空间为4G固定大小， 3分钟的临时表空间为512m动态大小，不知道是不是跟这个有关系
trace结果如下
--------------------------------------------
--------------
| Id | Operation | Name | Rows | Bytes | Cost (%CP
U)| Time |
--------------------------------------------
--------------
| 0 | SELECT STATEMENT | | 16 | 2480 | 434 (
5)| 00:00:06 |
| 1 | SORT ORDER BY | | 16 | 2480 | 434 (
5)| 00:00:06 |
|* 2 | HASH JOIN | | 16 | 2480 | 433 (
4)| 00:00:06 |
| 3 | NESTED LOOPS | | 13 | 1599 | 390 (
5)| 00:00:05 |
|* 4 | TABLE ACCESS FULL | PT_PART | 4151 | 397K| 386 (
4)| 00:00:05 |
|* 5 | TABLE ACCESS BY INDEX ROWID| PT_PART | 1 | 25 | 1 (
0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | SYS_C005515 | 1 | | 0 (
0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | PT_PRICE | 8087 | 252K| 43 (
3)| 00:00:01 |
--------------------------------------------
--------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"."STYLE_NO"="B"."PART_NO")
4 - filter("B"."BRAND"='B' AND "B"."YEAR"='H' AND "B"."COMP_CD"='ACREX')
5 - filter("B"."BRAND"='B' AND "B"."YEAR"='H')
6 - access("B"."COMP_CD"='ACREX' AND "PART_NO"="B"."PART_NO")
filter("ETS_PTZ_GETAVAILSTOCKSET"("COMP_CD",'H1001',"PART_NO",'N')>0)
7 - filter("B"."END_DT"='9999-99-99')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------
8456 recursive calls
0 db block gets
23930063 consistent gets
0 physical reads
7264 redo size
65874 bytes sent via SQL*Net to client
1482 bytes received via SQL*Net from client
51 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
736 rows processed
------解决方案--------------------
有一定关系，但不是决定性的，要看两个服务器上表的结构 是不是一样的 ，时间长的那个服务器上，表有没有建索引？分区啊 什么的 ，都要看一下的
------解决方案--------------------
导过来后执行下全schema的统计分析
- SQL code
begindbms_stats.gather_schema_stats('YOUR_SCHEMA_NAME');end;