Re: Please Advice on Performance Tuning

NJ,

>From what you've outlined here, everything should be the same except in
creating the new user, did you use import/export on the tables? If you did, then I'd check to see if row chaining might not be a problem.

Have you tried forcing the application to use rule-based optimizer and seeing if you get different results?

Regards,

David A. BArbour
Oracle DBA, OCP

N J Neog wrote:
>
> Hi David,
>
> Thanks for your suggestion.
> I thought TRACE,TKPROF, EXPAIN PLAN
> will be the last thing thing I will do.
> Because There may be lot of SQL Statement I have to select from these two
> Package for probable degradation. Moreover in both user same two package was
> recompiled.
> If inefficient SQL are there in Package then it is there in both schema, so
> if in one user
> takes 22-24 Min. then the same should happen in other user also or otherway
> round.
> The question is what to check for the degradation ?
>
> To add to it.
>
> Our Oracle Server is in RAID.
> Both Schema does not have any auditing or Profile (Except default)
>
> Thanks once again. Regards.
>
> NJN.
>
> ----- Original Message -----
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Sent: Friday, April 27, 2001 11:55 AM
>
> > NJ,
> >
> > For each SQL statement in each instance, run a trace, then run tkprof
> > against it with the explain option. When you compare the two, it should
> > tell you what they are doing differently and what you're biggest time
> > waster is.
> >
> > Regards,
> >
> > David A. Barbour
> > Oracle DBA, OCP
> >
> > > N J Neog wrote:
> > >
> > > 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
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: David A. Barbour
> > INET: dbarbour_at_nucentrix.net
> >
> > 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: David A. Barbour
  INET: dbarbour_at_nucentrix.net

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