Re: Subject: Batch load freezes every couple of minutes

On 04/19/2005 06:49:59 PM, Ranko Mosic wrote:
> It's not full, only 40% filled on average.

What you are looking for is INITRANS, not MAXTRANS. INITRANS is the number = of=20
pre-allocated ITL entries. In versions prior to 9i changing INITRANS was im= possible
without droping and re-creating the table in question. As this was usually = problem
only on heavily used tables, dropping them was out of the question, so inco= rrect INITRANS
was carved in stone. In oracle9i and later there is "ALTER TABLE MOVE" whic= h can be used
for rebuilding table attributes, INITRANS included. Before you start rebuilding tables, make sure that locks are what you are w= aiting for.
In other words, make sure that load isn't causing frequent log switches and=  checkpoint
which can seriously impact load performance. I had a problem with the datab= ase that appeared
to be hanging during large loads, and "hanging" was occuring in regular int= ervals, which
immediately made me suspect checkpoints and disregard suspicions about lock= ing. As it turns
out, I was right. The database had a standby, and, of course, there was a l= og_archive_dest_2,
defined as MANDATORY. I immediately suspected network, but the T3 line was = running at <0.1% of
capacity. It turned out that an ethernet port on the switch was blinking re= d....the machine
had a problem reaching the router. Of course, while an expensive 8 CPU HP 9= 000 class N node
was essentially using a shoelace to reach CISCO 7400 router which was idly = waiting for something
to do, a cheap T3 line was not carying any traffic and the database appeare= d to be hanging, as the
archiver was essentially stuck. Changing the state of the destination to de= ferred, everything=20
was back to normal. To be able to fix the problem, you have to find out wha= t are you waiting=20
for. If it's a lock, V$SESSION_WAIT will give you the block and and ID1 and=  ID2 as P2 and P3=20
arguments. Here is an example:

select event,p1text,p1,p2text,p2,p3text,p3=20 from v$session_wait where sid=3D31

enq: TX - row lock contention  	name|mode  	1415053318  	usn<<16 | slot  	2=
62168  sequence  	435

select request,id1,id2 from V$LOCK where=20 request>0 and sid=3D31
6 262168 435

You will see that P2 and P3 in V$SESSION_WAIT correspond to ID1 and ID2

This will help you to locate the object:

select owner,object_name,object_type from dba_objects o, v$session s where
o.object_id=3Ds.row_wait_obj# and s.sid=3D31

SCOTT EMP TABLE (What a surprise!)
Of course, the following select will produce the statement that you will=20 want to execute next:

select 'alter system disconnect '''||sid||','||serial#||

       ''' immediate;'=20
from v$session
where sid in=20

    (select blocking_session from v$session where sid=3D31)

If you are waiting for ITL ROW_WAIT_OBJ# will be -1.=20

--=20
Mladen Gogala
Oracle DBA

--
http://www.freelists.org/webpage/oracle-l