Re: SQL v$db_object_cache.full_hash_value

From: Jared Still <jkstill_at_gmail.com>
Date: Wed, 30 Dec 2020 14:47:17 -0800
Message-ID: <CAORjz=NSsXbWtpu=3cvER_VSZD=8Oia5zQ_aOE+1WQe6GCmGCg_at_mail.gmail.com>



So I took another look at this today.

If you don't already know, there are several available methods to calculate the full_hash_value and the SQL_ID from SQL statement text.

Carlos Sierra published one several years ago that calculates SQL_ID https://carlos-sierra.net/2013/09/12/function-to-compute-sql_id-out-of-sql_text/

Others have written about this at various times, such as Tanel Poder. Though I believe Tanel Poder was generating SQL_ID from the hash value.

I have probably read everything on this topic that has been written about it...

As a reminder, I first started looking at this as I need the full_hash_value to effectively manage SQL that is configured via dbms_shared_pool.markhot

The only place that value occurs is in v$db_object_cache.

So it doesn't help much when the SQL is not found in the cache.

Some ways to calculate full_hash_value
dbms_utility.get_sql_hash (requires ancillary code) dbms_translator.sql_hash

I think there is another that I do not recall right now.

All methods have one thing in common.

They sometimes fail to generate the correct value.

In my testing, that is between 1 and 1.5% of the time.

It doesn't matter which method I use, the same statements fail.

Whether generating SQL_ID or Full_Hash_Value, the same failures are seen

For instance, here is a sample of mismatches on SQL_ID



Mismatch
orig sql_id: 01rx4vtmhh35p
 gen sql_id: 6sx1r7tgh2tu2
sql: delete /* QOSH:PURGE_OSS */ /*+ dynamic_sampling(4) */ from sys.opt_sqlstat$ where last_gather_time < least(:1, sysdate - :2/86400)  and rownum <= :3
00000000 64 65 6C 65 74 65 20 2F 2A 20 51 4F 53 48 3A 50 |delete /* QOSH:P|
00000010 55 52 47 45 5F 4F 53 53 20 2A 2F 20 2F 2A 2B 20 |URGE_OSS */ /*+ |
00000020 64 79 6E 61 6D 69 63 5F 73 61 6D 70 6C 69 6E 67 |dynamic_sampling|
00000030 28 34 29 20 2A 2F 20 66 72 6F 6D 20 73 79 73 2E |(4) */ from sys.|
00000040 6F 70 74 5F 73 71 6C 73 74 61 74 24 20 20 77 68 |opt_sqlstat$  wh|
00000050 65 72 65 20 6C 61 73 74 5F 67 61 74 68 65 72 5F |ere last_gather_|
00000060 74 69 6D 65 20 3C 20 6C 65 61 73 74 28 3A 31 2C |time < least(:1,|
00000070 20 73 79 73 64 61 74 65 20 2D 20 3A 32 2F 38 36 | sysdate - :2/86|
00000080 34 30 30 29 20 20 61 6E 64 20 72 6F 77 6E 75 6D |400)  and rownum|
00000090 20 3C 3D 20 20 3A 33 20 20                      | <=  :3  |
================================================================================
Mismatch
orig sql_id: 0jqxg6f2fzpr3
 gen sql_id: 415ybmdqp39a2
sql: SELECT DECODE(USER, 'XS$NULL',
 XS_SYS_CONTEXT('XS$SESSION','USERNAME'), USER) FROM DUAL
00000000 53 45 4C 45 43 54 20 44 45 43 4F 44 45 28 55 53 |SELECT DECODE(US|
00000010 45 52 2C 20 27 58 53 24 4E 55 4C 4C 27 2C 20 20 |ER, 'XS$NULL',  |
00000020 58 53 5F 53 59 53 5F 43 4F 4E 54 45 58 54 28 27 |XS_SYS_CONTEXT('|
00000030 58 53 24 53 45 53 53 49 4F 4E 27 2C 27 55 53 45 |XS$SESSION','USE|
00000040 52 4E 41 4D 45 27 29 2C 20 55 53 45 52 29 20 46 |RNAME'), USER) F|
00000050 52 4F 4D 20 44 55 41 4C                         |ROM DUAL|
================================================================================

Here are mismatches based on full_hash_value



Mismatch
orig sql_id: 01rx4vtmhh35p
 gen sql_id: 6sx1r7tgh2tu2
sql: delete /* QOSH:PURGE_OSS */ /*+ dynamic_sampling(4) */ from sys.opt_sqlstat$ where last_gather_time < least(:1, sysdate - :2/86400)  and rownum <= :3
00000000 64 65 6C 65 74 65 20 2F 2A 20 51 4F 53 48 3A 50 |delete /* QOSH:P|
00000010 55 52 47 45 5F 4F 53 53 20 2A 2F 20 2F 2A 2B 20 |URGE_OSS */ /*+ |
00000020 64 79 6E 61 6D 69 63 5F 73 61 6D 70 6C 69 6E 67 |dynamic_sampling|
00000030 28 34 29 20 2A 2F 20 66 72 6F 6D 20 73 79 73 2E |(4) */ from sys.|
00000040 6F 70 74 5F 73 71 6C 73 74 61 74 24 20 20 77 68 |opt_sqlstat$  wh|
00000050 65 72 65 20 6C 61 73 74 5F 67 61 74 68 65 72 5F |ere last_gather_|
00000060 74 69 6D 65 20 3C 20 6C 65 61 73 74 28 3A 31 2C |time < least(:1,|
00000070 20 73 79 73 64 61 74 65 20 2D 20 3A 32 2F 38 36 | sysdate - :2/86|
00000080 34 30 30 29 20 20 61 6E 64 20 72 6F 77 6E 75 6D |400)  and rownum|
00000090 20 3C 3D 20 20 3A 33 20 20                      | <=  :3  |
================================================================================
Mismatch
orig sql_id: 0jqxg6f2fzpr3
 gen sql_id: 415ybmdqp39a2
sql: SELECT DECODE(USER, 'XS$NULL',
 XS_SYS_CONTEXT('XS$SESSION','USERNAME'), USER) FROM DUAL
00000000 53 45 4C 45 43 54 20 44 45 43 4F 44 45 28 55 53 |SELECT DECODE(US|
00000010 45 52 2C 20 27 58 53 24 4E 55 4C 4C 27 2C 20 20 |ER, 'XS$NULL',  |
00000020 58 53 5F 53 59 53 5F 43 4F 4E 54 45 58 54 28 27 |XS_SYS_CONTEXT('|
00000030 58 53 24 53 45 53 53 49 4F 4E 27 2C 27 55 53 45 |XS$SESSION','USE|
00000040 52 4E 41 4D 45 27 29 2C 20 55 53 45 52 29 20 46 |RNAME'), USER) F|
00000050 52 4F 4D 20 44 55 41 4C                         |ROM DUAL|
================================================================================

You may have noticed that the same SQL_ID appears in both.

The results are nearly the same:

full_hash_value: 31
sql_id :30

The sql_id set is a subset of the full_hash_value set

Should you care to try this on your own

https://github.com/jkstill/oracle-demos/blob/master/sql-hash-value/sql-gen-full-hash-demo.sql

Requires this package hexdump for dumping SQL https://github.com/jkstill/hexadecimal/tree/master/hexdump


https://github.com/jkstill/oracle-demos/blob/master/sql-hash-value/carlos-mismatch.sql This one requires creating Carlos Sierra's procedure

That Procedure is also in my repo for convenience. https://carlos-sierra.net/2013/09/12/function-to-compute-sql_id-out-of-sql_text/

Jared

On Mon, Dec 7, 2020 at 12:32 PM Jared Still <jkstill_at_gmail.com> wrote:

> Yes, I will dig into that a bit, and report back here if I find anything
> interesting.
>
> For the time being I want to finish the rest of this, and for now I can
> just ignore those with a mismatch.
>
> Most of what I want to work with will be in both v$sql and
> v$db_object_cache.
>
> However I may find some of interest in AWR, which is what this was to
> address.
>
> Of course, if they are periodically active, just scanning ASH for 'cursor:
> pin S wait on X' ( the problem de jour) a few times a day should be
> sufficient.
>
> But now I am just 'thinking out loud'.
>
>
>
> On Mon, Dec 7, 2020 at 12:16 PM Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>>
>> I'd notice something similar - it might be a coincidence, of course - but
>> I was cross-checking against v$sql and found the parsing user was always
>> SYS (which fits with SYS recursive, of course).
>>
>> Regards
>> Jonathan Lewis
>>
>>
>> On Mon, 7 Dec 2020 at 14:05, Jared Still <jkstill_at_gmail.com> wrote:
>>
>>> 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
>>> ==>> MISMATCH ==<<
>>> ============================================================
>>>
>>> ============================================================
>>> sql len: 45
>>> sql#: 2019
>>> sql_id: c7fnaqcmbm0b5
>>> sql: SELECT SUM(NUM_MAPPINGS+1) FROM smon_scn_time
>>> full_hash: 14a86bc5696fafc4c3ba8ab326b98165
>>> calc hash: 596c4eda631a007b1db10314f9641f97
>>> ==>> MISMATCH ==<<
>>> ============================================================
>>>
>>>
>>>

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Dec 30 2020 - 23:47:17 CET

Original text of this message