Re: IO issues with Oracle Parallel Server

In article <943790300.17031.0.nnrp-11.9e984b29_at_news.demon.co.uk>,   "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
>
> 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 ?
>

I have not used them yet, but reverse indexes were added with Oracle 8 and I remember that the example in the manual specifically mentioned their use with OPS to solve the type of problem mentioned in this thread.

With OPS it is desirable that all, or at least the great majority, of updates to a specific table come from only one instance. We were able to partition our application between material and financial users via the system profile for a server based application. But if you can not do this you will need to study and make use of the available gc parameters, free list groups (ugh!), and any other options available.

May I also suggest you consider allocating more locks via gc_files_to_locks to index data files than to table files. Also run $ORACLE_HOME/rdbms/admin/catparr after maintenance so that v$ping is accurate.

Jonathan, gave very good advise when he suggested you consult with 'experts' before using OPS. We have used it since ver. 7 and are very happy with it, but it does add some additional factors to application design and management.
--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sun Nov 28 1999 - 10:55:13 CST