Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: MERGE statement - where time goes ?

Re: MERGE statement - where time goes ?

From: Alberto Dell'Era <alberto.dellera_at_gmail.com>
Date: Mon, 23 Jul 2007 21:23:44 +0200
Message-ID: <4ef2fbf50707231223n361f00ddp347efc9fe687a17d@mail.gmail.com>


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_at_gmx.net> 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"
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jul 23 2007 - 14:23:44 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US