Milen,

MERGE T_F_MESSUNG_DLZ_2 (cr=27552 pr=7876 pw=0 time=52349971 us)
1378  VIEW (cr=4752 pr=602 pw=0 time=11221649 us)

So for updating/inserting the rows, you performed

( 7876- 602) / 7876 = 92% of the physical reads.

From

db file sequential read               7861     0.28      50.54

92% of 50.54 is 46.68 seconds, which is very close to the 41.128322
seconds you waited (you cannot expect a perfect match of course
when using averages).

Consider that when updating/inserting, you have to update the indexes also;
that explains why you performed (27552-4752) consistent gets
and ( 7876- 602) physical reads for a "mere" 1378 rows.

(you have also to update the rollback segments blocks, that
aren't necessarily cached - but "usually" the indexes contribute more).

hth
Alberto

On 7/23/07, Milen Kulev <makulev@(protected):
> Hello Albeto,
> Thank you for your prompt reply .
> OK, after selecting/filterung which rows should be inserted und which rows should be updated (VIEW step),
> The rows should be physically written to the table. This operation takes
> 52349971- 11221649 microseconds = 41128322 microseconds
> VIEW step filtered 1378 rows. Time to update/insert a row would be:
>
> 41128322 microseconds/1378 rows = 29846 microseconds ~ 29 ms, just for writing one row ?!
> At the time of issuing the statement there were no locks (or other serialization mechanism) on the table
> T_F_MESSUNG_DLZ_2.
> GlancePlus showed me at that time maximum ~ 400 I/Os per second (Max is ~ 3000 I/Os per second). So, there was no I/O
> subsystem botthelneck
> Furthermore, the sum of sizes of all T_F_MESSUNG_DLZ_2 columns is ~ 1k (9xx bytes).
> How is it possible to take 29 ms to update/insert 1k ?
(snip)

--
Alberto Dell'Era
"the more you know, the faster you go"
--
http://www.freelists.org/webpage/oracle-l