Re: Urgent rac wait problem
Date: Wed, 18 Mar 2009 11:18:08 +0900
Message-ID: <43c2e3d60903171918l53a116c9u358a11201241c5f9_at_mail.gmail.com>
But PGAT is just one of the configurations(parameters) that affect cost calculation.
It has nothing to do with hardware configuration nor performance. It's just a Oracle specific value manually set by DBA.
- If OP has set different PGAT values for both nodes, this would cause the cost difference.
- If OP has set same PGAT values for both nodes, there should be no difference in the cost calculation except other reasons like bind peeking.
To summarize, it's Oracle configuration problem, not problem of hardware performance nor configuration.
Dion Cho - Oracle Performance Storyteller
http://dioncho.wordpress.com (english)
http://ukja.tistory.com (korean)
On Wed, Mar 18, 2009 at 6:22 AM, Riyaj Shamsudeen < riyaj.shamsudeen_at_gmail.com> wrote:
>
> OP machine configuration is that node 1 with 8 CPU/8GB and node 2 with 4
> CPU and 4GB. Typically, in this case, area size parameters are probably
> derived or set to lower value in one instance ( such as pga_aggregate_target
> ). CBO uses PGAT also in calculations. I recently had a situation (in a
> development cluster) where one node had lower PGAT and plan was different
> between that instance and other instances. Increasing PGAT over 2GB resolved
> that issue.
>
> Having 10053 trace file from both nodes( before statistics collection )
> could have confirmed this issue[ Actually, it might be helpful even now].
>
> Adar, Can you please generate 10053 trace files for that SQL from both
> nodes, then do a diff between these two trace files. That will show how
> different these two intances are. Can you also show output from both nodes
> for 'show parameter size'?
>
> I agree that bind peeking at instance level could cause this issue too.
>
> --
> Cheers
>
> Riyaj Shamsudeen
> Principal DBA,
> Ora!nternals - http://www.orainternals.com
> Specialists in Performance, Recovery and EBS11i
> Blog: http://orainternals.wordpress.com
>
> Original message:
> ==============
>
>
> On Mon, Mar 16, 2009 at 9:01 PM, Dion Cho <ukja.dion_at_gmail.com> wrote:
> There should be other reasons for your problem like bind peeking or
> different optimizer configurations.
>
>
> http://www.pythian.com/news/867/stabilize-oracle-10gs-bind-peeking-behaviour-by-cutting-histograms
>
> Oracle has only one source of statistics. Statistics is per database, not
> per instance.
>
> Optimizer puts the system performance into consideration only by means of
> system statistics. But system statistics is per database, not per instance,
> which means that hardware configuration like CPU count, CPU performance,
> memory size and I/O performance has same effect on the multiple instances.
>
>
>
> ================================
> Dion Cho - Oracle Performance Storyteller
>
> http://dioncho.wordpress.com (english)
> http://ukja.tistory.com (korean)
> ================================
>
>
> On Mon, Mar 16, 2009 at 6:50 PM, Yechiel Adar <adar666_at_inter.net.il>wrote:
>>
>>> Since this is the only difference between the servers,
>>> I think that the number of cpus and the memory size may be a factor in
>>> the optimizer calculations.
>>> In the server with fewer resources oracle choose another path that needed
>>> a lot more blocks,
>>> but used less online memory, and caused a lot more gc 2-way wait.
>>> I never delved deep into the optimizer.
>>>
>>> Adar Yechiel
>>> Rechovot, Israel
>>>
>>>
>>>
>>> Dion Cho wrote:
>>>
>>>> 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)
>>>> ================================
>>>>
>>>>
>>>> --
>>>
>>> http://www.freelists.org/webpage/oracle-l
>>>
>>>
>>>
>>
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Mar 17 2009 - 21:18:08 CDT