Re: AW: Temp usage on Linux vs solaris

  • From: fairlie rego <fairlie_r@xxxxxxxxx>
  • To: Petr.Novak@xxxxxxxxxxxx, "greg@xxxxxxxxxxxxxxxxxx" <greg@xxxxxxxxxxxxxxxxxx>, Kumar Madduri <ksmadduri@xxxxxxxxx>
  • Date: Tue, 21 Jun 2011 03:15:35 -0700 (PDT)

With regard to plan hash values the plan hash value calculation has changed 
between 10.2 on Big Endian Systems and 11.2 on Little Endian Systems.
So even if the plan is the same the plan hash value will not be the same and 
will have to do a string comparison of all steps.

A workaround to get the same plans is to use

Set "_cursor_plan_hash_version"=2 on the 10.2 source system. ( I have not 
this workaround and this might not be feasible to do)

Fairlie Rego
Senior Oracle Consultant
M: +61 402 792 405

From: Petr Novak <Petr.Novak@xxxxxxxxxxxx>
To: "greg@xxxxxxxxxxxxxxxxxx" <greg@xxxxxxxxxxxxxxxxxx>; Kumar Madduri 
Cc: oracle Freelists <Oracle-L@xxxxxxxxxxxxx>
Sent: Tue, 21 June, 2011 4:28:11 PM
Subject: AW: Temp usage on Linux vs solaris

Plan_hash_value depends also on endianness. If the old and new system has 
different endian  ,  you would need to compare all steps of execution plans to 
be sure, if the execution plan really changed.

Best Regards,

Von: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx]&quot; im 
Auftrag von &quot;Greg Rahn [greg@xxxxxxxxxxxxxxxxxx]
Gesendet: Dienstag, 21. Juni 2011 08:10
Bis: Kumar Madduri
Cc: oracle Freelists
Betreff: Re: Temp usage on Linux vs solaris

Recommendations on triaging:
1) What are the parameter differences (anything that could impact execution 
2) For a  given query with the issue, look at the before and after plans and 
and figure out what is causing this (get a 10053 trace and diff it)

Once you get #2 and look into the cause for the diff, then it can be analyzed 
what could be causing it.

Understand that the increased temp usage is the effect (symptom) of plans 
changing and you want to get to the root cause of why that is the case.
If you have AWR data for both systems you can look at {sql_id, 
comparisons assuming all the objects are identical.

On Mon, Jun 20, 2011 at 10:35 PM, Kumar Madduri 
<ksmadduri@xxxxxxxxx<mailto:ksmadduri@xxxxxxxxx>> wrote:
To minimize issues with version differences we maintained the version of rdbms 
( between solaris and linux and another change to init.ora was not to 
use memory_target and memory_max-target (that should not matter I hope in this 
particular case thoug h. We are using sga_target and sga_max_size )

Greg Rahn

Other related posts: