Re: Oracle7 / Performance

My reply bounced, so I'll post to the net willyk_at_kbigate.novalis.public.sub.org (Willy Klotz) writes:
>Currently, we are installing Oracle on a IBM RS/6000.
>Environment: RS/6000 Mod. 570, AIX 3.2, 384 MB main storage, 10 GB
>online storage, Oracle RDBMS V 7.0.12, SQL*Forms 3.0, PL/SQL,
>SQL*Reportwriter.
>The application will put heavy load on the database. Generally speaking,
> there are two classes of programs: programs which require immediate
>response to the user (in less than a second), and others where response
>time is not a primary factor.
>What can I do to give "priority" to the programs requiring immediate
>response ? I thought of setting up two separate instances of the same
>database with different prioritys, and then connecting to the different
>instances depending on the program to run. Is this the right way ? Have
>anyone tried this ? Are there other ways to do this ?

The UNIX nice command and system call might be worth looking into. For things like forms, where some forms are always critical and others are not, you could put the nice C call in a user exit and raise and lower priorities as required.

This is not a guarantee, however.

General tuning is probably required. About a week ago I posted some excellent notes on tuning an SQL statement passed to me by Tina London. These are worth reading.

The problem is that the database itself (whether via another instance over database links or whatever) will still be slowed by locks from any inefficient process. (Were you thinking of links when you mentioned "same database", different instances?). And the whole machine can be slowed, of course.

Two different boxes with different databases, with replication between them and both acting essentially as servers may help. The cost of this, however, could easily be prohibitive.

Good luck

-- 
David T. Bath             | Email:dtb_at_otto.bf.rmit.oz.au (131.170.40.10)
Senior Tech Consultant    | Phone: +61 3 347-7511 TZ=AEST-10AEDST-11
Global Technology Group   | 179 Grattan St, Carlton, Vic, 3153, AUSTRALIA
"The robber of your free will does not exist" - Epictetus