Re: MERGE statement - where time goes ?
- From: "Alberto Dell'Era" <alberto.dellera@xxxxxxxxx>
- To: "Milen Kulev" <makulev@xxxxxxx>
- Date: Mon, 23 Jul 2007 21:23:44 +0200
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@xxxxxxx> wrote:
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"
--
//www.freelists.org/webpage/oracle-l
Other related posts:
