Home » SQL & PL/SQL » SQL & PL/SQL » Transactions timing out without errors!
Transactions timing out without errors! [message #278091] Thu, 01 November 2007 15:19 Go to next message
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 #278094 is a reply to message #278091] Thu, 01 November 2007 15:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
There is no transaction time-out (unless you use distributed transaction).
There may be a limit if you use profile but the session is not inactive it is killed.

If you want more you have to post more details like tool you use, client, versions, code and so on.

Regards
Michel
Re: Transactions timing out without errors! [message #278096 is a reply to message #278091] Thu, 01 November 2007 15:46 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
AFAIK, Oracle does not quietly "timeout".

Is there anything "interesting" in the alert log file for around the same time as the "timeout"?

ALTER SESSION SET SQL_TRACE=TRUE
before starting the problematic SQL.

What do repeated invocations of
SELECT * FROM V$SESS_IO WHERE SID = <SESSION_ID_OF_PROBLEM_SQL>
show after the timeout occors?
Re: Transactions timing out without errors! [message #278102 is a reply to message #278091] Thu, 01 November 2007 16:26 Go to previous message
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

Previous Topic: Comparing two strings
Next Topic: Dynamic strings
Goto Forum:
  


Current Time: Thu Feb 13 10:17:34 CST 2025