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
----- Original Message -----
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Sent: Friday, April 27, 2001 8:05 PM
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
