Re: Performance : Need better performing Oracle!!!

Hi Erik,

Thanks for your valuable input.
We have already implemented (That's what we think!) your suggestions during our tuning exercise. That is the reason we could reduce earlier window of 12 Hrs. down to 6 Hrs.

Only thing, which we could not do, was your suggestion no. 6. This was largely due to dependency in the present process.

The average processor utilization during the 6 Hr. window is about 150% (out of 400%), although peak time utilization is about 350%.

Let me just put forwards a few questions: - 1. How much time do you think this process would take on a N4000/8, the present box being K460/4.
2. How much time do you think it would take for me to run four such processes in parallel on a single N4000/8 box. Assuming that I take care of the resulting I/O bottlenecks.

Though we are in a process of Modeling this, but I would really appreciate some sort of "scientific gut-feel" from your experience.

Thanks and Regards
Sandeep Khajuria

In article <8a734j$t3h$1_at_web1.cup.hp.com>,   "Erik Trolle" <erik_trolle_at_hp.com> wrote:
> 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.
>
>

--
Sandeep Khajuria


Sent via Deja.com http://www.deja.com/
Before you buy.