Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 18522 invoked from network); 18 Jul 2007 11:15:44 -0500 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by 69.64.49.119 with SMTP; 18 Jul 2007 11:15:43 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8AFBA714EE5; Wed, 18 Jul 2007 12:13:32 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 08700-04; Wed, 18 Jul 2007 12:13:32 -0400 (EDT) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 01514714EF5; Wed, 18 Jul 2007 12:13:31 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 18 Jul 2007 11:32:23 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7126A714B2C for ; Wed, 18 Jul 2007 11:32:23 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 31474-06 for ; Wed, 18 Jul 2007 11:32:23 -0400 (EDT) Received: from outbound5-dub-R.bigfish.com (outbound-dub.frontbridge.com [213.199.154.16]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1EE23714B16 for ; Wed, 18 Jul 2007 11:32:20 -0400 (EDT) Received: from outbound5-dub.bigfish.com (localhost.localdomain [127.0.0.1]) by outbound5-dub-R.bigfish.com (Postfix) with ESMTP id 90F20418FE4 for ; Wed, 18 Jul 2007 15:34:01 +0000 (UTC) Received: from mail33-dub-R.bigfish.com (unknown [10.5.252.3]) by outbound5-dub.bigfish.com (Postfix) with ESMTP id 79C4E838050 for ; Wed, 18 Jul 2007 15:34:01 +0000 (UTC) Received: from mail33-dub (localhost.localdomain [127.0.0.1]) by mail33-dub-R.bigfish.com (Postfix) with ESMTP id 57A72830276 for ; Wed, 18 Jul 2007 15:34:01 +0000 (UTC) X-BigFish: VP X-MS-Exchange-Organization-Antispam-Report: OrigIP: 12.41.53.18;Service: EHS Received: by mail33-dub (MessageSwitch) id 1184772841276228_2186; Wed, 18 Jul 2007 15:34:01 +0000 (UCT) Received: from dsmtpg04.col.discoverfinancial.com (unknown [12.41.53.18]) (using TLSv1 with cipher EDH-RSA-DES-CBC3-SHA (168/168 bits)) (No client certificate requested) by mail33-dub.bigfish.com (Postfix) with ESMTP id D82811780069 for ; Wed, 18 Jul 2007 15:34:00 +0000 (UTC) Received: from dsmtpg01.novus.relay (DSMTPG01 [12.41.53.12]) by dsmtpg04.col.discoverfinancial.com with ESMTP id l6IFYCwa020868 for ; Wed, 18 Jul 2007 11:34:17 -0400 Received: from dfscolm02.co.discoverfinancial.com (dfscolm02.co.discoverfinancial.com [199.12.96.170]) by dsmtpg01.novus.relay with ESMTP id l6IFWVc36042 for ; Wed, 18 Jul 2007 11:32:31 -0400 In-Reply-To: <49d668000707172110j64ef6bc4k42713b5e02f0bc3a@mail.gmail.com> Subject: Re: tunning an index build To: "Alexander Fatkulin" Cc: oracle-l@freelists.org Message-ID: From: genegurevich@discover.com Date: Wed, 18 Jul 2007 10:32:11 -0500 X-MIMETrack: Serialize by Router on DFSCOLM02/MSRV/DFSI(Release 7.0.1FP1|April 17, 2006) at 07/18/2007 10:32:11 MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII X-archive-position: 50998 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: genegurevich@discover.com Precedence: normal Reply-to: genegurevich@discover.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain Alex, Yes, I have set pga_aggregate_target to 1.5G and workarea_size_policy is manual. I thought that this will make the sort_area_size (and all other area_size parameters) unused. IS that not the case? I am also working with the UNIX team to address the I/O contention that I am seeing. thank you for your input Gene Gurevich "Alexander Fatkulin" genegurevich@discover.com cc 07/17/2007 11:10 oracle-l@freelists.org PM Subject Re: tunning an index build Do you use those 1.5G through pga_aggregate_traget? If so, try to use more memory by switching your session to workarea_size_policy=manual and dedicating as much to sort_area_size as you can. Make sure you spent enough time on temp I/O before doing that. On 7/17/07, genegurevich@discover.com wrote: > Hi all: > > I think I have posted on this issue before, but I can't find my old Emails > so - sorry for the repeated question. > Here is the situation. I am running oracle 9208 and have a 40G table with > a lot of indices - mostly bitmap > Their sizes are under 1G. These indices take about 20 to 30 minutes to > build. That seems to be too long. > I have just waited for 24 minutes to build a 82M index. The index is > parallelized 4 way, the table is > parallelized 6 ways. I have 2G sga and 1.5G pga. I do see a lot of IO waits > (via vmstat) and I have UNIX > team looking into that. I think I have plenty of memory and the parallelizm > should be enough for a small index. > > Any thoughts on what else I should look at? > > thank you > > Gene Gurevich > > > -- > http://www.freelists.org/webpage/oracle-l > > > -- Alex Fatkulin, The Pythian Group, http://www.pythian.com/blogs/author/alexf/ -- http://www.freelists.org/webpage/oracle-l