Transactions timing out without errors! [message #278091] |
Thu, 01 November 2007 15:19  |
clos91
Messages: 1 Registered: November 2007 Location: Sacramento
|
Junior Member |
|
|
Transactions appearing to time out, but i don't get any errors. I have long transactions basically I am moving data from one table y to table x. something like this
insert into table x (x1,x2,x3) select y1, y2, y3 from y
I'm looking to move about 3.5 million records. I've tried different things like using cursors and committing after few 100 records but can't get it to work. The transaction at some point after 1 1/2 into execution time it just times out and session is set to INACTIVE. I don't get an error or anything it almost seems like is still executing but it isn't. I select count on the new table x and still 0 and session is inactive.
I have two envirnoments DEV and QA on DEV the same insert statement completes in about 1 hr. but on QA it never does. The two enviroments seem to be indetical (SGA Parameters). In fact QA is a newer and faster machine. Any ideas on what to look into?
|
|
|
|
|
Re: Transactions timing out without errors! [message #278102 is a reply to message #278091] |
Thu, 01 November 2007 16:26  |
 |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
If you want to see if your job is doing any work, you can look at the v$transaction table and monitor the ubarec and used_urec columns (there are others too you could also use). For example:
SELECT UBAREC, USED_UREC FROM V$TRANSACTION;
SO... lets say you start a long running transaction in sqlplus session A. You could open up sqlplus session B and run the select above several times. if the numbers are increasing then you are doing real work. If the numbers are decreasing then your transaction is rolling back. If the numbers are not doing diddly then you are not doing anything in the undo segments so you are not changing any data.
You could also query v$sess_io for the session that is running the long running job. The buffer_gets should be increasing if your job is doing any kind of work at all. Buffer_gets will go up if you are doing real work and they will also go up if you are rolling back. They won't do crap if you are hung.
You could also check dba_blockers and dba_waiters to see if you are queueing on some lock.
Let us know what you find out.
Good luck, Kevin
[Updated on: Thu, 01 November 2007 19:47] Report message to a moderator
|
|
|