So here is the query that was run and its plan:
SQL> explain plan for
2 select count(*)
3 from v$db_pipes
4 where name = 'MY_PIPE';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2656999297
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 148 | 1 (100)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 148 | | |
|* 2 | FIXED TABLE FULL| X$KGLOB | 1 | 148 | 1 (100)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------
2 - filter("KGLNAOBJ"='MY_PIPE' AND "KGLHDNSP"=7 AND "KGLOBSTA"<>0
AND "INST_ID"=USERENV('INSTANCE'))
It is seen that v$db_pipes is built on top of x$kglob, which is a fixed table.Obviously, doing a FULL TABLE SCAN against it is not the best thing to do, especially when we work with a large enough shared pool.
Fixed tables can have Oracle provided indexes and x$kglob has the following ones:
SQL> select index_number, column_name from v$indexed_fixed_column where table_name='X$KGLOB';
INDEX_NUMBER COLUMN_NAME
------------ ------------
1 KGLNAHSH
2 KGLOBT03
KGLOBT03 is not populated for pipes and it stores the SQL_ID of a statement being executed.KGLNAHSH is a hash value and it seems to store last 4 bytes of KGLNAHSV which is a full hash value:
SQL> select kglnaobj, kglnahsv, kglnahsh, to_char(kglnahsh, 'fm0xxxxxxx') hex_hash, kglhdnsp from x$kglob where kglnaobj in ('MY_PIPE', 'MY_PIPE1', 'MY_PIPE2');
KGLNAOBJ KGLNAHSV KGLNAHSH HEX_HASH KGLHDNSP
------------------------------ -------------------------------- ---------- --------- ----------
MY_PIPE2 53e58fa645a35847070108600b3043ce 187712462 0b3043ce 7
MY_PIPE1 1bb0749b381c19f0dd4d47413a125cc1 974281921 3a125cc1 7
MY_PIPE cdff652a7449f169da313e5685b962d8 2243519192 85b962d8 7
If I knew a way to calculate the hash value upfront, then I could query x$kglob passing the hash value as a filter to get an indexed access path:SQL> explain plan for
2 select count(*)
3 from x$kglob
4 where kglnaobj = 'MY_PIPE'
5 and kglhdnsp = 7
6 and kglobsta <> 0
7 and inst_id = userenv('instance')
8 and kglnahsh = 2243519192;
Explained.
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2109255596
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 155 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 155 | | |
|* 2 | FIXED TABLE FIXED INDEX| X$KGLOB (ind:1) | 1 | 155 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
2 - filter("KGLNAHSH"=2243519192 AND "KGLNAOBJ"='MY_PIPE' AND "KGLHDNSP"=7 AND
"KGLOBSTA"<>0 AND "INST_ID"=USERENV('INSTANCE'))
I decided to try to find out how the hash value is calculated.Firstly, I took the library cache dump:
Bucket: #=90840 Mutex=0x6cb2aa60(283467841536, 2, 0, 6)
LibraryHandle: Address=0x654cc068 Hash=85b962d8 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=CDB$ROOT.MY_PIPE
FullHashValue=cdff652a7449f169da313e5685b962d8 Namespace=PIPE(07) Type=PIPE(18) ContainerId=1 ContainerUid=1 Identifier=0 OwnerIdn=2147483644
Statistics: InvalidationCount=0 ExecutionCount=0 LoadCount=1 ActiveLocks=0 TotalLockCount=1 TotalPinCount=1
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
Concurrency: DependencyMutex=0x654cc118(0, 0, 0, 0) Mutex=0x654cc1b8(66, 8, 0, 6)
Flags=RON/PN0/[10012001] Flags2=[0000]
WaitersLists:
Lock=0x654cc0f8[0x654cc0f8,0x654cc0f8]
Pin=0x654cc0d8[0x654cc0d8,0x654cc0d8]
LoadLock=0x654cc150[0x654cc150,0x654cc150]
Timestamp:
LibraryObject: Address=0x65f9cb98 HeapMask=0000-0001-0001-0000 Flags=EXS/NRC[0400] Flags2=[0000] Flags3=[0000] PublicFlags=[0000]
Based on my knowledge of Oracle internals, I supposed that the hash value should be somehow derived from the name of the object and possibly other attributes of it.Unfortunately, I was not able to find a proper formula looking into the library cache dump (I was close, in fact, as I was combined the object name with the namespace, however, a proper formula is hard or even near to impossible to come by).
When my first attempts to solve this conundrum failed miserably, I took a break and returned to that task later on.
That time I took more systematic approach and started by unwrapping the DBMS_PIPE package. I found out that the DBMS_PIPE.CREATE_PIPE function calls the CREATEPIPE function that has a widely used INTERFACE pragma, which roughly means that the function is mapped to another C function within the Oracle kernel. The function to which CREATEPIPE should be mapped should probably be called kkxpcre based on the excellent Dennis Yurichev blog post (of course, Dennis' post was written a while ago but Oracle has not given much attention to pipes for a while and I seriously doubt that they have changed that mapping since then). All of this knowledge appears to be useless at a glance, however, it laid the groundwork for the next step.
The next step of my research started when I was using the DebugTrace program from Intel Pintools. I ran DebugTrace against the server process in which I created a new pipe named MY_PIPE that has the hash value 2243519192 which is 0x85b962d8 in hex.
Here is the relevant output of DebugTrace which I reformatted for brevity (this is the output that I obtained from Oracle 19c, I also got a 12.2 output initially that had a few minor differencies):
Call 0x000000000f7775db $ORACLE_HOME/bin/oracle:kkxpcre+0x0000000002db -> 0x000000000b032100 $ORACLE_HOME/bin/oracle:kkxpcrep(0x7f0fd3e6fe80, 0x7, ...) | Call 0x000000000b03225b $ORACLE_HOME/bin/oracle:kkxpcrep+0x00000000015b -> 0x000000001280c720 $ORACLE_HOME/bin/oracle:kglget(0x7f0fd3f889a0, 0x7fff3aea4d50, ...) | | Call 0x000000001280c844 $ORACLE_HOME/bin/oracle:kglget+0x000000000124 -> 0x00000000128119f0 $ORACLE_HOME/bin/oracle:kglLock(0x7f0fd3f889a0, 0x7fff3aea4d50, ...) | | | Call 0x0000000012811b4b $ORACLE_HOME/bin/oracle:kglLock+0x00000000015b -> 0x0000000012815280 $ORACLE_HOME/bin/oracle:kglComputeHash(0x7f0fd3f889a0, 0x7fff3aea4dd0, ...) | | | Tailcall 0x000000001281528d $ORACLE_HOME/bin/oracle:kglComputeHash+0x00000000000d -> 0x00000000128152b0 $ORACLE_HOME/bin/oracle:kglComputeHash0(0x7f0fd3f889a0, 0x7fff3aea4dd0, ...) | | | | Call 0x0000000012815399 $ORACLE_HOME/bin/oracle:kglComputeHash0+0x0000000000e9 -> 0x00000000127dca20 $ORACLE_HOME/bin/oracle:kggmd5Update(0x7fff3aea4580, 0x7f0fd3e6fe80, ...) | | | | | Call 0x00000000127dcad5 $ORACLE_HOME/bin/oracle:kggmd5Update+0x0000000000b5 -> 0x0000000006b74060 $ORACLE_HOME/bin/oracle:_intel_fast_memcpy(0x7fff3aea4588, 0x7f0fd3e6fe80, ...) | | | | | Tailcall 0x0000000006b740bc $ORACLE_HOME/bin/oracle:_intel_fast_memcpy+0x00000000005c -> 0x0000000006b74030 $ORACLE_HOME/bin/oracle:_intel_fast_memcpy.P(0x7fff3aea4588, 0x7f0fd3e6fe80, ...) | | | | | Tailcall 0x0000000006b74030 $ORACLE_HOME/bin/oracle:_intel_fast_memcpy.P -> 0x0000000006b814e0 $ORACLE_HOME/bin/oracle:__intel_ssse3_rep_memcpy(0x7fff3aea4588, 0x7f0fd3e6fe80, ...) | | | | | Return 0x0000000006b83cfa $ORACLE_HOME/bin/oracle:__intel_ssse3_rep_memcpy+0x00000000281a returns: 0x7fff3aea4588 | | | | Return 0x00000000127dcae8 $ORACLE_HOME/bin/oracle:kggmd5Update+0x0000000000c8 returns: 0x7fff3aea4588 | | | | Call 0x000000001281545e $ORACLE_HOME/bin/oracle:kglComputeHash0+0x0000000001ae -> 0x00000000127dca20 $ORACLE_HOME/bin/oracle:kggmd5Update(0x7fff3aea4580, 0x12976914, ...) | | | | | Call 0x00000000127dcad5 $ORACLE_HOME/bin/oracle:kggmd5Update+0x0000000000b5 -> 0x0000000006b74060 $ORACLE_HOME/bin/oracle:_intel_fast_memcpy(0x7fff3aea458f, 0x12976914, ...) | | | | | Tailcall 0x0000000006b740bc $ORACLE_HOME/bin/oracle:_intel_fast_memcpy+0x00000000005c -> 0x0000000006b74030 $ORACLE_HOME/bin/oracle:_intel_fast_memcpy.P(0x7fff3aea458f, 0x12976914, ...) | | | | | Tailcall 0x0000000006b74030 $ORACLE_HOME/bin/oracle:_intel_fast_memcpy.P -> 0x0000000006b814e0 $ORACLE_HOME/bin/oracle:__intel_ssse3_rep_memcpy(0x7fff3aea458f, 0x12976914, ...) | | | | | Return 0x0000000006b83f95 $ORACLE_HOME/bin/oracle:__intel_ssse3_rep_memcpy+0x000000002ab5 returns: 0x7fff3aea458f | | | | Return 0x00000000127dcae8 $ORACLE_HOME/bin/oracle:kggmd5Update+0x0000000000c8 returns: 0x7fff3aea458f | | | | Call 0x000000001281546f $ORACLE_HOME/bin/oracle:kglComputeHash0+0x0000000001bf -> 0x00000000127dca20 $ORACLE_HOME/bin/oracle:kggmd5Update(0x7fff3aea4580, 0x7580764c, ...) | | | | | Call 0x00000000127dcad5 $ORACLE_HOME/bin/oracle:kggmd5Update+0x0000000000b5 -> 0x0000000006b74060 $ORACLE_HOME/bin/oracle:_intel_fast_memcpy(0x7fff3aea4590, 0x7580764c, ...) | | | | | Tailcall 0x0000000006b740bc $ORACLE_HOME/bin/oracle:_intel_fast_memcpy+0x00000000005c -> 0x0000000006b74030 $ORACLE_HOME/bin/oracle:_intel_fast_memcpy.P(0x7fff3aea4590, 0x7580764c, ...) | | | | | Tailcall 0x0000000006b74030 $ORACLE_HOME/bin/oracle:_intel_fast_memcpy.P -> 0x0000000006b814e0 $ORACLE_HOME/bin/oracle:__intel_ssse3_rep_memcpy(0x7fff3aea4590, 0x7580764c, ...) | | | | | Return 0x0000000006b83c86 $ORACLE_HOME/bin/oracle:__intel_ssse3_rep_memcpy+0x0000000027a6 returns: 0x7fff3aea4590 | | | | Return 0x00000000127dcae8 $ORACLE_HOME/bin/oracle:kggmd5Update+0x0000000000c8 returns: 0x7fff3aea4590 | | | | Call 0x0000000012815415 $ORACLE_HOME/bin/oracle:kglComputeHash0+0x000000000165 -> 0x00000000127dca20 $ORACLE_HOME/bin/oracle:kggmd5Update(0x7fff3aea4580, 0x7fff3aea45f0, ...) | | | | | Call 0x00000000127dcad5 $ORACLE_HOME/bin/oracle:kggmd5Update+0x0000000000b5 -> 0x0000000006b74060 $ORACLE_HOME/bin/oracle:_intel_fast_memcpy(0x7fff3aea4598, 0x7fff3aea45f0, ...) | | | | | Tailcall 0x0000000006b740bc $ORACLE_HOME/bin/oracle:_intel_fast_memcpy+0x00000000005c -> 0x0000000006b74030 $ORACLE_HOME/bin/oracle:_intel_fast_memcpy.P(0x7fff3aea4598, 0x7fff3aea45f0, ...) | | | | | Tailcall 0x0000000006b74030 $ORACLE_HOME/bin/oracle:_intel_fast_memcpy.P -> 0x0000000006b814e0 $ORACLE_HOME/bin/oracle:__intel_ssse3_rep_memcpy(0x7fff3aea4598, 0x7fff3aea45f0, ...) | | | | | Return 0x0000000006b83726 $ORACLE_HOME/bin/oracle:__intel_ssse3_rep_memcpy+0x000000002246 returns: 0x7fff3aea4598 | | | | Return 0x00000000127dcae8 $ORACLE_HOME/bin/oracle:kggmd5Update+0x0000000000c8 returns: 0x7fff3aea4598 | | | | Call 0x0000000012815423 $ORACLE_HOME/bin/oracle:kglComputeHash0+0x000000000173 -> 0x00000000127dc8b0 $ORACLE_HOME/bin/oracle:kggmd5Finish(0x7fff3aea4580, 0, ...) | | | | | Call 0x00000000127dc927 $ORACLE_HOME/bin/oracle:kggmd5Finish+0x000000000077 -> 0x00000000127dca20 $ORACLE_HOME/bin/oracle:kggmd5Update(0x7fff3aea4580, 0x14aa6460, ...) | | | | | | Call 0x00000000127dcad5 $ORACLE_HOME/bin/oracle:kggmd5Update+0x0000000000b5 -> 0x0000000006b74060 $ORACLE_HOME/bin/oracle:_intel_fast_memcpy(0x7fff3aea459c, 0x14aa6460, ...) | | | | | | Tailcall 0x0000000006b740bc $ORACLE_HOME/bin/oracle:_intel_fast_memcpy+0x00000000005c -> 0x0000000006b74030 $ORACLE_HOME/bin/oracle:_intel_fast_memcpy.P(0x7fff3aea459c, 0x14aa6460, ...) | | | | | | Tailcall 0x0000000006b74030 $ORACLE_HOME/bin/oracle:_intel_fast_memcpy.P -> 0x0000000006b814e0 $ORACLE_HOME/bin/oracle:__intel_ssse3_rep_memcpy(0x7fff3aea459c, 0x14aa6460, ...) | | | | | | Return 0x0000000006b83e38 $ORACLE_HOME/bin/oracle:__intel_ssse3_rep_memcpy+0x000000002958 returns: 0x7fff3aea459c | | | | | Return 0x00000000127dcae8 $ORACLE_HOME/bin/oracle:kggmd5Update+0x0000000000c8 returns: 0x7fff3aea459c | | | | | Call 0x00000000127dc938 $ORACLE_HOME/bin/oracle:kggmd5Finish+0x000000000088 -> 0x00000000127dca20 $ORACLE_HOME/bin/oracle:kggmd5Update(0x7fff3aea4580, 0x7fff3aea4550, ...) | | | | | | Call 0x00000000127dcaa4 $ORACLE_HOME/bin/oracle:kggmd5Update+0x000000000084 -> 0x0000000006b74060 $ORACLE_HOME/bin/oracle:_intel_fast_memcpy(0x7fff3aea45c0, 0x7fff3aea4550, ...) | | | | | | Tailcall 0x0000000006b740bc $ORACLE_HOME/bin/oracle:_intel_fast_memcpy+0x00000000005c -> 0x0000000006b74030 $ORACLE_HOME/bin/oracle:_intel_fast_memcpy.P(0x7fff3aea45c0, 0x7fff3aea4550, ...) | | | | | | Tailcall 0x0000000006b74030 $ORACLE_HOME/bin/oracle:_intel_fast_memcpy.P -> 0x0000000006b814e0 $ORACLE_HOME/bin/oracle:__intel_ssse3_rep_memcpy(0x7fff3aea45c0, 0x7fff3aea4550, ...) | | | | | | Return 0x0000000006b83c86 $ORACLE_HOME/bin/oracle:__intel_ssse3_rep_memcpy+0x0000000027a6 returns: 0x7fff3aea45c0 | | | | | | Call 0x00000000127dcab1 $ORACLE_HOME/bin/oracle:kggmd5Update+0x000000000091 -> 0x00000000127dcba0 $ORACLE_HOME/bin/oracle:kggmd5Process(0x7fff3aea4a70, 0x7fff3aea4588, ...) | | | | | | Return 0x00000000127dd68d $ORACLE_HOME/bin/oracle:kggmd5Process+0x000000000aed returns: 0x66ba4229 | | | | | Return 0x00000000127dcae8 $ORACLE_HOME/bin/oracle:kggmd5Update+0x0000000000c8 returns: 0x66ba4229 | | | | Return 0x00000000127dc94a $ORACLE_HOME/bin/oracle:kggmd5Finish+0x00000000009a returns: 0x66ba4229 | | | Return 0x000000001281543b $ORACLE_HOME/bin/oracle:kglComputeHash0+0x00000000018b returns: 0x85b962d8There are several important observations that can be made about this output:
- kkxpcre is a C function mapped to dbms_pipe.createpipe through the INTERFACE pragma which is called by dbms_pipe.create_pipe, which is the only function exposed to the public
- kglComputeHash0 returns that hash value 0x85b962d8 that I am looking for, hence I need to look carefully at what is going on between kkxpcre and kglComputeHash0 as the hash value is somewhere between
- The call stack is: kkxpcre->kkxpcrep->kglget->kglLock->kglComputeHash->kglComputeHash0, the latter calls kggmdUpdate multiple times, then it calls kggmd5Finish, which calls kggmd5Update twice. The last call to kggmd5Update also invokes kggmd5Process. In terms of Oracle code layers, the code can be split in the following ones:
KKX(Programmatic interfaces to/from PL/SQL)->KGL(Library Cache)->KGG(Generic Routines) - Based on the fact that we call MD5 functions, the underlying alrorithm is used MD5 somehow.
0x0000000012815365 <+181>: mov %rax,-0x58(%rbp) 0x0000000012815369 <+185>: movl $0x67452301,(%rax) 0x000000001281536f <+191>: mov -0x58(%rbp),%rcx 0x0000000012815373 <+195>: movl $0xefcdab89,0x4(%rcx) 0x000000001281537a <+202>: mov -0x58(%rbp),%r8 0x000000001281537e <+206>: movl $0x98badcfe,0x8(%r8) 0x0000000012815386 <+214>: mov -0x58(%rbp),%r9 0x000000001281538a <+218>: movl $0x10325476,0xc(%r9) 0x0000000012815392 <+226>: mov 0x10(%r14),%rsi 0x0000000012815396 <+230>: mov (%r14),%edx 0x0000000012815399 <+233>: callq 0x127dca20 <kggmd5Update>All of those constants: 0x67452301, 0xefcdab89, 0x98badcfe, 0x10325476 - are magic initialization constants in the MD5 algorithm.
Since the algorithm is known, in order to calculate a hash value, I need to know the input values to the MD5 functions.
Thus, I setup a few breakpoints in GDB and spent a while reviewing the register values.
Finally, I came up with the following GDB breakpoints:
set pagination off
break kggmd5Update
commands
printf "Length: %d\n",$rdx
x/8xc $rsi
c
end
break kglComputeHash
commands
c
end
break kggmd5Process
commands
c
end
break kggmd5Finish
commands
c
end
I attached to the server process through GDB and ran the following command in SQL*Plus:SQL> var n number
SQL> exec :n:=dbms_pipe.create_pipe('MY_PIPE')
The debugger output showed this (comments are inline):Breakpoint 5, 0x0000000012815280 in kglComputeHash () Breakpoint 4, 0x00000000127dca20 in kggmd5Update () Length: 7 0x7f7b9304ffe0: 77 'M' 89 'Y' 95 '_' 80 'P' 73 'I' 80 'P' 69 'E' 0 '\000' -- MY_PIPE Breakpoint 4, 0x00000000127dca20 in kggmd5Update () Length: 1 0x12976914: 46 '.' 0 '\000' 0 '\000' 0 '\000' 113 'q' 109 'm' 120 'x' 113 'q' -- "." (just a dot) Breakpoint 4, 0x00000000127dca20 in kggmd5Update () Length: 8 0x7580c61c: 67 'C' 68 'D' 66 'B' 36 '$' 82 'R' 79 'O' 79 'O' 84 'T' -- CDB$ROOT Breakpoint 4, 0x00000000127dca20 in kggmd5Update () Length: 4 0x7ffd3203e070: 7 '\a' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' -- 0x07000000 or chr(7)||chr(0)||chr(0)||chr(0) Breakpoint 7, 0x00000000127dc8b0 in kggmd5Finish () Breakpoint 4, 0x00000000127dca20 in kggmd5Update () Length: 36 0x14aa6460 <kggmd5padding>: -128 '\200' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' Breakpoint 4, 0x00000000127dca20 in kggmd5Update () Length: 8 0x7ffd3203dfd0: -96 '\240' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' 0 '\000' Breakpoint 6, 0x00000000127dcba0 in kggmd5Process ()Let me explain what this output means. In accordance with: System V ABI AMD64, the function parameters passed as follows (page 21):
- %rdi - first argument to functions
- %rsi - second argument to functions
- %rdx - third argument to functions
/* MD5 block update operation. Continues an MD5 message-digest operation, processing another message block, and updating the context. */ void MD5Update (context, input, inputLen) MD5_CTX *context; /* context */ unsigned char *input; /* input block */ unsigned int inputLen; /* length of input block */As such, I was not really interested in the context variable - it holds the address of the context structure.
The second and third parameters, though, should hold the input string and its length, that is what I was getting in my GDB breakpoint (remember, that rsi and rdx are second and third parameters correspondingly):
break kggmd5Update
commands
printf "Length: %d\n",$rdx
x/8xc $rsi
c
end
First three calls in the GDB output are self-explanatory: it is a string 'MY_PIPE.CDB$ROOT'. The last call requires a bit of explanation, though: what is 0x07000000? It seems to be the namespace, which is 7 for pipes (X$KGLOB.KGLHDNSP), with 3 trailing zero bytes.I calculated MD5 hashes based on that formula and got this:
SQL> select dbms_crypto.hash(rawtohex('MY_PIPE1.CDB$ROOT'||chr(7)||chr(0)||chr(0)||chr(0)), 2) md5 from dual;
MD5
--------------------------------
9B74B01BF0191C3841474DDDC15C123A
SQL> select dbms_crypto.hash(rawtohex('MY_PIPE2.CDB$ROOT'||chr(7)||chr(0)||chr(0)||chr(0)), 2) md5 from dual;
MD5
--------------------------------
A68FE5534758A34560080107CE43300B
SQL> select dbms_crypto.hash(rawtohex('MY_PIPE.CDB$ROOT'||chr(7)||chr(0)||chr(0)||chr(0)), 2) md5 from dual;
MD5
--------------------------------
2A65FFCD69F14974563E31DAD862B985
Whereas the actual hashes were these:SQL> select kglnaobj, kglnahsv, kglnahsh, to_char(kglnahsh, 'fm0xxxxxxx') hex_hash, kglhdnsp from x$kglob where kglnaobj in ('MY_PIPE', 'MY_PIPE1', 'MY_PIPE2');
KGLNAOBJ KGLNAHSV KGLNAHSH HEX_HASH KGLHDNSP
------------------------------ -------------------------------- ---------- --------- ----------
MY_PIPE2 53e58fa645a35847070108600b3043ce 187712462 0b3043ce 7
MY_PIPE1 1bb0749b381c19f0dd4d47413a125cc1 974281921 3a125cc1 7
MY_PIPE cdff652a7449f169da313e5685b962d8 2243519192 85b962d8 7
Then it dawned on me that Oracle uses the same technique for these hashes as it uses for SQL_ID: Function to compute SQL_ID out of SQL_TEXT - it reverses the order of each 4 bytes. Look:SQL> with pipes(pipe_name) as ( 2 select 'MY_PIPE' from dual union all 3 select 'MY_PIPE1' from dual union all 4 select 'MY_PIPE2' from dual 5 ) 6 select pipe_name, 7 utl_raw.concat( 8 utl_raw.reverse(utl_raw.substr(hv, 1, 4)), 9 utl_raw.reverse(utl_raw.substr(hv, 5, 4)), 10 utl_raw.reverse(utl_raw.substr(hv, 9, 4)), 11 utl_raw.reverse(utl_raw.substr(hv, 13, 4)) 12 ) ora_hash_value 13 from (select pipe_name, 14 dbms_crypto.hash(rawtohex(pipe_name||'.CDB$ROOT'||chr(7)||chr(0)||chr(0)||chr(0)), 2) hv 15 from pipes) 16 order by pipe_name; PIPE_NAM ORA_HASH_VALUE -------- -------------------------------- MY_PIPE CDFF652A7449F169DA313E5685B962D8 MY_PIPE1 1BB0749B381C19F0DD4D47413A125CC1 MY_PIPE2 53E58FA645A35847070108600B3043CEThese are exactly the values I was looking for!
tl;dr
- The pipe full hash value can be calculated as below (where ORA_HASH_VALUE is the hash that I calculated based on the research made in this article):
SQL> select lower(rawtohex(utl_raw.concat( 2 utl_raw.reverse(utl_raw.substr(hv, 1, 4)), 3 utl_raw.reverse(utl_raw.substr(hv, 5, 4)), 4 utl_raw.reverse(utl_raw.substr(hv, 9, 4)), 5 utl_raw.reverse(utl_raw.substr(hv, 13, 4)) 6 ))) ora_hash_value, 7 kglnahsv, kglnahsh, hex_hash, kglhdnsp, kglhdnsd, kglnacon, kglnaown, kglnaobj 8 from (select dbms_crypto.hash(rawtohex(kglnaobj||'.'||kglnacon||chr(kglhdnsp)||chr(0)||chr(0)||chr(0)), 2) hv, 9 kglnahsv, kglnahsh, to_char(kglnahsh, 'fm0xxxxxxx') hex_hash, 10 kglhdnsp, kglhdnsd, kglnacon, kglnaown, kglnaobj 11 from x$kglob t 12 where kglhdnsp = 7) 13 order by kglnaobj 14 / ORA_HASH_VALUE KGLNAHSV KGLNAHSH HEX_HASH KGLHDNSP KGLHDNSD KGLNACON KGLNAOWN KGLNAOBJ -------------------------------- -------------------------------- ---------- --------- ---------- -------- -------- -------- -------- cdff652a7449f169da313e5685b962d8 cdff652a7449f169da313e5685b962d8 2243519192 85b962d8 7 PIPE CDB$ROOT MY_PIPE 1bb0749b381c19f0dd4d47413a125cc1 1bb0749b381c19f0dd4d47413a125cc1 974281921 3a125cc1 7 PIPE CDB$ROOT MY_PIPE1 53e58fa645a35847070108600b3043ce 53e58fa645a35847070108600b3043ce 187712462 0b3043ce 7 PIPE CDB$ROOT MY_PIPE2
1. We take: "<PIPE_NAME>.<CONTAINER_NAME>chr(7)chr(0)chr(0)chr(0)".
2. Compute an MD5 hash of it.
3. Then the order of each 4 bytes is reversed and the final output is assembled.
4. If you are interested in the numeric hash value, which is X$KGLOB.KGLNAHSH, you need to take last 4 bytes of the full hash value, which is X$KGLOB.KGLNAHSV.
5. I do not consider the non-CDB architecture due to its deprecation but the general pattern should be applicable to it as well.
- Due to the fact that v$db_pipes does not expose the hash value, it's a good idea to create an extended version of that view including the hash value, since we now know how to calculate it (legal disclaimer: that's just my opinion). After all, Oracle has provided functions to calculate the SQL_ID. They might provide similar functions to calculate hash values one day.
Excellent investigation, Mihail!
ОтветитьУдалить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.
ОтветитьУдалитьselect lower(rawtohex(utl_raw.concat(
utl_raw.reverse(utl_raw.substr(hv, 1, 4)),
utl_raw.reverse(utl_raw.substr(hv, 5, 4)),
utl_raw.reverse(utl_raw.substr(hv, 9, 4)),
utl_raw.reverse(utl_raw.substr(hv, 13, 4))
))) ora_hash_value,
kglnahsv, kglnahsh, hex_hash, kglhdnsp, kglhdnsd, kglnacon, kglnaown, kglnaobj
from (select dbms_crypto.hash(rawtohex(kglnaobj || case
when not kglnaown is null then '.' || kglnaown
end ||
case
when not kglnacon is null then '.' || kglnacon
end ||
chr(kglhdnsp) || chr(0)||chr(0)||chr(0)), 2) hv,
kglnahsv, kglnahsh, to_char(kglnahsh, 'fm0xxxxxxx') hex_hash,
kglhdnsp, kglhdnsd, kglnacon, kglnaown, kglnaobj
from x$kglob t
where kglhdnsp > 0
and not kglhdnsp in (45, 48, 75, 113))
Thanks for stopping by and providing the query.
УдалитьI would also add the following namespaces for 19c: 129, 137, 138, 141
select kglhdnsp, kglhdnsd,
count(*) total_objects,
count(decode(ora_hash_value, kglnahsv, 1)) matched,
count(decode(ora_hash_value, kglnahsv, null, 1)) non_matched,
trunc(count(decode(ora_hash_value, kglnahsv, 1))/count(*)*100, 3) matched_pct
from (select lower(rawtohex(utl_raw.concat(
utl_raw.reverse(utl_raw.substr(hv, 1, 4)),
utl_raw.reverse(utl_raw.substr(hv, 5, 4)),
utl_raw.reverse(utl_raw.substr(hv, 9, 4)),
utl_raw.reverse(utl_raw.substr(hv, 13, 4))
))) ora_hash_value,
kglnahsv, kglnahsh, hex_hash, kglhdnsp, kglhdnsd, kglnacon, kglnaown, kglnaobj
from (select dbms_crypto.hash(rawtohex(kglnaobj
|| case
when not kglnaown is null
then '.' || kglnaown
end
|| case
when not kglnacon is null
then '.' || kglnacon
end
|| chr(kglhdnsp)
|| chr(0)||chr(0)||chr(0)), 2) hv,
kglnahsv,
kglnahsh,
to_char(kglnahsh, 'fm0xxxxxxx') hex_hash,
kglhdnsp,
kglhdnsd,
kglnacon,
kglnaown,
kglnaobj
from x$kglob t
where kglhdnsp > 0)
)
group by kglhdnsp, kglhdnsd
order by kglhdnsp;
KGLHDNSP KGLHDNSD TOTAL MATCHED NON_MATCHED MATCHED_PCT
-------- ------------------------------ -------- -------- ----------- -----------
1 TABLE/PROCEDURE 1437 1431 6 99.582
2 BODY 51 51 0 100
3 TRIGGER 3 3 0 100
4 INDEX 155 155 0 100
5 CLUSTER 21 21 0 100
10 QUEUE 2 2 0 100
18 PUB SUB INTERNAL INFORMATION 3 3 0 100
23 RULESET 1 1 0 100
45 MULTI-VERSION OBJECT FOR TABLE 32 0 32 0
48 MULTI-VERSION OBJECT FOR INDEX 27 0 27 0
51 SCHEDULER GLOBAL ATTRIBUTE 4 4 0 100
64 EDITION 2 2 0 100
69 DBLINK 1 1 0 100
73 SCHEMA 7 7 0 100
74 DBINSTANCE 3 3 0 100
75 SQL AREA STATS 1097 0 1097 0
82 SQL AREA BUILD 421 421 0 100
93 AUDIT POLICY 4 4 0 100
103 OPTIMIZER FINDING 8 8 0 100
104 OPTIMIZER DIRECTIVE OWNER 48 48 0 100
129 FED APP 1 0 1 0
137 OPTIMIZER EXPRESSION HEADER 28 0 28 0
138 OPTIMIZER EXPRESSION OBJECT 3 0 3 0
141 RUNTIME STATISTICS 110 0 110 0
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.
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.
ОтветитьУдалитьThanks for the tip. I was surprised to find that DBMS_UTILITY.GET_SQL_HASH is a way more faster than DBMS_CRYPTO.HASH.
УдалитьI see that the former is done through an ICD function, whereas the latter calls an external C library.
I am going to compare these functions using FlameGraphs to identify the root cause: https://github.com/brendangregg/FlameGraph
DBMS_UTILITY.GET_SQL_HASH has OUT parameters, though, so that it requires a bit of work to use it in an SQL statement.
I would rather go for DBMS_SQL_TRANSLATOR.SQL_HASH if a pure SQL solution is required and performance is not a concern.
DBMS_UTILITY.GET_SQL_HASH returns an MD5 hash in its OUT-parameter, indeed, which has to be modified in order to get KGLNAHSV:
var hv varchar2(32)
var pre10ihash number
exec dbms_output.put_line(dbms_utility.get_sql_hash('select /* test_query*/* from dual'||chr(0), :hv, :pre10ihash))
print hv
select /* test_query*/* from dual;
select kglnahsh, kglnahsv
from x$kglob
where kglnaobj='select /* test_query*/* from dual';
DBMS_UTILITY.GET_SQL_HASH(HASH_VALUE):
5E80AD80D50E1C809454E8B352F7E43B
KGLNAHSV: 80ad805e801c0ed5b3e854943be4f752
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.kgllkhdl
Удалить