Re: Urgent rac wait problem
Date: Mon, 16 Mar 2009 17:43:36 +0900
Message-ID: <43c2e3d60903160143k3b7cb428k3aed378b3e399820_at_mail.gmail.com>
You solved your problem by updating the statistics.
Why on the earth do you think that CPU count and physical memory size were the reasons?
Dion Cho - Oracle Performance Storyteller
http://dioncho.wordpress.com (english)
http://ukja.tistory.com (korean)
On Mon, Mar 16, 2009 at 3:43 PM, Yechiel Adar <adar666_at_inter.net.il> wrote:
> Problem solved.
> We did compute statistics with histograms on the table and now it works
> fine on both machines.
>
> It turned out that the second node is NOT the same. It has 4 CPU and 4 GB
> of memory instead of 8 CPU and 8 GB of memory in node 1.
> This probably had effect on the explain plan and used less efficient path
> to the data.
>
> Adar Yechiel
> Rechovot, Israel
>
>
>
> Riyaj Shamsudeen wrote:
>
> Adar
> You might want to look at the plan for that SQL on node 2. May be the
> execution plan is different in node 2 compared to node 1. This scenario is
> possible due to difference in instance parameters.
> Can you also print tkprof output wait lines from node 2? I am
> specifically interested in seeing Interconnect performance numbers. What is
> the average time taken for 'gc current block 2 way'?
> What is your average block receive time and average send time from AWR
> reports during this time frame ? That might provide more insights too.
>
> --
> Cheers
>
> Riyaj Shamsudeen
> Principal DBA,
> Ora!nternals - http://www.orainternals.com
> Specialists in Performance, Recovery and EBS11i
> Blog: http://orainternals.wordpress.com
>
> On Sun, Mar 15, 2009 at 4:55 AM, Yechiel Adar <adar666_at_inter.net.il>wrote:
>
>> Update:
>> This happens only when the user connects to node 2.
>> I went a little crazy as I run the same sql from my station and it worked
>> fine.
>> After I took 10046 trace I saw that their trace was on node 2 and mine was
>> on node 1.
>> When I removed the load_balance parameter from the tnsnames, so they will
>> always connect to node 1, the problem went away.
>>
>> So the problem is not so urgent but I would like to work with load
>> balance.
>>
>> Adar Yechiel
>> Rechovot, Israel
>>
>>
>>
>> Yechiel Adar wrote:
>>
>>> We moved from one node rac to 2 node rac.
>>> A simple query takes 9 seconds instead of 0.3 second.
>>>
>>> The user activated 10046 trace and I see a lot of "gc current block
>>> 2-way".
>>>
>>> Help urgently required.
>>>
>>> Adar Yechiel
>>> Rechovot, Israel
>>>
>>> --
>>> http://www.freelists.org/webpage/oracle-l
>>>
>>>
>>>
>>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Mar 16 2009 - 03:43:36 CDT