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: [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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] 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).
