Re: Oracle 10g RAC

  • From: Sanjay Mishra <smishra_97@xxxxxxxxx>
  • To: Martin Berger <martin.a.berger@xxxxxxxxx>
  • Date: Fri, 14 Aug 2009 07:57:33 -0700 (PDT)

Martin

It is only one Sql  in the application where the performance is reported as it 
might be used very frequently. Regarding SQL execution plan, I am not seeing 
the  difference in the Execution Plan. I had even flush the Shared pool to see 
if that has any impact but plan remain same and query start behaving very fast 
with buffer cache flush. Also the time is random and you can say that after 
every hour, we can see the performance is degraded. Currently a job is schedule 
last week to flush the expensive flush buffer cache operation as otherwise it 
is affecting the application user severely and after that there is no issue 
reported but that is not good approach. 

thanks for your time and comments

Sanjay


________________________________
From: Martin Berger <martin.a.berger@xxxxxxxxx>
To: smishra_97@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Sent: Friday, August 14, 2009 12:29:29 AM
Subject: Re: Oracle 10g RAC

Sanjay, 

is it only a dedicated sql or does all selects behave like this?
if you have ash/awr licesed, can you run a ash report for this period of time? 
(if not, you can manually sample event and seq# from v$session of the session, 
which runs the sql currently)
did the execution plan change?
can you pin the time, when execution switch from fast to slow, and can you 
classify the overall situation on your instances at this point of time? 

Just some starting questions to reduce guesswork.

regards,
 Martin

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

Am 14.08.2009 um 03:56 schrieb Sanjay Mishra:

Hi
>
>I had 3 Node RAC on Solaris and I am facing one scenario very frequently where 
>a select query start taking  too much time in execution. It take only few 
>seconds but after some time it changed to few minutes. At this time if I flush 
>Buffer Cache on all Node, it come back to same few seconds and then after some 
>time, it changed back to same situation. Any expert comments as what can be 
>issue here. Oracle 10.2.0.3. This RAC environment is running 3 Database sand 
>the problem is only with one Database and so it is not linked to interconnect 
>which can give this issue. Also Select is only bringing few records and not 
>high number of records
>
>TIA
>Sanjay
>
>


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Other related posts: