Return-Path: Received: from ensim.rackshack.net (root@localhost) by orafaq.net (8.11.6/8.11.6) with ESMTP id h0E3DC516591 for ; Mon, 13 Jan 2003 21:13:12 -0600 X-ClientAddr: 209.68.248.164 Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by ensim.rackshack.net (8.11.6/8.11.6) with ESMTP id h0E3DCc16586 for ; Mon, 13 Jan 2003 21:13:12 -0600 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id PAA78254; Mon, 13 Jan 2003 15:54:58 -0800 (PST) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 0052E522; Mon, 13 Jan 2003 14:24:14 -0800 Message-ID: Date: Mon, 13 Jan 2003 14:24:14 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: Subject: RE: index rebuilding performance vs sort_area_size Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit We have not seen any performance gains after setting the sort_area_size in excess of 50Mb. We have set this as a standard in our re-indexing scripts to set this to 50Mb maximum. HTH. -----Original Message----- Sent: Monday, January 13, 2003 3:00 PM To: Multiple recipients of list ORACLE-L Hi: Today I did some small testing on our db (Oracle 8173 on Solaris 2.8) to test index rebuild (with nologging) performance vs sort_area_size. I used "alter session set sort_area_size = " to set the sort_area_size value. Nothing else was changed. The temp tablespace is 8G. There is no other active sessions running during the test. I selected two indexes for the test. Their sizes are about 20M and 115M respectively so that they were fit into their initial extent after the rebuild. Here is the result: -- 1. rebuild an index with size of about 20M: alter index isi.RUGDATA_INDEX rebuild nologging STORAGE (INITIAL 20M next 20M); sort_area_size 20971520 Elapsed: 00:00:12.49 00:00:11.68 00:00:12.18 sort_area_size 80971520 Elapsed: 00:00:09.95 00:00:09.94 00:00:09.54 -- 2. rebuild an index with size of about 115M: alter index mt.TOPIC_INDEX rebuild nologging STORAGE (INITIAL 114688000 next 114688000); sort_area_size 20971520 Elapsed: 00:00:51.06 00:00:50.44 00:00:51.46 sort_area_size 80971520 Elapsed: 00:00:52.17 00:00:51.65 00:00:51.75 sort_area_size 150971520 Elapsed: 00:00:42.42 00:00:41.81 00:00:41.71 So with this very limited data points, I found 1. In the 1st example, the sort_area_size was increased almost 4 times, but we only got about 20% performance improvement. 2. In the 2nd example, we got 20% performance boost when sort_area_size was increased from 21M to 151M. Is what I see here typical? It seems that with the increase of sort_area_size, the index rebuild will be faster, but not as fast as I hoped. Any comments? Guang Mei _________________________________________________________________ The new MSN 8 is here: Try it free* for 2 months http://join.msn.com/?page=dept/dialup -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: zlmei@hotmail.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: