Re:Please Advice on Performance Tuning

From: <dgoulet_at_vicr.com>
Date: Fri, 27 Apr 2001 09:35:27 -0700
Message-ID: <F001.002F419C.20010427063548@fatcity.com>

Naba,

Performance tuning is an 80/20 ruled operation. Regrettably it sounds like you went to the 20% payback side first. In all of the years I've been a DBA I have always gone into the SQL statements before touching the database. In our case our PeopleSoft payroll application was running slow, like yours. The culprit was an SQL statement that did a Cartesian product against the two largest tables in the schema while crippling the indexes. OOPS!! Therefore, go back and analyze the SQL. I'd be suprised if you could not reduce the run time by 50% or more right there.

Dick Goulet

____________________Reply Separator____________________
Author: njneog_at_oil.asm.nic.in (N J Neog)
Date:       4/26/2001 9:25 PM

Hi all,

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 it within 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

Import the payroll user to another Oracle User in the same database.
Removed unnecessary Index , Put parallelism into few tables by Alter table tab1 Parallel(DEGREE 5)
Analyze the tables with compute statistics;
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,same index ,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 takes to run comes down to 22-24 Min, same as the new user.

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

Thanks in Advance

Naba
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=iso-8859-1" http-equiv=Content-Type>
<META content="MSHTML 5.00.2314.1000" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffdf>
<DIV><FONT face=Verdana size=2>Hi all</FONT><FONT face=Verdana
size=2>,</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Verdana size=2>We have got a Payroll Application develeoped
in-house.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Verdana size=2>It takes 30-34 Minutes to Run this Batch
Application.<BR>General feeling is - it should be able to process it<BR>within 10-15 Minutes may be less than that.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Verdana size=2>I am to look into this problem and give a
solution to it.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Verdana size=2>Steps I had followed as</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Verdana size=2>1. Import the payroll user to another Oracle User

in the same database.</FONT></DIV>
<DIV><FONT face=Verdana size=2><BR>2. Removed unnecessary Index , Put
parallelism into few<BR>   tables by Alter table tab1 Parallel(DEGREE 5)</FONT></DIV>
<DIV><FONT face=Verdana size=2><BR>3. Analyze the tables with compute
statistics;</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Verdana size=2>4. Run the payroll without checking any
inefficient SQL or <BR>   wrong programming logic in the those Packages( It has only<BR>   two Package in it no other stand alone Procedure or  function).</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Verdana size=2>Payroll under this new Oracle User runs in 22-24
Minutes, 6-9 Minutes</FONT></DIV>
<DIV><FONT face=Verdana size=2>less. So, I asked Payroll Person to remove those
unnecessary Index,<BR>asked him to put parallelism into those tables which I had

done.<BR>He runs the Payroll with no Improvement at all. I asked again to
</FONT></DIV>
<DIV><FONT face=Verdana size=2>drop those table and recreate it with new storage

parameter same </FONT></DIV>
<DIV><FONT face=Verdana size=2>as the one created at New User.  Still no
visible Improvement.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Verdana size=2>At this stage every table in both user has same
storage parameter,same<BR>index ,and also analyzed. The question is why in One User <BR>it runs in 22-24 Min and in another 30-34 Min ?</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Verdana size=2>Now Please advice me what do I check or to do, so

that the it time takes<BR>to run comes down to 22-24 Min, same as the new user.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Verdana size=2>Oracle 8.1.4<BR>Optimize goal : choose<BR>Biggest

table haiving record less than 60000 rows.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Verdana size=2>Thanks in Advance</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Verdana size=2>Naba</DIV></FONT></BODY></HTML>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: dgoulet_at_vicr.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Apr 27 2001 - 11:35:27 CDT