RE: what's mean of "enqueue hash chains" latch?
- From: "Khedr, Waleed" <Waleed.Khedr@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 10 Jan 2005 10:55:41 -0500
Are you sure it's using PQ?
Run it in the explain plan process, and get the execution plan.
Regards,
Waleed
-----Original Message-----
From: eygle [mailto:oracle.unix@xxxxxxxxx]=20
Sent: Saturday, January 08, 2005 8:56 AM
To: oracle-l@xxxxxxxxxxxxx
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:
Pct Avg
Pct
Get Get Slps NoWait
NoWait
Latch Name Requests Miss /Miss Requests
Miss
----------------------------- -------------- ------ ------ ------------
------
active checkpoint queue latch 260 0.0 0
cache buffers chains 47,228,551 0.0 552
0.0
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
and
Latch Sleep breakdown for DB: QCB Instance: qcb Snaps: 150 -151
-> ordered by misses desc
Get Spin
&
Latch Name Requests Misses Sleeps Sleeps
1->4
-------------------------- -------------- ----------- -----------
------------
enqueue hash chains 46,899,333 18,367,985 6,312
18361675/630
8/2/0/0
dml lock allocation 46,899,746 5,678,834 1,228
5677606/1228
/0/0/0
-------------------------------------------------------------
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
Sleeps
------------------------ -------------------------- ------- ----------
-------
dml lock allocation ktaiam 0 615
444
dml lock allocation ktaidm 0 604
784
enqueue hash chains ksqrcl 0 3,778
3,453
enqueue hash chains ksqgtl3 0 2,472
2,832
-------------------------------------------------------------
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
