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

  • From: Riyaj Shamsudeen <rshamsud@xxxxxxxxxxxx>
  • To: Peter.Hitchman@xxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx, oracle.unix@xxxxxxxxx
  • Date: Sat, 08 Jan 2005 12:21:24 -0600

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
        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* ;-(

Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
Sent: Saturday, January 08, 2005 11:01 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: what's mean of "enqueue hash chains" latch?

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

Good luck.


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of eygle
Sent: 08 January 2005 13:56
To: oracle-l@xxxxxxxxxxxxx
Subject: what's mean of "enqueue hash chains" latch?


We have a Oracle Database on Solaris8.
With a parallel insert , database slow down heavy suddently.

I find lots of  latch wait in database,from statspack(with 15 minutes
elapse) list:

                                               Pct    Avg
                                  Get          Get   Slps       NoWait
Latch Name                       Requests      Miss  /Miss     Requests
----------------------------- -------------- ------ ------ ------------
active checkpoint queue latch            260    0.0                   0
cache buffers chains              47,228,551    0.0                 552
checkpoint queue latch                 3,263    0.0                   0
dml lock allocation               46,899,746   12.1    0.0            0
enqueue hash chains               46,899,333   39.2    0.0            0
enqueues                                 849    0.0                   0
job_queue_processes parameter             12    0.0                   0
ktm global data                            2    0.0                   0
library cache                         32,739    0.0    0.0            0
library cache load lock                   70    0.0                   0
list of block allocation                  36    0.0                   0


Latch Sleep breakdown for DB: QCB  Instance: qcb  Snaps: 150 -151
-> ordered by misses desc

                               Get                                  Spin
Latch Name                    Requests         Misses      Sleeps Sleeps
-------------------------- -------------- ----------- -----------
enqueue hash chains            46,899,333  18,367,985       6,312
dml lock allocation            46,899,746   5,678,834       1,228
Latch Miss Sources for DB: QCB  Instance: qcb  Snaps: 150 -151
-> only latches with sleeps are shown
-> ordered by name, sleeps desc

                                                    NoWait Waiter
Latch Name               Where                       Misses     Sleeps
------------------------ -------------------------- ------- ----------
dml lock allocation      ktaiam                           0        615
dml lock allocation      ktaidm                           0        604
enqueue hash chains      ksqrcl                           0      3,778
enqueue hash chains      ksqgtl3                          0      2,472

I can not find more info about  "enqueue hash chains".
What' s it mean and how to reduce it ?

The SQL  of parallel insert is :

insert /*+ append parallel(fc_costgatherresult_m,4) */ into=20
 select /*+ parallel(fc_costcalresult_m,4)  */
  from fc_costcalresult_m  where pk_dwbm =3D originaldwbm
  group by

All the table is partition table.
Table fc_costcalresult_m with nearly 40G data.

And we have the parameter:
dml_locks =3D 2000
enqueue_resources =3D 2200

I also want to know why "dml lock allocation"  Requests is so high?

Any suggestion is appreciate.

eygle  from China.
my site:

-- Attached file included as plaintext by Ecartis --
-- Desc: Signature

The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material.  If the reader of this message is not the intended recipient,
you are hereby notified that your access is unauthorized, and any review,
dissemination, distribution or copying of this message including any
attachments is strictly prohibited.   If you are not the intended
recipient, please contact the sender and delete the material from any


Other related posts: