RE: Deadlock in trace!! (fwd)

Hey Jared,

One of our problems with the deadlock dump in the trace files is that it only dumps one of the processes that was involved in the deadlock. Makes troubleshooting very difficult.

If Jakh is unable to modify the app to relieve the deadlocks, perhaps increasing the efficency of the DB, thereby reducing the amount of time that resources are locked by any given process, might help? I'm also assuming that moving off of NT isn't feasible... ;)

A few things I saw in the init.ora:

1. SET LOG_CHECKPOINT_INTERVAL TO A NON-ZERO NUMBER!!!! (Metalink 30754.1) This is causing a checkpoint for every redo buffer written! Personally, I don't use it. I set it to the size of the redo log / block size + some huge number. I let the log switch do my checkpointing. Also, I've set LOG_CHECKPOINT_TIMEOUT to 1800 to ensure a checkpoint every 30 minutes.
2. Get rid of ONE of the control files on the E: drive. This is just adding more drive contention on checkpoints, which are probably killing the system right now.
3. Try adding a high value on ENQUEUE_RESOURCES. I think the default is something terribly low, like 50. Try 2000. Note 30722.1 on MetaLink says DML_LOCKS + 700 + 20. I've found that to be a little low for me.

Try those changes, bounce, and see what happens. Of course, since this advise is free, and I'm stressed out from moving and preparing to get married, I can't be held responsible for bogus advice.

Good luck! Let me know how it goes! :)

> -----Original Message-----
> From: jared still [mailto:jkstill_at_teleport.com]
> Sent: Sunday, August 27, 2000 21:48
> To: Multiple recipients of list ORACLE-L
> Subject: Deadlock in trace!! (fwd)
>
>
>
> ---------- Forwarded message ----------
> Date: Sat, 26 Aug 2000 10:53:29 +0400
> To: jared still <jkstill_at_teleport.com>
>
> Hi Jared,
>
> Today I have another problem, alter files are showoing
> deadlock detected as under:
>
> ------------------------------------------------------------------
> Errors in file E:\ORANT\RDBMS73\trace\ORA00305.TRC:
>
> Sat Aug 26 09:19:08 2000
> Errors in file E:\ORANT\RDBMS73\trace\ORA00072.TRC:
>
> Sat Aug 26 09:19:15 2000
> Errors in file E:\ORANT\RDBMS73\trace\ORA00301.TRC:
>
> Sat Aug 26 09:21:10 2000
> Errors in file E:\ORANT\RDBMS73\trace\ORA00346.TRC:
> ORA-00060: deadlock detected while waiting for resource
> ORA-06512: at "AUS.SOF_PACK", line 48
> ORA-06512: at "AUS.SOF_PACK", line 582
> ORA-06512: at line 1
>
> Sat Aug 26 09:21:10 2000
> Errors in file E:\ORANT\RDBMS73\trace\ORA00307.TRC:
> ----------------------------------------------------------------------
>
> The log_switch_interwal is set to 0 (zero). Does this
> parameter has to be changes to force the log
> switching.
>
> Plssssss help, users are really crying due to the dead-lock.
> I m attaching one of the trace file. I m
> very thankfull for ur help.
>
> bye,
>
> Jakh
>
>
> jared still wrote:
>
> > Well, if full table scans are a problem, I would
> > suggest creating indexes.
> >
> > You don't want to do that for some reason, so I am
> > guessing that this is a canned application.
> >
> > If your hit ratio is between 73 and 81 % with full
> > table scans, I'm guessing that your tables are not
> > very large.
> >
> > Do you have hash joins enabled? If you don't, I would
> > suggest trying that. This requires that you analyze
> > the tables of course.
> >
> > Capture some of the slow queries vrom v$sqlarea and
> > v$sqltext and run explain plan on them. If they
> > are doing full table scans and nested loop joins, you
> > *definitely* need to enable hash joins.
> >
> > HTH
> >
> > Jared
> >
> > On Fri, 25 Aug 2000, Javed Akhtar wrote:
> >
> > > Hi Jared,
> > >
> > > We are running Oracle 7.3.4 on NT Box. Users are observing poor
> > > performance, Upon tracing the SQLs I found many full
> > > table scans and the ratio comes around 0.73 to 0.81. At
> the same time
> > > the LOG switches are switching as under :
> > >
> > > Log. Switch Log. Name Data&Time
> > >
> > > 3635 log4.log
> 24.08.2000 5:30
> > > (it's a database startup every day after backup)
> > > 3636 log5.log
> 24.08.2000 9:30
> > > 3637 log3.log
> 24.08.2000 15:28
> > >
> > > No log switch happens today 25.08.2000. The
> LOG_CHECKPOINT_INTERNAL is
> > > set to 0 (zero). The other init<SID>.ora
> > > settings are as under:
> > >
> > > ----------------------------- Init<sid>.ora---------------
> > > db_files = 30
> > > control_files =
> > >
> (E:\ORANT\DATABASE\ctl1orcl.ora,E:\ORANT\DATABASE\ctl2orcl.ora
> ,C:\WINNT\ctl3orcl.ora)
> > >
> > > ROLLBACK_SEGMENTS =
> > >
> (RB001,RB002,RB003,RB004,RB005,RB006,RB007,RB008,RB009,RB010,R
> B011,RB012,RB013,RB014,RB015,RB016)
> > >
> > > compatible = 7.3.0.0.0
> > >
> > > db_file_multiblock_read_count = 32
> #
> > > LARGE
> > >
> > > db_block_buffers = 15000
> > >
> > > shared_pool_size = 40000000
> > >
> > > log_checkpoint_interval = 0
> > >
> > > processes = 250
> #
> > > LARGE
> > >
> > > dml_locks = 1000
> > >
> > > log_buffer = 655360
> #
> > > LARGE
> > >
> > > sequence_cache_entries = 30 # INITIAL
> > >
> > > sequence_cache_hash_buckets = 23 # INITIAL
> > >
> > > max_dump_file_size = 10240 # limit trace file size
> to 5 Meg each
> > >
> > > max_enabled_roles = 90
> > >
> > >
> > > background_dump_dest=%RDBMS73%\trace
> > > user_dump_dest=%RDBMS73%\trace
> > >
> > > db_block_size = 2048
> > >
> > > checkpoint_process=true
> > >
> > > sort_area_size=1000000
> > > sort_area_retained_size=1000000
> > >
> > > remote_login_passwordfile = shared
> > >
> > > text_enable = true
> > > open_cursors = 300
> > >
> > > log_simultaneous_copies = 4
> > > log_small_entry_max_size = 40
> > >
> > > nls_date_format = dd-mm-yyyy
> > >
> > > LICENSE_MAX_SESSIONS=110
> > > LICENSE_SESSIONS_WARNING=100
> > > ----------------------------------------------------- End of
> > > ini<sid>.ora file -------------
> > >
> > > Plsssssssss sugguest what to do in order to avoide the
> dead slow. Any
> > > tuning issue which can help apart from creating indexes.
> > >
> > > Thanks
> > >
> > > Jakh
> > >
> > >
> >
> > Jared Still
> > Certified Oracle DBA and Part Time Perl Evangelist ;)
> > jkstill_at_teleport.com
Received on Mon Aug 28 2000 - 08:02:26 CDT