Re: Same SQL, different tools, different SQL_IDs?

From: Tanel Poder <tanel_at_tanelpoder.com>
Date: Sat, 23 Jul 2011 21:31:28 +0300
Message-ID: <CAMHX9JJKD-czJpG9Qv-CaWEHWVLq0sCYg_3YYN6Wr_un7xq-iw_at_mail.gmail.com>

The hash calculation function (SQL_ID) is universal across platforms ... starting from 10g up to the latest Oracle version, it's just MD5 hash (and a chr(0) is added to the supplied SQL text before hashing). SQL_ID uses half of the 128bit MD5 result only. I've written about it here:

http://blog.tanelpoder.com/2009/02/22/sql_id-is-just-a-fancy-representation-of-hash-value/

To diagnose further you can run dump() function on these SQL texsts. I wouldn't be surprised if some tools (one some platforms) use both CR+LF for line feed and some use only one. In SQLplus regular output they both look the same - but DUMP() function will tell you the difference. Also, some tools may add a space or line feed in the end of the SQL...

--
Tanel Poder
Blog: http://blog.tanelpoder.com
Expert Oracle Exadata book:
http://www.apress.com/9781430233923


On Fri, Jul 22, 2011 at 10:12 AM, Saibabu Devabhaktuni
<saibabu_d_at_yahoo.com>wrote:



> Can you run ora_hash on sql_fulltext for those 2 sql queries which you are

> expecting sql_id's to be same?

>

> select ora_hash(sql_fulltext) from v$sqlstats where sql_id='&sql_id'

>

>

> Thanks,

>  Sai

> http://sai-oracle.blogspot.com

>


--
http://www.freelists.org/webpage/oracle-l

Received on Sat Jul 23 2011 - 13:31:28 CDT