OraFAQ Forum: Performance Tuning » Different SQL ID for same SQL/HASH

Home » RDBMS Server » Performance Tuning » Different SQL ID for same SQL/HASH_VALUE (10.2.0.3, Solaris 5.9)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Different SQL ID for same SQL/HASH_VALUE [message #569837]

Fri, 02 November 2012 07:55

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

Report message to a moderator

Re: Different SQL ID for same SQL/HASH_VALUE [message #569839 is a reply to message #569837]

Fri, 02 November 2012 08:00

cookiemonster
Messages: 9135
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.

Report message to a moderator

Re: Different SQL ID for same SQL/HASH_VALUE [message #569840 is a reply to message #569839]

Fri, 02 November 2012 08:02

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

Report message to a moderator

Re: Different SQL ID for same SQL/HASH_VALUE [message #569842 is a reply to message #569840]

Fri, 02 November 2012 08:18

cookiemonster
Messages: 9135
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.

Report message to a moderator

Re: Different SQL ID for same SQL/HASH_VALUE [message #569856 is a reply to message #569842]

Fri, 02 November 2012 09:20

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.

Report message to a moderator

Re: Different SQL ID for same SQL/HASH_VALUE [message #569859 is a reply to message #569856]

Fri, 02 November 2012 09:31

BlackSwan
Messages: 20086
Registered: January 2009

Senior Member

does the SQL involved utilize BIND variables?

Report message to a moderator

Re: Different SQL ID for same SQL/HASH_VALUE [message #569871 is a reply to message #569837]

Fri, 02 November 2012 11:46

LNossov
Messages: 282
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.

Report message to a moderator

Re: Different SQL ID for same SQL/HASH_VALUE [message #570026 is a reply to message #569871]

Sun, 04 November 2012 22:17

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.

Report message to a moderator

Re: Different SQL ID for same SQL/HASH_VALUE [message #570027 is a reply to message #570026]

Sun, 04 November 2012 22:28

BlackSwan
Messages: 20086
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

Report message to a moderator

Re: Different SQL ID for same SQL/HASH_VALUE [message #570056 is a reply to message #569871]

Mon, 05 November 2012 04:02

Roachcoach
Messages: 1032
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.

Report message to a moderator

Re: Different SQL ID for same SQL/HASH_VALUE [message #570096 is a reply to message #570026]

Mon, 05 November 2012 11:40

LNossov
Messages: 282
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.

Report message to a moderator

Previous Topic:	Performance degrades as dB size increases
Next Topic:	Table rebuild should be done after removing most of its data?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 19 17:17:07 CDT 2013

Total time taken to generate the page: 0.68060 seconds