Re: multi-threading an Oracle application

Vemp wrote:

> We have a situation like this: Oracle on an AIX box having 8 processors. There
> is a big chunk of PL/SQL code pinned in the SGA. And then a batch program
> that repeatedly uses this layer of PL/SQL code to process transactions.
>
> My doubt is : Will multi-threading improve the performance of the batch program
> ? (currently the program takes too long)
>

No, multi-threading will only help you in the case where you are managing many connections to thedatabase. If your batch job is single threaded and doing only a single connect, either for the duration of the batch job or with a connect, read, disconnect, process, connect strategy, then the mulit-threaded
servers won't help a bit. I'm assuming that you don't have a horde of other connections to manage while
the batch job is running.

> The PL/SQL code reads a lot from the database and also writes to it.
> specifically:
>
> 1. What special/extra steps does one need to take to utilise the underlying
> hardware in a case as above? ( I may look like asking too much here, but
> guidelines/experience will do)
>

I'm very much afraid that without specifics much of what follows will be wide of the mark, but....

If you have control of the database schema, you should review your database design to see that the
design is appropriate for your situation. Do you have the indexes you need to speed your query performance? Do you have too many indexes that are slowing your updates or inserts? Is your data
sufficiently normalized to minimize the data you must read? Have you done any denormalization to limit the impact of joins on your data?

As the questions indicate the tradeoffs involved in design are very sensitive to the specific situation you face and, regretably, generic advice will be of limited use.

Next, you should examine your SQL. Explain plan should work here. Your objective is to determine where you are spending your time. Is the PL/SQL dominated by select processing or update processing?
If the selects are consuming the most time, are you using the indexes you have? The Oracle supplied tuning guide can help you with this stuff. You should also investigate the parallel query option. If your selects can be broken up they can be parallelized to your performance advantage. All other things being equal this offers AIX the opportunity to use your multiple CPU's to advantage.

Then of course, there are the SGA issues. Are you thrashing the shared pool because the pinned PL/SQL consumes too many resources? Run UTLBSTAT and UTLESTAT to get some data on this.
Make sure your buffer cache hit ratio is high. Treat 80% as a minimum unless your database is truly huge and you can't get any more memory. You should also take a look at latch contention. I assume that you are not competing with on-line users when the batch runs, if you are , then locking may be a problem.

If many tables and indexes are being hit, then disk contention can be an issue. Make sure that you are using as many disk spindles as possible. A disk that is at over 40% utilization should be considered a problem. You should also, to the extent possible, balance I/O accross disk controllers.

This information just brushes the surface of performance tuning. If you are getting the impression that many variables play a part in Oracle tuning then you are right.

One other bit of advice that you might already know. Make only one change at a time and preserve your ability to back out of your change.

> 2. Does anyone have experience in such a thing, i.e multi-threading improved
> the performance substantially. ( I am only talking w.r.t the above case).
>
> 3. Are there any pointers to sources on the net that have details on speeding
> up Oracle apps on UNIX machines?
>

I don't really know about the web however, Oracle Performance Tuning by Mark Gurry and Peter Corrigan from O'Reilly and Assoc. is general acknowledged as a good reference for tuning. Also the book Oracle & Unix Performance Tuning by Ahmed Alomari covers some of the specifics of Solaris and HP platforms. Some of that discussion is relevant to AIX. I'm sorry that I could not be of more help.

Allan Received on Fri Nov 21 1997 - 00:00:00 CST