From oracle-l-bounce@freelists.org Mon Jul 19 13:09:39 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i6JI9O332228 for ; Mon, 19 Jul 2004 13:09:34 -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 i6JI9E632184 for ; Mon, 19 Jul 2004 13:09:24 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 103D072CA31; Mon, 19 Jul 2004 12:48:49 -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 18503-86; Mon, 19 Jul 2004 12:48:48 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 53F0E72C648; Mon, 19 Jul 2004 12:48:48 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 19 Jul 2004 12:47:23 -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 00ACE72C0B6 for ; Mon, 19 Jul 2004 12:47:23 -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 18519-64 for ; Mon, 19 Jul 2004 12:47:22 -0500 (EST) Received: from web81602.mail.yahoo.com (web81602.mail.yahoo.com [206.190.37.119]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with SMTP id 8234972C035 for ; Mon, 19 Jul 2004 12:47:22 -0500 (EST) Message-ID: <20040719181250.20676.qmail@web81602.mail.yahoo.com> Received: from [216.145.53.41] by web81602.mail.yahoo.com via HTTP; Mon, 19 Jul 2004 11:12:50 PDT Date: Mon, 19 Jul 2004 11:12:50 -0700 (PDT) From: Sai Selvaganesan Subject: Re: About having a large SGA for a DataWarehouse To: oracle-l@freelists.org In-Reply-To: <6.1.0.6.0.20040719202749.0245b388@pop.singnet.com.sg> MIME-Version: 1.0 Content-type: text/plain X-Virus-Scanned: by amavisd-new at freelists.org Content-Transfer-Encoding: 8bit X-archive-position: 5413 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: ssaisundar@sbcglobal.net Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org in 9i Are the Hash/sort area sizes required even when using work_area_size_policy = AUTO? are these not supposed to be taken care of automatically for each process. so wehen we say "increasing sort and hash to 64M" should these be set to 64m or oracle will do this "automatically" please advise sai Hemant K Chitale wrote: At my site we'll be migrating a [currently very small 20GB] Corporate DataWarehouse from 32-bit 8.1.7 Solaris to 32-bit 9i Linux. It will grow rapidly with Manufacturing Datamarts to probably 500GB in a year. Some of the DataMart teams are pushing for a very large SGA while I would rather have a large PGA_AGGREGATE_TARGET [not using 10g and Automatic Memory Management] My rebuttal was : We must consider how large we really need the SGA. A too large SGA [assuming that it is not too large as to cause excessive Paging and begin Swapping at the OS level] can result in : a. Performance impact in Checkpoints -- larger checkpoints, longer write times for DBWR b. Performance impact in Free Buffer scans -- longer buffer chains c. Performance impact on Cache Buffer Chains latch -- more buffers per latch means that the latch may be held more frequently d. Delayed Block Cleanouts -- modified blocks remaining in memory requiring cleanups and causing potential ORA-1555s Comments / Recommendations / Corrections from this list, please ? Hemant K Chitale Oracle 9i Database Administrator Certified Professional http://web.singnet.com.sg/~hkchital "A man's reputation is what other people think of him; his character is what he really is." -- Miner, Jack ---------------------------------------------------------------- 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 ----------------------------------------------------------------- ---------------------------------------------------------------- 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 -----------------------------------------------------------------