Re: SQL v$db_object_cache.full_hash_value

Thank you Jonathan, I did see your blog on that.

The shared_pool was flushed after the last time markhot was run.

Checking just now, there aren't any SQL marked as hot.

The SQL that get an incorrect hash value have so far all been system SQL

Here are two examples:

  sql len: 53
     sql#: 2516
   sql_id: g4y6nw3tts7cc
      sql: BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;
full_hash: c7d4ecb14863c535f278d4e0f39c1d8c
calc hash: 4feafe18f3031f89567263b189b174fe

  sql len: 45
     sql#: 2019
   sql_id: c7fnaqcmbm0b5
      sql: SELECT SUM(NUM_MAPPINGS+1) FROM smon_scn_time
full_hash: 14a86bc5696fafc4c3ba8ab326b98165
calc hash: 596c4eda631a007b1db10314f9641f97

Jared

On Mon, Dec 7, 2020 at 2:10 AM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> One thought off the top of my head.
>
> If you mark an SQL statement as "HOT" then it gets a new hash value (and
> full_hash_value) that is derived from the text plus the 'object property'
> which will be something like 'MARKHOTnn" - so there may be an additional
> feature to the call that makes it possible for a property to be included -
> and maybe your special cases have some other property that you're not
> allowing for in your tests.
>
> Couple of notes about it here:
> https://jonathanlewis.wordpress.com/2017/10/02/markhot/
>
>
> Regards
> Jonathan Lewis
>
>
>
> On Mon, 7 Dec 2020 at 01:41, 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