Oracle Scratchpad

January 30, 2009


Filed under: Infrastructure,Oracle,Troubleshooting — Jonathan Lewis @ 11:51 am GMT Jan 30,2009

A question about seeing what’s in a pipe came up on the 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:

rem     Script:         pipe_view.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Jan 2009
rem     Purpose:        
rem     Last tested 

        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
        x$kglob c,
        x$ksmhp h
        c.kglhdnsp = 7
and     c.kglobsta != 0
and     h.ksmchds = c.kglobhd0
order by

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:

--------------- -------- ---------------- -------- ---------- --------
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.


  1. Now you have your pipes sorted all you need is you slippers!

    Excellent bit of work there. I am sure you will make the poster of the orignal question very happy (I know you will he sits next to me in the office)

    Many thanks – maybe now he will stop moaning about how to do it. Thanks for saving my ears.


    Comment by David Hopkinson — January 30, 2009 @ 12:27 pm GMT Jan 30,2009 | Reply

  2. Hi Jonathan, this is absolutely wonderful and exactly what I was looking for.

    Dave, stop whinging …



    Comment by Geoff May — January 30, 2009 @ 12:38 pm GMT Jan 30,2009 | Reply

  3. Excellent article. But I have some latch free (library cache) while quering v$db_pipes and x$kglob. My query is:

    Rows     Row Source Operation
    -------  ---------------------------------------------------
        191  SORT ORDER BY (cr=0 r=0 w=0 time=32691509 us)
        191   FIXED TABLE FULL X$KGLOB (cr=0 r=0 w=0 time=32678781 us)

    Is it possible to use index access path on this fixed table?
    My DB is Oracle9i Enterprise Edition Release – 64bit Production, AIX 5.3, so I can’t collect stats on fixed objects.

    Comment by Ulan — December 26, 2012 @ 5:53 pm GMT Dec 26,2012 | Reply

    • Ulan,

      As you can see, v$db_pipes corresponds to the fixed object x$kglob. You can’t create new indexes on the x$ objects.
      Added to this, your predicate is “not like ‘%LOCK%'” – and that’s not the type of predicate that would generally allow Oracle to use an indexed access path. Take a look at the predicate section of the execution plan:

      Predicate Information (identified by operation id):
         1 - filter("X$KGLOB"."KGLOBSTA"<>0 AND "X$KGLOB"."KGLHDNSP"=7
                    AND "X$KGLOB"."INST_ID"=:B1 AND "X$KGLOB"."KGLNAOBJ" NOT LIKE

      Your only hope would be that Oracle had an “index” on the namespace (kglhdnsp) column – but it doesn’t:

      SQL> select * from v$indexed_fixed_column
        2  where table_name = 'X$KGLOB';
      -------------------- ------------ -------------------- ---------------
      X$KGLOB                         1 KGLNAHSH                           0

      In the only “indexed” access path into x$kglob is the thing that we otherwise recognise as the hash value (kglnahsh) for a cursor.

      Comment by Jonathan Lewis — December 28, 2012 @ 11:16 am GMT Dec 28,2012 | Reply

      • Hi Jonathan,
        You are right, I found this index on KGLNAHSH and tried to use it by many way, including hints, sql rewrites and so on. It seems the only way to get rid of these latches is upgrade ..
        Thank you!

        Comment by Ulan — December 28, 2012 @ 11:24 am GMT Dec 28,2012 | Reply

  4. […] from 2009 where I wrote a piece of code similar to this view definition to address a question about the content of pipes – but I got the hinting right (although I used the undesirable /*+ ordered */ hint rather […]

    Pingback by Geek Stuff | Oracle Scratchpad — March 14, 2022 @ 3:04 pm GMT Mar 14,2022 | Reply

  5. […] Oracle Pipes (Jan 2009): What’s currently inside a pipe created by dbms_pipe. […]

    Pingback by Infrastructure Catalogue | Oracle Scratchpad — March 14, 2022 @ 3:09 pm GMT Mar 14,2022 | Reply

  6. […] v$sql_shared_memory (Jan 2009): Even Oracle’s developers get the use_nl() hint wrong. […]

    Pingback by Hinting Catalogue | Oracle Scratchpad — March 14, 2022 @ 3:15 pm GMT Mar 14,2022 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: