Re: SQL v$db_object_cache.full_hash_value

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