Oracle-L: RE: index rebuilding performance vs sort_area

From: <Govind.Arumugam_at_alltel.com>
Date: Mon, 13 Jan 2003 14:24:14 -0800
Message-ID: <F001.0052E522.20030113142414@fatcity.com>

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

In the 1st example, the sort_area_size was increased almost 4 times, but we only got about 20% performance improvement.
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_at_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_at_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: <Govind.Arumugam_at_alltel.com
INET: Govind.Arumugam_at_alltel.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

RE: index rebuilding performance vs sort_area_size