From oracle-l-bounce@freelists.org Thu May 26 15:34:59 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j4QKYw58031483 for ; Thu, 26 May 2005 15:34:58 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j4QKYwNi031478 for ; Thu, 26 May 2005 15:34:58 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 696811B2855; Thu, 26 May 2005 14:31:59 -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 26578-01; Thu, 26 May 2005 14:31:59 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id DA4321B2D95; Thu, 26 May 2005 14:31:58 -0500 (EST) X-ME-UUID: 20050526193007363.08D9D1C00B26@mwinf0302.wanadoo.fr Message-ID: <429623BE.8060003@roughsea.com> Date: Thu, 26 May 2005 21:30:06 +0200 From: Stephane Faroult Organization: RoughSea Limited User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.2) Gecko/20040804 X-Accept-Language: en, fr-fr, en-us MIME-Version: 1.0 To: dnt9000@yahoo.com Cc: oracle-l@freelists.org Subject: Re: Interesting problem References: <20050526185240.2748.qmail@web30714.mail.mud.yahoo.com> In-Reply-To: <20050526185240.2748.qmail@web30714.mail.mud.yahoo.com> Content-type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-archive-position: 20367 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: sfaroult@roughsea.com Precedence: normal Reply-To: sfaroult@roughsea.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=0.0 required=5.0 tests=AWL autolearn=ham version=2.63 Dave, Is 'I'd love to set 10046 but can't afford to do it for all processes and cannot catch the culprit' a correct interpretation of what you say? Unfortunately, you cannot catch the problem after it has occurred. It really depends on how your query is called. If it is called only by some sessions, and repeatedly, and if the table which is accessed is pretty typical of the query (ie you don't find it in most queries) - I know, quite a lot of conditions - perhaps you could check V$ACCESS and switch trace on whenever you notice (polling every minute) somebody accesses the table and pray for the problem to reproduce. Another solution, possibly more focused but otherwise a similar idea, would be to get SQL_ADDRESS and HASH_VALUE for the said statement in V$SQL , and check for their popping up in V$SESSION. By the way, it might be interesting to check how many copies of the text (how many children cursors) you have in V$SQL. It may happen that the same query applies to similarly named tables in distinct schemas and the high-water-mark of one of the tables (for instance) is very high (V$ACCESS could also hint at this type of case). HTH Stephane Faroult David Turner wrote: >We've been using set client info on many of our >production systems for some time and this helps us >create resource usage reports so we can identify >owners of bottlenecks. However, we do have some older >systems where this isn't implemented and the >transactions are much shorted so it's very hard to >identify why some of our sessions have longer response >times. > >For instance I have a query that scans maybe 4 blocks >and returns data instantly when I run it manually but >throughout the day periodically it takes well over 10 >seconds, which isn't acceptable. > >I've set up a script that montors the db every minute >but it just doesn't appear to be catching what is >causing our normally fast running queries to >periodically run long. If any of you have any >suggestions on diagnosing this I'd appreciate it. > >The main areas I've focused on are > >A minute by minute report of v$session and v$process >info > >Many of the standard performance tuning stats tracked >via statspack > >Cronjobs > >Feed processes > >with no luck so far. > >Thx, Dave > > -- http://www.freelists.org/webpage/oracle-l