Re: SQL v$db_object_cache.full_hash_value

Jonathan,

No, I have not looked at those functions, but you can be sure that I will.

Thank you,

Jared

On Sun, Feb 14, 2021 at 08:14 Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> 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
>>>
>>>
>>> --
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