From oracle-l-bounce@freelists.org Sun Jan 9 03:14:51 2005 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id j099Eoc13045 for ; Sun, 9 Jan 2005 03:14:50 -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 j099Enn13038 for ; Sun, 9 Jan 2005 03:14:49 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6A07972C47D; Sun, 9 Jan 2005 04:21:24 -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 14746-32; Sun, 9 Jan 2005 04:21:24 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id AF7CC72C61F; Sun, 9 Jan 2005 04:21:21 -0500 (EST) Message-ID: <01fc01c4f62c$59b785d0$6702a8c0@Primary> From: "Jonathan Lewis" To: References: <1cf1ebe90501080555ef76f5d@mail.gmail.com> Subject: Re: what's mean of "enqueue hash chains" latch? Date: Sun, 9 Jan 2005 09:19:41 -0000 MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-archive-position: 14506 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: jonathan@jlcomp.demon.co.uk Precedence: normal Reply-To: jonathan@jlcomp.demon.co.uk X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Following up on your wait stats as well as the stuff you sent earlier: It is very odd that you seem to have so many locks requested and released (which is what the dml lock allocation and enqueue hash chain latches are about. Check (at the session level preferably) the statistics with names like 'enqueue%'. You may find that you are trying to acquire just a few enqueues but are very unsuccessful in the attempt to acquire the necessary memory structures. As a follow-up check (if the numbers are high) I have loaded my 'snap_enqueues' script onto my web site - this lets you take snapshots of v$enqueue_stats (9i) or x$ksqst (8i) so that you can check which type of enqueue is showing most activity. You can then do a few repeated queries for that type of enqueue against V$lock to see if you get any clues. You can check v$latch_children across the period to see if there are multiple child latches on your system for the two reported latches - and see if the activity is isolated on one of them. (I think I would expect multiple latches on the hash chain, but just one on the dml lock allocation). The waits show 160 seconds of latch wait time in 15 minutes of processing, so the waiting is not the big problem - the big problem (or at least symptom) has to be CPU. The CPU could, of course, be disappearing on the latch spins, but the parallel activity might actually be responsible for most of it. It is interesting to note that you have recorded 3,200 seconds of send blocked in 15 minutes, with only 1,500 blocks - the average wait time is 2.05 seconds: which means that (almost all of ) these waits are parallel slaves being blocked by a single process (which might be the query co-ordinator, or a single PX slave at the next level up). As a reference point - it is always useful to know how many partitions, and how many indexes (and their partition counts if they are not local). (The script is loaded under the 'monitoring' section) Regards Jonathan Lewis http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/seminar.html Public Appearances - schedule updated Dec 23rd 2004 -- http://www.freelists.org/webpage/oracle-l