1 2 Previous Next 25 Replies Latest reply on Apr 13, 2018 12:22 PM by AndrewSayer

    Hash value generated does not match

    user1356432

      Hi,

      We are using DBMS_SQLHASH.GETHASH to get the hash value of a result set for one of our requirements. The hash value generated by using the mentioned function in SQL * PLUS is different from that is generated in SQL Developer. Any idea about this discrepancy.

       

      Thanks.

      Regards; Gopal                            

        • 1. Re: Hash value generated does not match
          padders

          I would expect in the first instance that either the query itself is returning different results in the two sessions or that possibly some session specific config (e.g. NLS_DATE_FORMAT) is different.

          • 2. Re: Hash value generated does not match
            Paulzip

            Does the sqltext have ordering?  If not, it probably should.

            Are your client session settings the same? Date format, number format etc?

            • 3. Re: Hash value generated does not match
              Jonathan Lewis

              Further to the comment by padders and paulzip

              a) differencesin session format parameters such as nls_date_format could produce different output for identical stored values, and this would change the hash value

              b) differences in optimizer parameters could lead to the result set appearing in a different order, and this would change the hash value

              c) even WITH an "order by" clause you could get the same result set in a different order if the set of columns used in the order by clause did not identify each row uniquely.

               

              A couple of quick checks:

              Select the first couple of rows from your result set in the two environments and check by eye that they look identical

              Extract the execution plans from memory (dbms_xplan.display_cursor()) for the two queries and check that they match - including a match on the predicate sections.

               

              Regards

              Jonathan Lewis

              1 person found this helpful
              • 4. Re: Hash value generated does not match
                John Thorton

                user1356432 wrote:

                 

                Hi,

                We are using DBMS_SQLHASH.GETHASH to get the hash value of a result set for one of our requirements. The hash value generated by using the mentioned function in SQL * PLUS is different from that is generated in SQL Developer. Any idea about this discrepancy.

                 

                Thanks.

                Regards; Gopal

                post COPY & PASTE results that show above is correct.

                 

                How do I ask a question on the forums?

                • 5. Re: Hash value generated does not match
                  Stefan Jager

                  How many rows are there in your resultset?

                   

                  SQL Developer has a tendency to only select the first 50 rows (depending on configuration I think), whereas SQL Plus will select all of them.

                  • 6. Re: Hash value generated does not match
                    Mark D Powell

                    Gopal, please see the following to verify what others have posted about the usage of DBMS_SQLHASH and potentially help you determine how to resolve the issue, if possible.

                    - -

                    DBMS_SQLHASH Does Not Produce The Same Value In All Cases (Doc ID 1569256.1)

                    - -

                    HTH -- Mark D Powell --

                    1 person found this helpful
                    • 7. Re: Hash value generated does not match
                      Jonathan Lewis

                      I'd guess it's something like this:
                      Cut-n-past from SQL Developer screens:

                       

                      variable b1 varchar2(64)

                       

                      begin

                          :b1 := dbms_sqlhash.gethash('select ctime from obj$',dbms_crypto.hash_md5);

                      end;

                      /

                       

                      print b1

                       

                       

                       

                      PL/SQL procedure successfully completed.

                       

                       

                      B1

                      --------------------------------------------------------------------------------

                      18B862AF8C005A042B2A66A988D536FF

                       

                       

                       

                       

                      Now from SQL*Plus:

                       

                      SQL> variable b1 varchar2(64)

                      SQL> begin

                        2  :b1 := dbms_sqlhash.gethash('select ctime from obj$',dbms_crypto.hash_md5);

                        3  end;

                        4  /

                       

                      PL/SQL procedure successfully completed.

                       

                      SQL> print b1

                       

                      B1

                      --------------------------------------------------------------------------------------------------------------------------------

                      63828654857CFF6A71221850D33FCEDE

                       

                      SQL>

                       

                       

                      Regards

                      Jonathan Lewis

                      • 8. Re: Hash value generated does not match

                        Any idea about this discrepancy.

                        1. you get the hash for a result set using sql*plus

                        2. you get the hash for a result set using sql dev

                        3. the hashes are different.

                         

                        Therefore the natural conclusion is that the result sets are different.

                         

                        Therefore the OBVIOUS thing to do first is: GENERATE AND MANUALLY COMPARE THE TWO RESULT SETS.

                         

                        Did you do that? If not then do it and post the results.

                         

                        1. your friend John says he pulled two apples from the same basket at the grocery store.

                        2. he said they were two DIFFERENT types of apple but should have been the same since they were in the same basket.

                         

                        Wouldn't your first response to John be: SHOW ME THE APPLES?

                        • 9. Re: Hash value generated does not match
                          Jonathan Lewis

                          Stefan Jager wrote:

                           

                          How many rows are there in your resultset?

                           

                          SQL Developer has a tendency to only select the first 50 rows (depending on configuration I think), whereas SQL Plus will select all of them.

                           

                          Stefan,

                           

                          Not relevant in this case - the complete result set is selected internally by the function which then computes the hash value from the rows and returns only a single value.

                           

                          Regards

                          Jonathan Lewis

                          • 10. Re: Hash value generated does not match
                            Stefan Jager

                            Hi Jonathan,

                             

                            I beg to differ: SQL Developer tends to modify the SQL statement before it is sent to Oracle - so the two statements as received by Oracle won't be the same. Hence the different hashes, as it should be. I do admit at the moment I have no proof of that, and if I'm honest I'm not taking your copy and paste as proof - SQL Dev could be clever enough to see through an anonymous block and still modify the select statement. I'd like to see the traces for both your statements, because only that will confirm anything either way.

                             

                            Besides that: especially an old hash like MD5 should give the same result given the same input. Therefore even with your statements something funny is going on between the two clients. How would you explain that? Those two hashes SHOULD be the same, unless the ctime column changes by the minute.

                             

                            Regards,

                            Stefan

                            • 11. Re: Hash value generated does not match
                              Jonathan Lewis

                              Stefan Jager wrote:

                               

                              Hi Jonathan,

                               

                              I beg to differ: SQL Developer tends to modify the SQL statement before it is sent to Oracle - so the two statements as received by Oracle won't be the same. Hence the different hashes, as it should be. I do admit at the moment I have no proof of that, and if I'm honest I'm not taking your copy and paste as proof - SQL Dev could be clever enough to see through an anonymous block and still modify the select statement. I'd like to see the traces for both your statements, because only that will confirm anything either way.

                               

                              Besides that: especially an old hash like MD5 should give the same result given the same input. Therefore even with your statements something funny is going on between the two clients. How would you explain that? Those two hashes SHOULD be the same, unless the ctime column changes by the minute.

                               

                              Regards,

                              Stefan

                               

                              Stefan,

                               

                              I was demonstrating that it was possible to get different hash values from the same statement in two different environments to see if that helped John Thorton make an intelligent comment. Naturally I know why the two results were different in my example - I had a different setting for the nls_date_format.

                               

                              As far as your "modify the statement" comment is concerned, that's not how SQL Developer limits the return set to the first 50 rows; it's basically doing something you could do in SQL*Plus through "set pagesize 53 set pause on".  It's possible, of course, that some tools (and maybe even SQL*Developer) would decide to set the optimizer mode to use first_rows_N optimization, and you might even find some tools (though I would be surprised if you did) that injected a /*+ first_rows(N) */ hint;  and maybe that can be adjusted in the configuration screen. But that's about the size of the rowsource returned - and a call to dbms_sqlhash.gethash() returns a single value to represent the entire result set of query.

                               

                              Regards

                              Jonathan Lewis

                              • 12. Re: Hash value generated does not match

                                How many rows are there in your resultset?

                                 

                                SQL Developer has a tendency to only select the first 50 rows (depending on configuration I think), whereas SQL Plus will select all of them.

                                How is ANY of that relevant?

                                 

                                The function OP is calling does NOT return a result set - it returns a scalar.

                                 

                                Neither client will EVER see a result set - so the number of rows isn't an issue.

                                I beg to differ: SQL Developer tends to modify the SQL statement before it is sent to Oracle - so the two statements as received by Oracle won't be the same.

                                That seems to be quite a LEAP from 'tends to modify' to "won't be the same".

                                 

                                The example Jonathan gave was a block - not a sql statement. Sql Dev does NOT modify strings within blocks.

                                • 13. Re: Hash value generated does not match

                                  As far as your "modify the statement" comment is concerned, that's not how SQL Developer limits the return set to the first 50 rows; it's basically doing something you could do in SQL*Plus through "set pagesize 53 set pause on".

                                  Sql Dev is written in Java and uses a Java-based JDBC driver. It defines a buffer based on the JDBC batch size that has been set and then FETCHES the results to try to fill the buffer.

                                   

                                  But your example does NOT return a result set - it returns a scalar. So the batch/fetch size doesn't enter into it. Doing a hash on  a result set is about the same as doing a count(*). In neither case is the entire result set sent to the client when the aggregation is done on the server.

                                   

                                  Until/unless OP decides to post the actual queries and results they are using it is anyone's guess what really happened. But if I had to guess my guess would be that OP got confused about what they really did and posted the thread before realizing their mistake.

                                   

                                  We'll have to wait and see - wouldn't surprise me at all if we never hear from them again.

                                  • 14. Re: Hash value generated does not match
                                    Jonathan Lewis

                                    rp0428 wrote:

                                     

                                    As far as your "modify the statement" comment is concerned, that's not how SQL Developer limits the return set to the first 50 rows; it's basically doing something you could do in SQL*Plus through "set pagesize 53 set pause on".

                                    Sql Dev is written in Java and uses a Java-based JDBC driver. It defines a buffer based on the JDBC batch size that has been set and then FETCHES the results to try to fill the buffer.

                                     

                                     

                                    I have heard that theory before  - but in my version of SQL Developer (17.4.1.054) under Tools -> Preferences -> Database -> Advanced there's a preference called the SQL Array Fetch Size which can be set to a value between 50 and 200, and when I executed a query that select a row of size roughly 512KB SQL Developer still started with (and v$sql reported) a single fetch of 50 rows.

                                     

                                    But your example does NOT return a result set - it returns a scalar.

                                    No need to tell me that really, given that it's paraphrasing something I told Stefan earlier on:  "Not relevant in this case - the complete result set is selected internally by the function which then computes the hash value from the rows and returns only a single value."

                                     

                                    But if I had to guess my guess would be that OP got confused about what they really did.

                                     

                                    I prefer to assume that people are reasonably competent and sensible until they prove otherwise, and the OP's opening statement doesn't sound particularly confused or confusing to me:

                                    "We are using DBMS_SQLHASH.GETHASH to get the hash value of a result set for one of our requirements." 

                                    "The hash value generated by using the mentioned function in SQL * PLUS is different from that is generated in SQL Developer."

                                     

                                     

                                    Regards

                                    Jonathan Lewis

                                    1 2 Previous Next