FreeLists / oracle-l / RE: perforamance issue

["Peter McLarty" <Peter_McLarty@xxxxxxxxxxxxxxxxxxxxx>]

Gee with friends like you 

You are a bad man

Cheers

Peter McLarty
Technical Consultant
Service Delivery 


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mladen Gogala
Sent: Monday, 2 October 2006 10:42 AM
To: edwin_kodamala@xxxxxxxxx
Cc: Oracle-L Freelists; edwin.kodamala@xxxxxxxxx
Subject: Re: perforamance issue


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


--
http://www.freelists.org/webpage/oracle-l