Re: Oracle 10g RAC

From: Sanjay Mishra <>
Date: Fri, 14 Aug 2009 12:34:25 -0700 (PDT)
Message-ID: <>

Thanks Martin. Let me do some run and see the result and will update.

From: Martin Berger <>
To: Sanjay Mishra <>
Sent: Friday, August 14, 2009 3:15:37 PM
Subject: Re: Oracle 10g RAC

Hi Sanjay,

thank you for answering my 1st ,3rd and 4th question.
Maybe you can also give a short answer to the 2nd (let me summaryze them):
If you have awr/ash licensed, can you grab a report from a 'bad' sql time?
otherwise, can you sample event, seq# and maybe some wait from
v$session of this dedicated sql?

I'm pretty sure, this will bring some new infos.

best regards,

On Fri, Aug 14, 2009 at 16:57, Sanjay Mishra<> wrote:
> 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 <>
> To:
> Cc:
> 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
> --
> 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 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
> Sanjay

Received on Fri Aug 14 2009 - 14:34:25 CDT

Original text of this message