我有一段语句执行需要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
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
10秒能执行完的服务器上的执行计划呢?
还有尝试下先在表格上执行数据分析
dbms_stats.gather_table_stats('SCHEMA_NAME', 'TABLE_NAME');
在进行查询
应该是表信息没有搜集,重新 analyze table 后提示消失,速度恢复了。
另,这个是10几秒那个库里导出来再倒入到新库的,表的统计信息要重新分析吗?
其他查询好像都正常。
dbms_stats.gather_schema_stats('YOUR_SCHEMA_NAME');
end;