Pipes

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.