General performance tips for Oracle

[Anne-Marie Matula <amatula@OIT.UMASS.EDU>]

Hi all,

I've heard how the -XP flag can significantly increase performance.  However
the SQR documentation states that this is only for Sybase.

Does anyone have any good performance tips for running SQRs on an Oracle
platform?

We are running in a 3 tier PeopleSoft environment, that is, the SQRs are
being executed on the client.  We have SQR 3, Unix server, PS 7.01 but are
currently in the process of upgrading the PS and SQR.

I would think taking 1 hour per every 1000 records to post to PS tables is
very bad performance.  For the load in question we used PS SQRs but with
modifications to add our customer requested functionality: add to yet more
tables :-(

In a nutshell the process involves:
For each record in a Suspend table >

I.   Checking if the person exists in the PS Person table based on various
levels of search-match criteria
II.  Adding to and updating various tables
And once all records have been processed:

III. Deleting those records from Suspend that were processed

Transactions are not committed to the database until the entire process has
completed successfully

My assumption is I. takes considerable time and processing one record at a
time requires yet more time.  *However* as a still newbie SQR programmer
(can I still claim that with 4 months experience!?) with no DBA experience I
don't see any feasible solutions.

Thanks in advance for any suggestions you can offer!


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Anne-Marie Matula
Student Information System Project
Office of Information Technologies
Whitmore Bldg, University of Massachusetts, Amherst
Phone:  413/577-0685
Fax:    413/545-2150