Re: Oracle 10g RAC

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




________________________________
From: Martin Berger <martin.a.berger_at_gmail.com>
To: Sanjay Mishra <smishra_97_at_yahoo.com>
Cc: oracle-l_at_freelists.org
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,
Martin


On Fri, Aug 14, 2009 at 16:57, Sanjay Mishra<smishra_97_at_yahoo.com> 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 <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



      

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