日期:2014-05-16  浏览次数:20786 次

语句效率问题
我有一段语句执行需要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
begin
dbms_stats.gather_schema_stats('YOUR_SCHEMA_NAME');
end;