Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 27901 invoked from network); 6 Sep 2006 11:54:45 -0500 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by softdnserror with SMTP; 6 Sep 2006 11:54:39 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E0966403AB8; Wed, 6 Sep 2006 12:53:25 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 11313-04-2; Wed, 6 Sep 2006 12:53:25 -0400 (EDT) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id DAAB5403B65; Wed, 6 Sep 2006 12:53:24 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 06 Sep 2006 12:52:27 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C8AA64036B2 for ; Wed, 6 Sep 2006 12:52:26 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 11082-02 for ; Wed, 6 Sep 2006 12:52:26 -0400 (EDT) Received: from mail.pdx.polyserve.com (mail2.pdx.polyserve.com [216.64.170.69]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C7496403664 for ; Wed, 6 Sep 2006 12:52:23 -0400 (EDT) Received: from ex2.ms.polyserve.com (ex2.ms.polyserve.com [10.1.0.6]) by mail2.pdx.polyserve.com (Postfix) with ESMTP id 35BB65EC368 for ; Wed, 6 Sep 2006 09:52:34 -0700 (PDT) X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by Ecartis Subject: RE: excessive number of db writer Date: Wed, 6 Sep 2006 09:52:33 -0700 Message-ID: <5D2570CAFC98974F9B6A759D1C74BAD0E5AF2B@ex2.ms.polyserve.com> In-Reply-To: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: excessive number of db writer From: "Kevin Closson" To: "Oracle-L Freelists" X-archive-position: 39058 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: kevinc@polyserve.com Precedence: normal Reply-to: kevinc@polyserve.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p10 (Debian) at avenirtech.net >>>with 20 DBWrs, you have more working sets and more latches protecting the structures. the no.of buffers that each DBWR has to "manage" is lesser when you have more working sets (or DBWRs). Database writers don't "manage buffers". They get posted to work, they grab an LRU latch, they walk it looking at age and state to build a write batch--which includes marking it as "in flight" so that interested sessions will find that the buffer is busy (I seem to recall marking busy requires grabbing the associated chain latch, but I haven't been in the source since 8i so they may have piggy-backed under the LRU latch--who knows...). Then they throw the writes out and start polling for completions. As the completions come it, free the buffer and post free buffer waiters. There are port-level diffs in how the post/wait works such as whether it is just a goofy flurry of singleton IPC semaphore operations or a more elegant vectored posting. It makes about no sense to have more database writers than CPUs if you have async I/O (POSIX, proprietary or ODM). As an aside, and as usual for the sake of history, the idea of multiple writers (processes that build their own write batches) was not considered until the dawn of NUMA systems in the mid-to-late 90s. See, having a single writer build batches and task slaves is absolute murder on a NUMA system. All dbwr latching activity had to take place in the L3 of the building block where dbwr happened to be running. It was, um, horrible. So, Tirthanker Lahiri listened to our findings and analysis of our bus traces and implemented multiple true database writers...how nice. There is more info about that in James Morle's book... -- http://www.freelists.org/webpage/oracle-l