Re: Performance : Need better performing Oracle!!!

Some ideas:

1 Use SQL*Loader in step 2. Run parallel. 2 Use Array Processing whereever possible. E.g. step 4 3 Run utlbstat/utlestat reports and verify 4 Check proper indexes are in place and used. Hidden table scans can waste lots of CPU cycles
5 Remove unused indexes, to eliminate CPU usage to maintain them 6 Check all CPUs are used. CPU usage should be close to 100% all the time 7 If still problems and only more HW can solve the problem, then ask first whether data can be segmented and multiple machines used running in parallel. If yes, multiple N4000/8 would be first choice. Otherwise V2500/32

Erik Trolle - HP Asia Pacific

Sandeep wrote in message <8a6jnk$9eh$1_at_nnrp1.deja.com>...
>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