Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> index rebuilding performance vs sort_area_size
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:
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
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
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
--
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). Received on Mon Jan 13 2003 - 14:00:24 CST
![]() |
![]() |