Re: Subject: Batch load freezes every couple of minutes

  • From: "Ranko Mosic" <mosicr@xxxxxxxxxx>
  • To: "sol beach" <sol.beach@xxxxxxxxx>
  • Date: Mon, 18 Apr 2005 21:42:41 -0400

Isn't maxtrans of 255 ( default )sufficient ?

----- Original Message -----
From: "sol beach" <sol.beach@xxxxxxxxx>
To: <mosicr@xxxxxxxxxx>
Cc: <oracle-l@xxxxxxxxxxxxx>; <ryan_gaffuri@xxxxxxxxxxx>
Sent: Sunday, April 17, 2005 9:18 PM
Subject: Re: Subject: Batch load freezes every couple of minutes

>Instance runs 20 parallel load processes

You might be encountering enqueue waits.
Typically INITRANS=1 for tables & 2 for indexes (YMMV).
Unless & until the session holding the Interested Transaction Lock
releases it, other sessions will quietly get into line waiting for it
to be released. This is NOT a deadlock & won't be reported as one.


On 4/17/05, ryan_gaffuri@xxxxxxxxxxx <ryan_gaffuri@xxxxxxxxxxx> wrote:
> Please provide your definition of 'hang'? To see if your processes are
still running look in v$sess_io, if the values there are incrementing, then
processing is still going on.
> you need to find out what the processes are doing at the point that they
are freezing? Best way is to get a 10046 trace of one of the processes
dumped to a file. Another alternative is to use statspack of before the
'hang' and then another snapshot afterward and look at what sql is
> you mentioned that the times that you have problems are when you have lock
waits. you could have 1 process doing something for a long time and aquiring
a lock on a row. the rest of the processes require that row and have to wait
for a commit or rollback.
> the v$ tables(other than a quick look at v$sess_io) probably won't be
helpful here. you need to know exactly what is going on when this occurs.
> --


Other related posts: