Re: IO issues with Oracle Parallel Server

Rule 1 with OPS - get a couple of days of expert advice as soon as you start thinking about using it.

When you allocate extents to individual instances, you separate the blocks where the inserts take place so there is less pinging - an alternative which may also have help would have been to increase the FREELISTS and FREELIST GROUPS for the table.

The same trick does not work on indexes because the the index block needing the inserted entry is dictated by the data, not by the instance inserting the data.

If any of your indexes are based on meaningless sequence numbers, then you may get some benefit on insertions if you use a reversing trick on the key value (I think 8.1 is the one that allows you to declare a reverse column in an index, but there is an undocumented REVERSE() function that you could use on the sequence value (or to_char() of it) in 8.0(. allows for a reverse column

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

maozmind_at_my-deja.com wrote in message <81r0tt$hmu$1_at_nnrp1.deja.com>...
>We installed OPS (Sun Solaris, Oracle 8.0.6) to
>work with our application. We are facing
>performance issues compare to our 1 node
>settings. The main thing we found is a high rate
>of IOs, both on data and index tablespaces. We
>tried to tune the database parameters
>(GC_FILES_TO_LOCKS and other parallel specific
>parameters), but haven't noticed any change.
>The only change we noticed was after we allocate
>extent to one of the 'bottle neck' tables, on
>separate
>datafiles and instances as follows:
>ALTER TABLE activecalls ALLOCATE EXTENT (SIZE
>100M DATAFILE 'dat25' INSTANCE 1);
>ALTER TABLE activecalls ALLOCATE EXTENT (SIZE
>100M DATAFILE 'dat28' INSTANCE 2);
>
>This change reduced contention, and the IO rate
>decreased dramatically, performance increased a
>little.
>This table has transactions on both nodes -
>INSERT/UPDATE/DELETE transactions on high rate.
>We tried to do the same on the table indexes,
>i.e. separate on two datafiles, one per each
>instance.
>but in this change there was no improvement on
>the IOs made on this tablespace.
>
>Any ideas why this trick worked with the data
>datafile, but not with the indexes? Any hits for
>such
>a problem ?
Received on Sun Nov 28 1999 - 05:56:41 CST