Oracle-L: Re: index rebuilding performance vs sort_area

From: Jared Still <jkstill_at_cybcon.com>
Date: Mon, 13 Jan 2003 13:03:38 -0800
Message-ID: <F001.0052E432.20030113130338@fatcity.com>

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.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: Jared Still
  INET: jkstill_at_cybcon.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 - 15:03:38 CST

Re: index rebuilding performance vs sort_area_size