A question about seeing what’s in a pipe came up on the comp.databases.oracle.server newsgroup a few days ago – it was quite a simple question and one which really ought to have a simple reply:
I’m trying to work out how many messages are queued in a pipe and how long said messages are. Anyone got any ideas?
So far, the best I can come up with is something silly like receiving DBMS_PIPE messages, unpacking them, counting and adding lengths and then repacking and requeuing the messages.
What I would like is something like this: select * from DBA_ALL_ABOUT_PIPES;
And DBA_ALL_ABOUT_PIPES has the name, type of pipe (public or private), size, number of queued messages and length of messages.
Well, there is a view gv$db_pipes, which lists some of the rows from x$kglob for namespace 7. So that’s a starting point.
There is also a view gv$sql_shared_memory which lists subheap six for child cursors by joining x$kglcursor to x$ksmhp. Since the contents of pipes have to be visible across the instance it seems a reasonable guess that the messages in a pipe are in one of the subheaps of that pipe. So this seems like another starting point.
Now, we can see in x$kqfdt that x$kglcursor is simply a different access object for x$kglob. So lets take the definition of v$sql_shared_memory, replace the x$kglcursor with x$kglob, put in the predicate (namespace 7) that identifies pipes by copying from gv$db_pipes, take out the predicate that hides parent cursor information, and make the join to heap zero instead of heap six, and what do you get.
After eliminating a few redundant columns from the query and tidying the output, I came up with the following query which (in this guise) can only be run by SYS:
select /*+ ordered use_nl(h) */ c.inst_id instance, kglnaobj pipe_name, decode(kglobt00,1,'PRIVATE','PUBLIC') type, ksmchcom chunk_com, ksmchptr chunk_ptr, ksmchsiz chunk_size, ksmchcls alloc_class from x$kglob c, x$ksmhp h where c.kglhdnsp = 7 and c.kglobsta != 0 and h.ksmchds = c.kglobhd0 order by pipe_name, chunk_com ;
The output, after I had created a pipe called FRED in another session and sent five messages (three short, one slightly longer, and one quite large) into it looked like this:
PIPE_NAME TYPE CHUNK_COM CHUNK_PT CHUNK_SIZE ALLOC_CL --------------- -------- ---------------- -------- ---------- -------- FRED PRIVATE free memory 1CDDC194 136 free FRED PRIVATE free memory 1CDDBE74 32 free FRED PRIVATE kkxpr 1CDDC284 60 freeabl FRED PRIVATE kkxpr 1CDDC250 52 freeabl FRED PRIVATE kkxpr 1CDDBEC8 456 freeabl FRED PRIVATE kkxpr 1CDDBE94 52 freeabl FRED PRIVATE kkxpr 1CDDC21C 52 freeabl FRED PRIVATE permanent memor 1CDDC0B4 204 perm
Now I’m not claiming that I’ve definitely got the complete answer to the original question – but the rows where chunk_com = ‘kkxpr’ certainly look as if they might be my five messages.