Re: Performance : Need better performing Oracle!!!

Sandeep

this looks like a classic datawarehouse Extract/Transform/Load operation to me. There's a lot of software out there that does this alot more efficiently than PL/SQL (from my experience). see http://www.dwinfocenter.org/clean.html for a full-ish list of vendors

Of course Perl/C etc can do the job a lot more effiencently as well!

martin

Sandeep wrote:
>
> 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 Thu Mar 09 2000 - 00:00:00 CST