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@xxxxxxxxx> wrote:
Jared Still
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@xxxxxxxxx> 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@xxxxxxxxx> 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
--
