FreeLists / oracle-l / Re: perforamance issue

["Rakesh Tikku" <rakesh.tikku@xxxxxxxxx>]

Hi Edwin,

In addition to figuring out what the earlier execution plan was and how it
differs from the current one, you need to find out where this sql is coming
from. I have been tuning Oracle Apps sql for a number of years now, and the
coding style does not look familiar. I have a feeling that this sql is a
custom sql and is not coming from Oracle delivered code.

Besides this sql is a blind query (ie it does not have any meaningful filter
conditions) and you need to figure out why is it getting frequently executed
on your system. v$sql.module column for this sql might provide a hint.

All that has been said so far is assuming that this sql is indeed the cause
of the problem, but that might not be the case (since you caught this sql
while sampling active users ). You should generate a hourly statspack
reports for periods of poor performance, so that you get a complete picture
of what is causing the problem. I would not be surprized if it shows that
this sql was not the main culprit after all.

Regards,

Rakesh Tikku
Independent Consultant
Performance Optimization Specialist



On 10/1/06, Mladen Gogala <mgogala@xxxxxxxxxxx> wrote:
> On 10/01/2006 09:45:48 PM, edwin devadanam wrote:
> > Hi gurus,
> > we are having oracle applications instance(11.5.10.) running with
> 9.2.0.5 database version.
> > After applying oracle applications patch(not database patch),the whole
> system turned upside down.
> > All the quieries are taking 20 times more time than before patching.
> > we have done some workarounds but invain.
> > please have a look at the active users sql run on the database before
> and after applying patch.
> > active users before patching : time taken to complete 10sec (please see
> attachement)
> > active users after patch        :  time taken to complete 180sec (please
> see attachment)
> >
> >   Any help would be appreciated.
> >
> >   Thanks,
> >   Edwin.K
> >
>
> Edwin, there are several things:
>
> 1) Do you have plans before the patch?  What exactly does the patch do?
> What problem
>   was the patch supposed to solve? What modules does it patch?
> 2) What kind of the statistics do you have? Did you gather system
> statistics, aka "CPU costing"?
>   Do you have all relevant histograms?
> 3) Did you trace the sessions in question? 10046 and 10053? Are there any
> other changes in
>   effect?
> 4) Did the patch installation re-compute your statistics? Are there any
> new hints added to the
>   application processes?
> 5) What are the application waiting for? Sequential or scattered reads?
> Enqueues?
> 6) Are performance problems centered around one group of applications? One
> table, package or
>   trigger? Is there anything that the modules that users are complaining
> about have in common?
>   Is every module using the same procedure, table or something like that?
> 7) Did you open a level 1 SR with Oracle Corp? Your situation sounds like
> "business
>   critical system down" situation and Oracle is usually very good when
> that happens.
>
> It is extremely hard to give you a precise diagnosis based only on the
> several execution
> plans. The only person who can fix the things is you. This is why the job
> of database administrator
> is so stressful. Now everything depends on you, your knowledge and
> experience. If things fail, you
> will be blamed for everything and probably fired. I hope that I have
> lessened the level of stress that
> you feel right now.
> --
> Mladen Gogala
> http://www.mladen-gogala.com
>
> --
> http://www.freelists.org/webpage/oracle-l