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?
Any comments are appriciated.