From oracle-l-bounce@freelists.org Sat Jan 8 12:16:49 2005 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id j08IGno06713 for ; Sat, 8 Jan 2005 12:16:49 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id j08IGmn06706 for ; Sat, 8 Jan 2005 12:16:48 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0087072C465; Sat, 8 Jan 2005 13:23:22 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 00335-58; Sat, 8 Jan 2005 13:23:22 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 582BF72C115; Sat, 8 Jan 2005 13:23:19 -0500 (EST) Date: Sat, 08 Jan 2005 12:21:24 -0600 From: Riyaj Shamsudeen Subject: RE: what's mean of "enqueue hash chains" latch? In-reply-to: <3D730671F3FB8C4EA73FD2E94A11F94703AD61@tshuklonmbx02.ERF.THOMSON.COM> To: Peter.Hitchman@thomson.com, oracle-l@freelists.org, oracle.unix@gmail.com Message-id: <000001c4f5ae$dcd3d000$6501a8c0@corp.jcp.com> MIME-version: 1.0 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 Content-type: text/plain Content-Transfer-Encoding: 8bit X-Disclaimer: Outbound Correspondence X-VirusScan: JCP virus scan detected no virus X-archive-position: 14494 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: rshamsud@jcpenney.com Precedence: normal Reply-To: rshamsud@jcpenney.com X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Hi eygle DML locks protects objects from concurrent modification. Frequently, dml lock allocation latch contention is seen with enqueue hash chain latches, as dml_locks are implemented through TM enqueue locks. These resource structures are hanging from enqueue hash chains serialized by enqueue hash chain latches. So, reducing dml lock allocation latch contention should resolve enqueue hash chain latch contention. As Peter mentioned, these locks are released after each commits. Since you are using append hint, I am positive that you must commit after every statement, so your commit frequency is probably high. Further, if you must have this much contention, then I *guess* this is probably OLTP kind of application and append and parallel hints might not be better suited. Also, append hint will add data after the high water mark and not sure, how your extents are setup. So if your extents are smaller, this will lead in to TM locking contention for dictionay objects itself. If I were you, I would looking to eliminate these append and parallel hints for inserts. Sorry, I didn't test this *theory* ;-( Thanks Riyaj "Re-yas" Shamsudeen Certified Oracle DBA -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Peter.Hitchman@thomson.com Sent: Saturday, January 08, 2005 11:01 AM To: oracle-l@freelists.org Subject: RE: what's mean of "enqueue hash chains" latch? Hi, >From MetaLink:- "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 ". 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@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of eygle Sent: 08 January 2005 13:56 To: oracle-l@freelists.org 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 -- 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. -- http://www.freelists.org/webpage/oracle-l