Re: Subject: Batch load freezes every couple of minutes
- From: Mladen Gogala <gogala@xxxxxxxxxxxxx>
- To: mosicr@xxxxxxxxxx
- Date: Wed, 20 Apr 2005 01:28:57 +0000
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
