The increase in performance of course comes from
sorting in memory and avoiding disk writes.
Try running this bit of SQL before and after your tests,
and record the numbers:
select
stat.sid,
name.name name,
stat.value
from v$sesstat stat, v$statname name, v$session sess
where
stat.sid = sess.sid
and sess.audsid = sys_context('userenv','sessionid')
and stat.statistic# = name.statistic#
and name.name like '%sort%';
You can then see how many sorts to disk are taking
place. I suspect you're not eliminating as many as you
had hoped for.
You will need to determine how much memory is required
to actually do the entire sort in memory.
Something else to monitor is the amount of memory
available on your system. If increasing the sort_are_size
causes excessive paging to take place, there isn't much
point in changing the value.
Paging can be monitored vi vmstat on Solaris.
HTH
Jared
On Monday 13 January 2003 12:00, Guang Mei wrote:
> 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.6800:00:12.18
>
> sort_area_size 80971520
> Elapsed:00:00:09.9500: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.4400:00:51.46
>
> sort_area_size 80971520
> Elapsed:00:00:52.17 00:00:51.6500: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: Jared Still
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).
