Performance : Need better performing Oracle!!!

Hi,

Let me give you brief background on the type of work I am talking here. We run data conversions on Oracle. Typical steps involved in a

conversion process is as follows:-

1. Get data from source system as flat files.
2. Load data into Oracle Tables.
3. Run various PL/SQL procedures for moving, scrubbing, cleaning, error

logging, reporting etc.
4. Unload data into flat files using Pro*C. These flat files are then loaded into target system.

One of the present conversion process comprises of 150 PL/SQL

procedures/functions. Size of database is around 10 GB. On a HP 9000,

K-460 4-way box with 1 GB RAM and EMC disk arrays, present Oracle

process(Step 3 above) takes about 6 Hrs.(Reduced from 12 to 6 Hrs.

after tuning PL/SQL procedures and running jobs in parallel making use

of multiple processors).
Step 2 and 4 now take about 1 Hr 30 Mins.

We are heading for some conversion which are going to be 10 times the

size of the present one. This would mean a conversion time of 50-60

Hrs!!!

As it is clear from above, this is a batch process and a typical PL/SQL

procedure creates BIG cursor and then walks through all the records in

a loop to perform cleaning, scrubbing or error reporting on each

record. Work is primarily single-user and sequential in nature.

Multiple processors are made use of by running jobs in parallel.

We are in the process "Modeling" our conversion process to estimate

type of H/W required.

I am looking for answers to following questions:-

Q1. What kind of H/W configuration can get us performance gains to be

in a 8-10 Hr. window, even after running against 10 times present data? Q2. Will I get better performance from Oracle by migrating to a

Mainframe class machine?
Q3. Do we have real life examples of Companies using Oracle on

Mainframe for performance reasons?

Thanks for going through entire mail...
Looking forward for some fruitful response from Gurus/Technical Architects in the forum.

Thanks and Regards
Sandeep Khajuria
Sandeep_Khajuria_at_Satyam.com

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Mar 08 2000 - 00:00:00 CST