Home » SQL & PL/SQL » SQL & PL/SQL » merge issue (database,,red hat 64bit)
merge issue [message #381101] Thu, 15 January 2009 04:23 Go to next message
Messages: 6
Registered: June 2005
Junior Member
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:

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

...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.

[Updated on: Thu, 15 January 2009 04:25]

Report message to a moderator

Re: merge issue [message #381103 is a reply to message #381101] Thu, 15 January 2009 04:31 Go to previous messageGo to next message
Michel Cadot
Messages: 65079
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Does it happen each and every time or just once?

Re: merge issue [message #381124 is a reply to message #381103] Thu, 15 January 2009 06:01 Go to previous message
Messages: 6
Registered: June 2005
Junior Member
Hi Michel
This happens every time i run that package if merge is in it, no metter if merge in it is executed with 'execute immediate' , as standalone sql or by calling some other package which consists only of procedure with merge statement.
every time sessions stays active, although, in about few minutes i guess, table that needs to be merged is merged.
when i kill that session, i can't do a truncate(which i do alwys because repetition of that testing process requires that table to be empty) on that merged table becouse i get :
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

so i have to kill that os process of that session to do truncate.
i just don't get it why this thing hangs when executed through package and finishes normally as sql.

[Updated on: Thu, 15 January 2009 06:19]

Report message to a moderator

Previous Topic: How to send email from PL/SQL
Next Topic: How can I apply outer join in this query
Goto Forum:

Current Time: Thu Jul 20 12:11:57 CDT 2017

Total time taken to generate the page: 0.18381 seconds