Re: Oracle 10g RAC

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_at_gmail.com>
To: smishra_97_at_yahoo.com
Cc: oracle-l_at_freelists.org
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

--
http://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 

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