From oracle-l-bounce@freelists.org Sat Jan 8 07:53:30 2005 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id j08DrUU21080 for ; Sat, 8 Jan 2005 07:53:30 -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 j08DrUn21075 for ; Sat, 8 Jan 2005 07:53:30 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E745372C59E; Sat, 8 Jan 2005 09:00:04 -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 22046-90; Sat, 8 Jan 2005 09:00:04 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1680F72C0FD; Sat, 8 Jan 2005 08:58:43 -0500 (EST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type:content-transfer-encoding; b=uub6Fle/LUbLHm5tYVp0yAjeDQ3leRxOLGRnQGiTLu7hT7kElql4R1EJg4xFtsMvgDfctf00zg8J6Ia1MCPPRAlxwSp7WBmMdERhxkf9/hNv0i2vfGIitVJrguEALqVc18Nu7DVnEO+mruSQd1v/olcMdUbUw/NpcQ2rQ5ZWn9U= Message-ID: <1cf1ebe90501080555ef76f5d@mail.gmail.com> Date: Sat, 8 Jan 2005 21:55:55 +0800 From: eygle To: oracle-l@freelists.org Subject: what's mean of "enqueue hash chains" latch? Mime-Version: 1.0 Content-type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 8bit X-archive-position: 14491 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: oracle.unix@gmail.com Precedence: normal Reply-To: oracle.unix@gmail.com X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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