index rebuilding performance vs sort_area_size

[Guang Mei]

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: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).