Hi
The tkprof re****t shows that the following insert operation was waited
substantially for db file sequential read. from the raw trace file it
shows that the blocks it waited for are related to couple of Index
segments one is a unique key.
The table is partitioned on oprn_dt column and the indexes are all
local prefixed indexes.
I am not very sure how the insert operation is waiting for index
reads. There are no Referential integrity constraints on this table.
The procedure is doing a bulk fetch from a Global Temp Table into this
target table.
INSERT /*+ append */ INTO OPERATION_DTLS
VALUES
(:B1 ,:B2 ,:B3 ,:B4 ,:B5 ,:B6 ,:B7 ,:B8 ,:B9 ,:B10 ,:B11 ,:B12 ,:B13 ,:B14
,
:B15 ,:B16 ,:B17 ,:B18 ,:B19 ,:B20 ,:B21 ,:B22 ,:B23 ,:B24 ,:B25 ,:B26 ,
:B27 ,:B28 ,:B29 ,:B30 ,:B31 ,:B32 ,:B33 ,:B34 ,:B35 ,:B36 ,:B37 ,:B38 ,
:B39 ,:B40 ,:B41 ,:B42 ,:B43 ,:B44 ,:B45 ,:B46 ,:B47 )
call count cpu elapsed disk query
current rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 0 0.00 0.00 0 0
0 0
Execute 19 39.90 184.88 11833 36926
1102079 92524
Fetch 0 0.00 0.00 0 0
0 0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 19 39.90 184.88 11833 36926
1102079 92524
Misses in library cache during parse: 0
Misses in library cache during execute: 2
Optimizer mode: ALL_ROWS
Parsing user id: 228 (recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total
Waited
---------------------------------------- Waited ----------
------------
library cache lock 1
1.86 1.86
db file sequential read 11835 0.53
142.44
log file switch completion 7
0.13 0.35
control file sequential read 13
0.00 0.02
KSV master wait 7
0.06 0.12
Data file init write 188
0.03 0.44
db file single write 1
0.00 0.00
control file parallel write 3
0.00 0.00
rdbms ipc reply 1
0.02 0.02
latch: cache buffers chains 1
0.00 0.00
Thanks for your insight,
Sam
|
