Re: what's mean of "enqueue hash chains" latch?
- From: "biti_rainy" <biti_rainy@xxxxxxxxx>
- To: "oracle.unix@xxxxxxxxx" <oracle.unix@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 8 Jan 2005 22:33:58 +0800
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@xxxxxxxxxxx
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
>--
>//www.freelists.org/webpage/oracle-l
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
