Re: Urgent rac wait problem
- From: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
- To: ukja.dion@xxxxxxxxx
- Date: Tue, 17 Mar 2009 16:22:50 -0500
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@xxxxxxxxx> 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@xxxxxxxxxxxx> 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)
>>> ================================
>>>
>>>
>>> --
>>
>> //www.freelists.org/webpage/oracle-l
>>
>>
>>
>
Other related posts:
