From oracle-l-bounce@freelists.org Tue Aug 24 11:18:20 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i7OGIK519976 for ; Tue, 24 Aug 2004 11:18:20 -0500 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 i7OGIKI19971 for ; Tue, 24 Aug 2004 11:18:20 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B516872D4D0; Tue, 24 Aug 2004 11:22:16 -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 01444-77; Tue, 24 Aug 2004 11:22:16 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 06F3D72CF3C; Tue, 24 Aug 2004 11:22:16 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 24 Aug 2004 11:20:48 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5712672DA2F for ; Tue, 24 Aug 2004 11:20:48 -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 01160-76 for ; Tue, 24 Aug 2004 11:20:48 -0500 (EST) Received: from mproxy.gmail.com (rproxy.gmail.com [64.233.170.205]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id F27CC72CCC9 for ; Tue, 24 Aug 2004 11:20:47 -0500 (EST) Received: by mproxy.gmail.com with SMTP id 76so80157rnk for ; Tue, 24 Aug 2004 09:22:34 -0700 (PDT) Received: by 10.38.102.54 with SMTP id z54mr1257183rnb; Tue, 24 Aug 2004 09:22:34 -0700 (PDT) Received: by 10.38.71.64 with HTTP; Tue, 24 Aug 2004 09:22:34 -0700 (PDT) Message-ID: Date: Tue, 24 Aug 2004 12:22:34 -0400 From: Robyn To: oracle-l@freelists.org Subject: Re: now what?? In-Reply-To: <4C9B6FDA0B06FE4DAF5918BBF0AD82CF09EFEB25@bosmail00.bos.il.pqe> Mime-Version: 1.0 Content-type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 8bit References: <4C9B6FDA0B06FE4DAF5918BBF0AD82CF09EFEB25@bosmail00.bos.il.pqe> X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 8250 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: robyn.sands@gmail.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Mark, These are primarily data buffer / 130 type waits and the sql causing most of them is cursor type junk that needs to be rewritten. I added freelists to a few objects yesterday and saw the numbers decrease slightly, but I believe everything that is left falls exactly into this category. sigh .. further confirmation that this is going to take a long, long time ... Robyn On Tue, 24 Aug 2004 11:29:26 -0400, Bobak, Mark wrote: > In addition to Cary's excellent advice, here's a bit of specific = > knowledge > that may help you. When you encounter buffer busy waits, there are a = > few > things to consider. What type of block is seeing the waits? If it's a > segment header, then looking to increase free lists is probably a good > idea. If it's data blocks (table or index), then what's the P3 value? > If it's 130, that means that the buffer is busy cause data is being read > into it (due to one of the db file sequential/scattered reads). These > types of buffer busy waits are secondary to heavy physical I/O that's > colliding on blocks. (This can easily be seen/simulated by doing > concurrent full table scans on "large" tables, for sufficiently large > values of "large", or even due to concurrent fast full index scans or > grossly inefficient index full or range scans.) So, if you see P3 > set to 130 for data blocks, you want to focus on your inefficient SQL > that's causing db file sequential/scattered reads. When you get those > under control, that entire class of buffer busy waits ought to = > disappear. > There are other cases of buffer busy waits, but in my experience=20 > they're less common, and I won't write more on them unless you reply = > back > that you're seeing things that don't fit either the segment header case=20 > or the data block w/ P3 set to 130 case. > > You definitely want to follow Cary's advice as to the overall approach, > but the above maybe helpful in diagnosing and dealing w/ buffer busy > waits as you encounter them. > > Hope that helps, > > -Mark ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------