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@xxxxxxxxx> 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@xxxxxxxxx>
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@xxxxxxxxx> 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 ==<<
============================================================
