"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
-- --Original Message-- --
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] On Behalf Of eygle
Sent: 08 January 2005 13:56
To: oracle-l@(protected)
Subject: what 's mean of "enqueue hash chains " latch?
hello;
We have a Oracle 8.1.7.0.0 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:
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
fc_costgatherresult_m
(kjnd,kjqj,pk_dwbm,pk_deptdoc,pk_psndoc,isproduct,pk_productorbalance,ac
countcurrtype,producttype,pk_costitem,originaldwbm,originaldept,calmny,p
k_client,clienttype,pk_credittype)
select /*+ parallel(fc_costcalresult_m,4) */
'2004 ', '11 ',pk_dwbm,pk_deptdoc,pk_psndoc,isproduct,pk_productorbalance,a
ccountcurrtype,producttype,pk_costitem,originaldwbm,originaldept,sum(cal
mny),pk_client,clienttype,pk_credittype
from fc_costcalresult_m where pk_dwbm =3D originaldwbm
group by
pk_dwbm,pk_deptdoc,pk_psndoc,isproduct,pk_productorbalance,accountcurrty
pe,producttype,pk_costitem,originaldwbm,originaldept,pk_client,clienttyp
e,pk_credittype
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: http://www.eygle.com
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
-- 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
computer.
