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

From: eygle <oracle.unix_at_gmail.com>
Date: Sat, 8 Jan 2005 21:55:55 +0800
Message-ID: <1cf1ebe90501080555ef76f5d@mail.gmail.com>

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 fc_costgatherresult_m
(kjnd,kjqj,pk_dwbm,pk_deptdoc,pk_psndoc,isproduct,pk_productorbalance,accountcurrtype,producttype,pk_costitem,originaldwbm,originaldept,calmny,pk_client,clienttype,pk_credittype) select /*+ parallel(fc_costcalresult_m,4) */ '2004','11',pk_dwbm,pk_deptdoc,pk_psndoc,isproduct,pk_productorbalance,accountcurrtype,producttype,pk_costitem,originaldwbm,originaldept,sum(calmny),pk_client,clienttype,pk_credittype from fc_costcalresult_m where pk_dwbm = originaldwbm group by pk_dwbm,pk_deptdoc,pk_psndoc,isproduct,pk_productorbalance,accountcurrtype,producttype,pk_costitem,originaldwbm,originaldept,pk_client,clienttype,pk_credittype

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

And we have the parameter:
dml_locks = 2000
enqueue_resources = 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

Received on Sat Jan 08 2005 - 07:53:30 CST