I’m trying to compare how a query runs on two different 11.2.0.3 systems. One runs on HP-UX Itanium and one runs on 64 bit x86 Linux. Same query, same plan, different hash value.
HP-UX:
SQL_ID 0kkhhb2w93cx0 -------------------- update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,exts ize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19 where ts#=:1 and file#=:2 and block#=:3 Plan hash value: 1283625304 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | | | 2 (100)| | | 1 | UPDATE | SEG$ | | | | | | 2 | TABLE ACCESS CLUSTER| SEG$ | 1 | 65 | 2 (0)| 00:00:01 | | 3 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------
Linux:
SQL_ID 0kkhhb2w93cx0 -------------------- update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,exts ize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19 where ts#=:1 and file#=:2 and block#=:3 Plan hash value: 2170058777 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | | | 2 (100)| | | 1 | UPDATE | SEG$ | | | | | | 2 | TABLE ACCESS CLUSTER| SEG$ | 1 | 64 | 2 (0)| 00:00:01 | | 3 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------
I wonder if the endianness plays into the plan hash value calculation? Or is it just a port specific calculation?
Odd.
– Bobby
Bobby,
yes, it seems to be a result of different endianness: at least Timur Akhmadeev came to the same conclusion in his comment http://oracle-randolf.blogspot.de/2009/07/planhashvalue-how-equal-and-stable-are.html?showComment=1274280461253#c8402177371947878145 on Randolf Geist’s blog. And Fairlie Rego mentioned some endianess-related changes in the calculation on Oracle-L: http://www.freelists.org/post/oracle-l/AW-Temp-usage-on-Linux-vs-solaris,1.
Regards
Martin
Thanks for the links.
– Bobby
Hi Bobby,
if it helps, in the comment section of this Randolf Geist’s post there’s a similar observation as yours:
http://oracle-randolf.blogspot.com/2009/07/planhashvalue-how-equal-and-stable-are.html?showComment=1274280461253#c8402177371947878145
Regards
Thanks for the link.
– Bobby