tag:blogger.com,1999:blog-3058240549611702067.post5927580525849202312..comments2022-10-19T15:00:26.288+06:00Comments on Oracle Notes by Mikhail: v$db_pipes: Unveiling the Truth of Oracle Hash ValueMikhail Velikikhhttp://www.blogger.com/profile/12809585068205118395noreply@blogger.comBlogger6125tag:blogger.com,1999:blog-3058240549611702067.post-7717554568544502272020-05-25T09:54:55.304+06:002020-05-25T09:54:55.304+06:00Yes, it's more suitable for pl/sql, and faster...Yes, it's more suitable for pl/sql, and faster in practice. I use result of this function, which is kglnahsh, not kglnahsv. kglnahsh is more intresting because of indexes on it. In order to discard possible duplicats caused by hashing we can use x$kglob accessed by index on column x$kglob.kglnahsh and it's column kglhadr to compare with x$kgllk.kgllkhdlGolubov Antonhttps://www.blogger.com/profile/09912656428402662842noreply@blogger.comtag:blogger.com,1999:blog-3058240549611702067.post-30578702356086113322020-05-19T03:10:13.347+06:002020-05-19T03:10:13.347+06:00Thanks for the tip. I was surprised to find that D...Thanks for the tip. I was surprised to find that DBMS_UTILITY.GET_SQL_HASH is a way more faster than DBMS_CRYPTO.HASH.<br />I see that the former is done through an ICD function, whereas the latter calls an external C library.<br />I am going to compare these functions using FlameGraphs to identify the root cause: https://github.com/brendangregg/FlameGraph<br /><br />DBMS_UTILITY.GET_SQL_HASH has OUT parameters, though, so that it requires a bit of work to use it in an SQL statement.<br />I would rather go for DBMS_SQL_TRANSLATOR.SQL_HASH if a pure SQL solution is required and performance is not a concern.<br /><br />DBMS_UTILITY.GET_SQL_HASH returns an MD5 hash in its OUT-parameter, indeed, which has to be modified in order to get KGLNAHSV:<br /><br />var hv varchar2(32)<br />var pre10ihash number<br />exec dbms_output.put_line(dbms_utility.get_sql_hash('select /* test_query*/* from dual'||chr(0), :hv, :pre10ihash))<br /><br />print hv<br /><br />select /* test_query*/* from dual;<br /><br />select kglnahsh, kglnahsv<br /> from x$kglob<br /> where kglnaobj='select /* test_query*/* from dual';<br /><br />DBMS_UTILITY.GET_SQL_HASH(HASH_VALUE): <br />5E80AD80D50E1C809454E8B352F7E43B<br /><br />KGLNAHSV: 80ad805e801c0ed5b3e854943be4f752<br />Mikhail Velikikhhttps://www.blogger.com/profile/12809585068205118395noreply@blogger.comtag:blogger.com,1999:blog-3058240549611702067.post-85588782003696623322020-05-19T02:46:49.958+06:002020-05-19T02:46:49.958+06:00Thanks for stopping by and providing the query.
I...Thanks for stopping by and providing the query.<br /><br />I would also add the following namespaces for 19c: 129, 137, 138, 141<br /><br />select kglhdnsp, kglhdnsd, <br /> count(*) total_objects,<br /> count(decode(ora_hash_value, kglnahsv, 1)) matched,<br /> count(decode(ora_hash_value, kglnahsv, null, 1)) non_matched,<br /> trunc(count(decode(ora_hash_value, kglnahsv, 1))/count(*)*100, 3) matched_pct<br /> from (select lower(rawtohex(utl_raw.concat(<br /> utl_raw.reverse(utl_raw.substr(hv, 1, 4)),<br /> utl_raw.reverse(utl_raw.substr(hv, 5, 4)),<br /> utl_raw.reverse(utl_raw.substr(hv, 9, 4)),<br /> utl_raw.reverse(utl_raw.substr(hv, 13, 4))<br /> ))) ora_hash_value,<br /> kglnahsv, kglnahsh, hex_hash, kglhdnsp, kglhdnsd, kglnacon, kglnaown, kglnaobj<br /> from (select dbms_crypto.hash(rawtohex(kglnaobj <br /> || case<br /> when not kglnaown is null <br /> then '.' || kglnaown<br /> end <br /> || case<br /> when not kglnacon is null <br /> then '.' || kglnacon<br /> end <br /> || chr(kglhdnsp) <br /> || chr(0)||chr(0)||chr(0)), 2) hv,<br /> kglnahsv,<br /> kglnahsh,<br /> to_char(kglnahsh, 'fm0xxxxxxx') hex_hash,<br /> kglhdnsp,<br /> kglhdnsd,<br /> kglnacon,<br /> kglnaown,<br /> kglnaobj<br /> from x$kglob t<br /> where kglhdnsp > 0)<br /> )<br /> group by kglhdnsp, kglhdnsd<br /> order by kglhdnsp;<br /><br />KGLHDNSP KGLHDNSD TOTAL MATCHED NON_MATCHED MATCHED_PCT<br />-------- ------------------------------ -------- -------- ----------- -----------<br /> 1 TABLE/PROCEDURE 1437 1431 6 99.582<br /> 2 BODY 51 51 0 100<br /> 3 TRIGGER 3 3 0 100<br /> 4 INDEX 155 155 0 100<br /> 5 CLUSTER 21 21 0 100<br /> 10 QUEUE 2 2 0 100<br /> 18 PUB SUB INTERNAL INFORMATION 3 3 0 100<br /> 23 RULESET 1 1 0 100<br /> 45 MULTI-VERSION OBJECT FOR TABLE 32 0 32 0<br /> 48 MULTI-VERSION OBJECT FOR INDEX 27 0 27 0<br /> 51 SCHEDULER GLOBAL ATTRIBUTE 4 4 0 100<br /> 64 EDITION 2 2 0 100<br /> 69 DBLINK 1 1 0 100<br /> 73 SCHEMA 7 7 0 100<br /> 74 DBINSTANCE 3 3 0 100<br /> 75 SQL AREA STATS 1097 0 1097 0<br /> 82 SQL AREA BUILD 421 421 0 100<br /> 93 AUDIT POLICY 4 4 0 100<br /> 103 OPTIMIZER FINDING 8 8 0 100<br /> 104 OPTIMIZER DIRECTIVE OWNER 48 48 0 100<br /> 129 FED APP 1 0 1 0<br /> 137 OPTIMIZER EXPRESSION HEADER 28 0 28 0<br /> 138 OPTIMIZER EXPRESSION OBJECT 3 0 3 0<br /> 141 RUNTIME STATISTICS 110 0 110 0<br /><br />Judging by my databases, all of those namespace 1 hash mismatches are due to multitenant features: X$CDBVW$/X$OBLNK$ - the containers clause and object links correspondingly.<br />Mikhail Velikikhhttps://www.blogger.com/profile/12809585068205118395noreply@blogger.comtag:blogger.com,1999:blog-3058240549611702067.post-84701211734013006872020-05-18T09:03:29.851+06:002020-05-18T09:03:29.851+06:00Also, there is a function dbms_utility.get_sql_has...Also, there is a function dbms_utility.get_sql_hash, which works faster than dbms_crypto.hash and return last 4 byte in reverse order itself.Golubov Antonhttps://www.blogger.com/profile/09912656428402662842noreply@blogger.comtag:blogger.com,1999:blog-3058240549611702067.post-27050892271837024002020-05-18T00:48:44.778+06:002020-05-18T00:48:44.778+06:00It's slightly augmented version. It allows to ...It's slightly augmented version. It allows to get hash for almost all namespaces in CDB and not in CDB. Now it's possible for fast check for using or executing pl/sql procedures by user sessions using x$kgllk.<br /><br />select lower(rawtohex(utl_raw.concat(<br /> utl_raw.reverse(utl_raw.substr(hv, 1, 4)),<br /> utl_raw.reverse(utl_raw.substr(hv, 5, 4)),<br /> utl_raw.reverse(utl_raw.substr(hv, 9, 4)),<br /> utl_raw.reverse(utl_raw.substr(hv, 13, 4))<br /> ))) ora_hash_value,<br /> kglnahsv, kglnahsh, hex_hash, kglhdnsp, kglhdnsd, kglnacon, kglnaown, kglnaobj<br /> from (select dbms_crypto.hash(rawtohex(kglnaobj || case<br /> when not kglnaown is null then '.' || kglnaown<br /> end || <br /> case<br /> when not kglnacon is null then '.' || kglnacon<br /> end ||<br /> chr(kglhdnsp) || chr(0)||chr(0)||chr(0)), 2) hv,<br /> kglnahsv, kglnahsh, to_char(kglnahsh, 'fm0xxxxxxx') hex_hash,<br /> kglhdnsp, kglhdnsd, kglnacon, kglnaown, kglnaobj<br /> from x$kglob t<br /> where kglhdnsp > 0<br /> and not kglhdnsp in (45, 48, 75, 113))Golubov Antonhttps://www.blogger.com/profile/09912656428402662842noreply@blogger.comtag:blogger.com,1999:blog-3058240549611702067.post-64195885168631380642020-03-27T15:56:33.473+06:002020-03-27T15:56:33.473+06:00Excellent investigation, Mihail!Excellent investigation, Mihail!Igor Usoltsevhttps://www.blogger.com/profile/15303587951058938350noreply@blogger.com