Please Advice on Performance Tuning

Hi all<FONT face=Verdana
size=2>,
 

We have got a Payroll Application develeoped in-house.
 

It takes 30-34 Minutes to Run this Batch Application.General feeling is - it should be able to process itwithin 10-15 Minutes may be less than that.
 

I am to look into this problem and give a solution to it.
 

Steps I had followed as
 

1. Import the payroll user to another Oracle User in the same database.
2. Removed unnecessary Index , Put
parallelism into few   tables by Alter table tab1 Parallel(DEGREE 5)
3. Analyze the tables with compute
statistics;
 

4. Run the payroll without checking any inefficient SQL or    wrong programming logic in the those Packages( It has only   two Package in it no other stand alone Procedure or  function).
 

Payroll under this new Oracle User runs in 22-24 Minutes, 6-9 Minutes
less. So, I asked Payroll Person to remove those unnecessary Index,asked him to put parallelism into those tables which I had done.He runs the Payroll with no Improvement at all. I asked again to

drop those table and recreate it with new storage parameter same
as the one created at New User.  Still no visible Improvement.
 

At this stage every table in both user has same storage parameter,sameindex ,and also analyzed. The question is why in One User it runs in 22-24 Min and in another 30-34 Min ?
 

Now Please advice me what do I check or to do, so that the it time takesto run comes down to 22-24 Min, same as the new user.
 

Oracle 8.1.4Optimize goal : chooseBiggest table haiving record less than 60000 rows.
 

Thanks in Advance
 

Naba Received on Fri Apr 27 2001 - 00:08:04 CDT