奇怪的执行计划

显示全部楼层 · 2012-11-26 16:22:47

首先可以确定的是：
1、CUX_WIP_BOM_EXPLOSION_HIS HIS是新建的表，数据为空。

第一次执行程序的时候，查看Trace文件如下：
QL ID: gpw5uu02nx6pj Plan Hash: 3409220278
DELETE FROM CUX_WIP_BOM_EXPLOSION_HIS HIS
WHERE
HIS.ORGANIZATION_ID = :B1

call
count cpuelapsed disk query currentrows
------------------------------- -------------------- --------------------
Parse
1
0.000.00
0
0
0
0
Execute 1 24.10 1158.00 2363972706944 2
0
Fetch
0 0.00 0.00
0
0
0
0
------- ------ -------- - ------------------- -------------------- ----------
total 2
24.101158.00 2363972706944 2
0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 47 (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max)Row Source Operation
---------- ---------- -------------------------------------------------------------
0
0
0DELETECUX_WIP_BOM_EXPLOSION_HIS (cr=2706944 pr=236397 pw=0 time=0 us)
0
0
0 TABLE ACCESS FULL CUX_WIP_BOM_EXPLOSION_HIS (cr=2706944 pr=236397 pw=0 time=0 us cost=2 size=204 card=1)

Elapsed times include waiting on following events:
Event waited on
Times Max. WaitTotal Waited
---------------------------------------- Waited----------------------
Disk file operations I/O
300.01
0.03
db file sequential read
1851010.32 1096.24
db file parallel read
50.04
0.10
db file scattered read
8910.16 10.82
log file switch completion
130.11
0.62
buffer busy waits
210.00
0.00
latch: object queue header operation
30.00
0.00
latch free
10.00
0.00
latch: cache buffers chains
30.00
0.00
latch: undo global data
10.00
0.00
********************************************************************************
不知道为什么删除花费了这么长的时间？求各位大神解答，多谢！！

千问 · 2012-11-26 16:22:47

确定是新建的表？
查下segment_size多大。

千问 · 2012-11-26 16:22:47

看下这个表所占的磁盘空间。

千问 · 2012-11-26 16:22:47

IO问题？
新表的话，应该碎片问题不大

千问 · 2012-11-26 16:22:47

不太相信这个结果，很诡异...

千问 · 2012-11-26 16:22:47

应该是高水位的问题