SQL v$db_object_cache.full_hash_value

Dear list:

Recently I have wanted to generate the FULL_HASH_VALUE directly from SQL Text.

This is because the full_hash_value is only found in one place: v$db_object_cache

Say you want to use a utility that requires the full_hash_value, but the sql does not currently exist in x$kglob ( and of course, also not in v$db_object_cache)

The only option is to generate the full_hash_value if possible.

Of course I first went looking to see if anyone else has done this, and found Luca Canali has done so, years ago, using dbms_obfuscation_toolkit.md5.

That seemed to work.

Then I updated the method, using dbms_crypto.hash instead, and wrote a function to convert the hash to little endian.

It mostly works.

What I found was that about 1 - 1.5% of the generated values for full_hash_value are incorrect.

In a set of 2100 or sql SQL in a test database, 22 were getting an incorrect full_hash_value.

I can tell it is incorrect by comparing the generated value to the value found in v$db_object_cache.

So then I went looking for another method, and came across dbms_utility.get_sql_hash.

Ah, that is more like it! An internal function, dedicated to the task.

Except, it doesn't always work. It fails with exactly the SQL statements that other methods failed with.

The get_sql_hash function has no code of its own; it directly calls ICD_GETSQLHASH, which is found in a C library.

Something came to mind at this point: If any oracle utilities are using ICD_GETSQLHASH and/or dbms_utility.get_sql_hash, they are getting wrong results about 1% of the time.

And of course, there may be some magic I am unaware of in calling this function.

The magic I *am* aware of: it is necessary to append a chr(0) to the end of the SQL statement.
If that is not done, the hash will always be incorrect.

As per this note though:

    Querying V$Access Contents On Latch: Library Cache (Doc ID 757280.1)

... the appended value on the string changes the behavior

For instance, if chr(1) is appended, then get_sql_hash assumes the string passed refers to a table.

There are several different values presented in that note, which are then themselves terminated with chr(0)

However, there is not one for SQL statements. So I guess that SQL is the default.
Looks like some cheap overloading.

So, if you are still with me, I am wondering if anyone knows how this might be improved, so that no hash mismatches occur.