Re: what's mean of "enqueue hash chains" latch?

  • From: eygle <oracle.unix@xxxxxxxxx>
  • To: Riyaj Shamsudeen <rshamsud@xxxxxxxxxxxx>
  • Date: Sun, 9 Jan 2005 11:29:20 +0800

hello,Riyaj ;

This is not a OLTP database,it's a finacial db,we need to produce
report every month.
In that moment,only the session execute the only SQL,no commit still.
Before yesterday,we have the report run about 2 hours. With this heavy
latch contention, it's run out of 10 hours and have no result.

thanks for help.

On Sat, 08 Jan 2005 12:21:24 -0600, Riyaj Shamsudeen
<rshamsud@xxxxxxxxxxxx> wrote:
> Hi eygle
> 
>         DML locks protects objects from concurrent modification.
> Frequently, dml lock allocation latch contention is seen with enqueue
> hash chain latches, as dml_locks are implemented through TM enqueue
> locks. These resource structures are hanging from enqueue hash chains
> serialized by enqueue hash chain latches. So, reducing dml lock
> allocation latch contention should resolve enqueue hash chain latch
> contention.
>         As Peter mentioned, these locks are released after each commits.
> Since you are using append hint, I am positive that you must commit
> after every statement, so your commit frequency is probably high.
> Further, if you must have this much contention, then I *guess* this is
> probably OLTP kind of application and append and parallel hints might
> not be better suited.
>         Also, append hint will add data after the high water mark and
> not sure, how your extents are setup. So if your extents are smaller,
> this will lead in to TM locking contention for dictionay objects itself.
> 
>         If I were you, I would looking to eliminate these append and
> parallel hints for inserts. Sorry, I didn't test this *theory* ;-(
> 
> Thanks
> Riyaj "Re-yas" Shamsudeen
> Certified Oracle DBA
> 
> 
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
> Peter.Hitchman@xxxxxxxxxxx
> Sent: Saturday, January 08, 2005 11:01 AM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: RE: what's mean of "enqueue hash chains" latch?
> 
> Hi,
> From MetaLink:-
> 
> "dml lock allocation
> This latch protects the list of State Objects (dml locks). Every time a
> transaction modifies a table, a DML lock is gotten and released when the
> change is committed. The number of State Objects for dml locks is
> determined by the init.ora <Parameter:DML_LOCKS>".
> 
> So I would be looking at how often you commit.
> Also what sort of turn over are you getting on the online redo logs? It
> may well be that they are too small. You may want to investigate having
> multiple freelists for the table you are inserting into, having many
> parallel inserts (assuming you have enabled parallel DML), may well be
> causing a lot of contention for the header block at the start of the
> segment.
> 
> Good luck.
> 
> Pete
> 
----------------------------------------
eygle  from China.
my site: http://www.eygle.com
--
//www.freelists.org/webpage/oracle-l

Other related posts: