Re: Performance : Need better performing Oracle!!!

On Wed, 08 Mar 2000 22:20:41 GMT, Sandeep <Sandeep_Khajuria_at_Satyam.com> wrote:

>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).
>

There is something wrong with your PL/SQL code. It should be taking a lot less in that sort of hardware.

>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.
>

Make sure your PL/SQL code is not calling functions and procedures all over the place for every row that comes in. Use inline code if possible.

Alternatively, use some checking upfront for the lighter cases in your functions and return quickly. Leave the heavy processing for the odd ones.

I had to do just that in a recent conversion case similar to yours. In one particular case, I was able to reduce a PL/SQL procedure overhead from 10 hours to 20 minutes by just re-arranging the code using the above techniques.

>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?

Me theenks you'd gain more by having another round of tuning than looking at faster hardware.

>Q2. Will I get better performance from Oracle by migrating to a
>
>Mainframe class machine?

Undoubtedly. But that will cost.

>Q3. Do we have real life examples of Companies using Oracle on
>
>Mainframe for performance reasons?
>

No. Have never found a case where it was needed. And this is with databases an order of magnitude larger than your volumes.

Cheers
Nuno Souto
nsouto_at_nsw.bigpond.net.au.nospam
http://www.users.bigpond.net.au/the_Den/index.html Received on Thu Mar 09 2000 - 00:00:00 CST