Re: Log Writer Freezing up - Deadlock ???

From: Ven Jayram <ESCI_at_worldnet.att.net>
Date: 1996/12/01
Message-ID: <32A23603.7D12@worldnet.att.net>#1/1

Dave Young wrote:
>
> We are running Oracle 7.3.2.2 on a HP9000.(heard there is mandatory 7.3.2.3
> release soon to be available for HP) When I do a huge load (1.7 million
> transactions - PL/SQL block inserts into table one at a time with a commit.
> Trigger on insert of said table calls multiple procedures/functions and does
> DML'S - inserts/updates/deletes).
>
> I can be the only process on the box, yet it always "hangs" the database
> (evetually , never at the same place 100,000 records, 600,000 records and today
> 1.3 million records). We have tried every Oracle Tech suggestion but to date
> nothing has work. To recover we have to shutdown abort and re-boot the machine
> as the process ora_lgwr_CCP will not go away , even with a kill -9. Currently
> running in NOARCHIVELOG mode - until all backload data is in)
>
> I think it is a deadlock as any select on v$lock, or dba_transaction will hang
> yet you can select from v$session, user tables etc. Any suggestions would be
> apprecaited.
>
> thx..........dy

]

Dave:
Here are a few suggestions:
1. If you can , do this in single user mode so there is nobody else except the user doing the load.

2. Bump up dml_locks=2500
3. set cleanup_rollback_entries=200
4. If possible, (SMP box) use the parallel query options. split the

insert based on ranges and run multiple loads( basically, data partitions).

Let me know how it goes.
Good Luck

Ven Jayram
Principal Consultant,
Enterprise Systems Consulting, Inc
Denver, CO

std disclaimer applies Received on Sun Dec 01 1996 - 00:00:00 CST