Re: Log Writer Freeze up - Deadlock ???

dyoung_at_cha.ab.ca (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
>

Hi there,

I think the problem is your redo logs are generated very fast, before archiver archives the redo log files. Till archiver has finished the job, you will experience a "System Hang" situation. You can try the following.

1. Increase the size of redo log files.
2. Create more redo log groups. If you have 3 groups, try making it 6. Since you are performing batch updates / inserts you can try a max of 10 redo groups.

By doing this, your archiver will have all the time in the world to complete its task, thus it will avoid system hang.

I hope it will help you. Please let me know your feedback on the above.

Regards

N.Prabhakar Received on Tue Dec 03 1996 - 00:00:00 CST