Home » RDBMS Server » Performance Tuning » Different SQL ID for same SQL/HASH_VALUE (10.2.0.3, Solaris 5.9)
Different SQL ID for same SQL/HASH_VALUE [message #569837] Fri, 02 November 2012 07:55 Go to next message
suddhasatwa_bhaumik
Messages: 18
Registered: October 2011
Location: India
Junior Member
Hello All,

I see one of my SQL's which is ran by the user on a 10.2.0.3 database changing its SQL_ID after some runs even if the query is not changed a bit! However the HASH VALUE for this query remains the same.

Can someone advice how a same query can have different SQL_ID's but same HASH_VALUE?

Note: Statistics are not modified on the base tables of this query.

Please let me know for further information.

Thanks,
Suddhasatwa
Re: Different SQL ID for same SQL/HASH_VALUE [message #569839 is a reply to message #569837] Fri, 02 November 2012 08:00 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Because it's aged out of the SGA probably.
hash_value is the hash of the sql text, so should have the same value always for the same statement, even in different DBs.
Re: Different SQL ID for same SQL/HASH_VALUE [message #569840 is a reply to message #569839] Fri, 02 November 2012 08:02 Go to previous messageGo to next message
suddhasatwa_bhaumik
Messages: 18
Registered: October 2011
Location: India
Junior Member
Thanks for the prompt reply. Can you please explain what you mean by "aged out"?
Also, what are the different scenarios wherein the SQL_ID would be different over time for the same SQL?

Thanks,
Suddhasatwa
Re: Different SQL ID for same SQL/HASH_VALUE [message #569842 is a reply to message #569840] Fri, 02 November 2012 08:18 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
SQL is stored in the SGA. When oracle runs out of space in the SGA for new statements the least recently used statement in there is aged out (deleted) to make room.
Next time the statement is run it is reloaded into the SGA with a new sql_id.
Re: Different SQL ID for same SQL/HASH_VALUE [message #569856 is a reply to message #569842] Fri, 02 November 2012 09:20 Go to previous messageGo to next message
suddhasatwa_bhaumik
Messages: 18
Registered: October 2011
Location: India
Junior Member
I understand the above point now. However it is seen that SQL ID is something in the morning and within a matter of 4-5 hours (when nothing much is running on the database other than normal usage) the SQL_ID is different. Also with different SQL_ID's the performance changes radically.

Please share your thoughts on the same.

Thanks.
Re: Different SQL ID for same SQL/HASH_VALUE [message #569859 is a reply to message #569856] Fri, 02 November 2012 09:31 Go to previous messageGo to next message
BlackSwan
Messages: 22787
Registered: January 2009
Senior Member
does the SQL involved utilize BIND variables?
Re: Different SQL ID for same SQL/HASH_VALUE [message #569871 is a reply to message #569837] Fri, 02 November 2012 11:46 Go to previous messageGo to next message
LNossov
Messages: 288
Registered: July 2011
Location: Germany
Senior Member
Theoretically it is possible. Because Oracle uses the last 4 bypes of MD5 hash value for the hash value of sql and the last 8 bytes for sqlid. So the hash values can be equal whereas the sqlid's different. But in this case the both sql texts have to be also different. Please verify this.
Re: Different SQL ID for same SQL/HASH_VALUE [message #570026 is a reply to message #569871] Sun, 04 November 2012 22:17 Go to previous messageGo to next message
suddhasatwa_bhaumik
Messages: 18
Registered: October 2011
Location: India
Junior Member
@Black Swan: Yes it does. However whenever the SQL is ran, the same value is passed in the BIND variable. Please advice.
@LNossov: That's the issue here - the SQL Text does not change but the SQL_ID does, that too within a very short span of time.

Re: Different SQL ID for same SQL/HASH_VALUE [message #570027 is a reply to message #570026] Sun, 04 November 2012 22:28 Go to previous messageGo to next message
BlackSwan
Messages: 22787
Registered: January 2009
Senior Member
>Also with different SQL_ID's the performance changes radically.

post EXPLAIN PLAN for both fast & slow executions and be sure to include Predicate Information including actual SQL
Re: Different SQL ID for same SQL/HASH_VALUE [message #570056 is a reply to message #569871] Mon, 05 November 2012 04:02 Go to previous messageGo to next message
Roachcoach
Messages: 1202
Registered: May 2010
Location: UK
Senior Member
LNossov wrote on Fri, 02 November 2012 16:46
Theoretically it is possible. Because Oracle uses the last 4 bypes of MD5 hash value for the hash value of sql and the last 8 bytes for sqlid. So the hash values can be equal whereas the sqlid's different. But in this case the both sql texts have to be also different. Please verify this.


That was my understanding too, across multiple different instances so long as the text (including case) is identical, the sql_id is the same.

It's one of the things that makes punting baselines etc across our different environments so quick & easy.
Re: Different SQL ID for same SQL/HASH_VALUE [message #570096 is a reply to message #570026] Mon, 05 November 2012 11:40 Go to previous message
LNossov
Messages: 288
Registered: July 2011
Location: Germany
Senior Member
If you have some outputs from v$sql with different sqlid's for the same sqltext, please upload them. I never heard about such issue.
Previous Topic: Performance degrades as dB size increases
Next Topic: Table rebuild should be done after removing most of its data?
Goto Forum:
  


Current Time: Wed Sep 17 20:56:08 CDT 2014

Total time taken to generate the page: 0.05433 seconds