From oracle-l-bounce@freelists.org Wed Feb 2 22:07:51 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j1347p4W028918 for ; Wed, 2 Feb 2005 22:07:51 -0600 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 j1347pem028914 for ; Wed, 2 Feb 2005 22:07:51 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B23246CC80; Wed, 2 Feb 2005 22:06:55 -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 01137-07; Wed, 2 Feb 2005 22:06:55 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3830D6CC9C; Wed, 2 Feb 2005 22:06:55 -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=s4hLvKpHcgsdPTyCh11v/gffk+YHOWo0C1r2au7CF/W3sXVZyOH2eWcHDMSTWactGT914hLlzY1icENq4Ca1y4SL/HxD/UVn41rWH32HnLKu3HrJT+/G33qez9yQxS8SA7xOS90kAkJpaIcj1rpiywhlnBm22vSzmdGLiDxohMI= Message-ID: Date: Wed, 2 Feb 2005 22:05:14 -0500 From: Richard Ji To: "Oracle L (E-mail)" Subject: cloned db puzzle Mime-Version: 1.0 Content-type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 8bit X-archive-position: 15722 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: richard.c.ji@gmail.com Precedence: normal Reply-To: richard.c.ji@gmail.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Checker-Version: SpamAssassin 2.60 (1.212-2003-09-23-exp) on air891.startdedicated.com X-Spam-Status: No, hits=0.0 required=5.0 tests=none autolearn=ham version=2.60 X-Spam-Level: Hi all, After we cloned a db (8.1.7.4) to another server which has the same # CPU there is one frequently executed stored procedure that is doing 3 to 4 more times buffer gets per execution than the original server. We checked the tables invovled in this stored procedure, indexes, make sure they are analyzed. init parameters are the same. I noticed on the new server the "buffer is not pinned count" is far greater than the original server. What's interesting is that on the new server, when I reviewed statspack from the very beginning after we build it, that stored procedure is in the same ball park as the original one. As we increase load on the new server buffer gets went up, where as for the original server, the buffer gets stays the same even when load goes up. I am looking for any suggestions. Oh, of course, the "latch free" buffer cache chain is the top wait, which is should be the result of more buffer gets. Thanks. Richard -- http://www.freelists.org/webpage/oracle-l