I am having dificulties with making merge to finish succesfully , when executing package in which merge is implemented
as substitution for update-insert that is currently in that package.
When package executed with update-insert part , finishes in about 40sec and updates about 60000 rows in one partitioned table.
The thing is that when i run merge outside of that package,as alone sql statement, it finish in about 15 secs(succesfully updates the same number of rows as package with update-insert),
but when executing package with that merge statement part instead of insert-update, session which executed that package hungs(last night i started that package and i encourted session active this morning). Also strange thing for me is that after some time (few minutes i guess) , table which needs to be updated with merge , is merged! .. but anyway session is still active , hunged....
I tryed to implement that merge statement in that package with execute immediate and also by calling package where inside it is procedure with merge statement, but
in all cases session hungs.
Wait events for that session while its hunged are mainly checkpoint incomplete , db file sequential read, etc.... by watching through toad session browser i see that they change every few seconds....
I had trace enabled for that session for about 30min, it generated .trc file of aprox. 700MB. then i disabled trace , although session stayed active , and,
interesting enough, table was merged with the expected data ....
I would like to share just this piece of maybe relevant info from the bottom of that tkprofed trace file:
...so , no dubt, log file switch (checkpoint incomplete) points out , but why then this merge finishes in about 15sec when executed as sql only and also no hanging occurs?
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 506 0.40 3.04
log file switch (checkpoint incomplete) 6596131 0.72 500.62
free buffer waits 536 0.01 5.87
log file switch completion 353 0.30 5.62
latch: cache buffers lru chain 1 0.00 0.00
buffer busy waits 29 0.00 0.00
log buffer space 135 0.46 6.31
buffer exterminate 12 1.00 10.44
db file scattered read 6 0.00 0.00
latch: messages 10 0.00 0.00
latch: redo writing 28 0.00 0.00
latch: cache buffers chains 12 0.00 0.00
latch free 12 0.00 0.00
latch: object queue header operation 2 0.00 0.00
Any comments are appriciated.
Click Here to Expand Forum to Full Width