Re: SQL v$db_object_cache.full_hash_value

Jared,
Just picking up an ongoing project of yours.

Have you looked at functions:
dbms_spd_internal.ub8_to_sqlid(number)
dbms_sqltune_util0.sqltext_to_sqlid(clob)

Regards
Jonathan Lewis

On Mon, 18 Jan 2021 at 23:44, Jared Still <jkstill_at_gmail.com> wrote:

> Well, this has turned into something of a project.
>
> One aspect of the project is how to get the full_hash_value, hash_value
> and sql_id from the sql text, when all you have is the sql text.
>
> While you may get the hash_value and sql_id from various bits of the data
> dictionary, if you need the full_hash_value, and it isn't in
> v$db_object_cache, it must be generated.
>
> This may be of some interest:
> https://github.com/jkstill/oracle-demos/blob/master/sql-hash-value/SQL-Hashing.md
>
>
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
> Principal Consultant at Pythian
> Oracle ACE Alumni
> Pythian Blog http://www.pythian.com/blog/author/still/
> Github: https://github.com/jkstill
>
>
>
>
> On Sun, Dec 6, 2020 at 5:40 PM Jared Still <jkstill_at_gmail.com> wrote:
>
>> Note: full_hash_value is directly taken from x$kglob.kglnahsv
>>
>> 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.
>>
>> There is a demo SQL script here that just runs against whatever is in
>> v$sql.
>>
>> https://github.com/jkstill/oracle-demos/blob/master/sql-hash-value/sql-gen-full-hash-demo.sql
>>
>> Hash mismatches are marked by '==>> MISMATCH <<=='
>>
>> There is also a script to create a function, along with a similar demo
>> script.
>>
>> Thank you,
>>
>> Jared Still
>> Certifiable Oracle DBA and Part Time Perl Evangelist
>> Principal Consultant at Pythian
>> Oracle ACE Alumni
>> Pythian Blog http://www.pythian.com/blog/author/still/
>> Github: https://github.com/jkstill
>>
>>
>>

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