| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Re:Please Advice on Performance Tuning
I think I can answer following part:
>>>
My question was why is the Time difference when run from New schema with
same Packages. You may argue that since I had Imported all tables so
storage parameter are better organised, but I had asked the Payroll guy
to drop all table and recreate it with NEw storage parameter same as
other schema. After this also same time difference is there.
<<<
Becoz' you analyzed the new schema after creating it and the old schema was not analyzed. This would make a major difference ......
HTH,
Rajesh
OCP Oracle 8 & 8i
-----Original Message-----
Sent: Saturday, April 28, 2001 11:00 AM
To: Multiple recipients of list ORACLE-L
Hi Dict,
Thanks for your advice;
As you suggested supposing, I found out few inefficient SQL, corrected
those
SQL,
compiled it in both Schema and run Payroll in both Schema,
Will the time difference(6-9 Minutes) be Solved ? Because I have already
6-9 Minutes time diffrenece in my hand prior to finding this, when run
in
two
different Schema.
Please Look at the way I tried to solve it. I had IMPORTED all objects
from
Payroll
schema to another schema and analyze the table in new schema and run the
payroll. There itself Time taken to run is less(6-9 Mintues) than the
original Schema.
I had not Changed any SQL statements. My question was why is the Time
difference
when run from New schema with same Packages. You may argue that since I
had
Imported all tables so storage parameter are better organised, but I had
asked the
Payroll guy to drop all table and recreate it with NEw storage
parameter
same as
other schema. After this also same time difference is there.
I would like to see why it is taking more time in one schema ?
After settling this issue, I would have definitely gone for Analyzing
the
SQL statement
by TKPROF or auto trace on .
Thanks once again.
Regards.
Naba
> 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
>
> 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,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).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: N J Neog INET: njneog_at_oil.asm.nic.in 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rajesh Dayal INET: Rajesh_at_ohitelecom.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 Sat Apr 28 2001 - 01:50:28 CDT
 |
 |