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

hi,eygle   

please read steve adams's oracle 8i intermal service (p41) it describe more detail .

he said :
if we've increased the enqueue_resources parameter, it may be necessary to increase the _enqueue_hash parameter because the size of the hash table is derived from the value of processes.  

you may increase the _enqueue_hash to a prime number .

and do not use parallel insert ?

Best regards

msn: biti_rainy_at_hotmail.com
a dba from alibaba(china)

• from the mail-----
  >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
  >
  

--
http://www.freelists.org/webpage/oracle-l