Re: Question about Oracle pipes sizes and the messages at the queue

From: Hanan Hit <hithanan_at_gmail.com>
Date: Mon, 17 Mar 2014 21:59:37 -0700
Message-Id: <82C1B7A5-DB73-4234-A3B8-51C091EC3AB4_at_gmail.com>

Hi Jonathan,

Thanks for the prompt response.

It is helpful for sure and give me more insight than I had before - Thank you for that.

It is strange though that I do see different sums once running your query with summary

select
type ,
pipe_name,
sum(chunk_size) /1024/1024 chunk_size
from
(
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

and ksmchcom = 'kkxpr'
)
group by
type ,
pipe_name
order by 3 ,2
;

Result

TYPE

---

PIPE_NAME

---

CHUNK_SIZE

---

PUBLIC
ROPIPE
.000228882

PUBLIC
PUBSUBPIPENAME
 .00062561

PUBLIC
ADVISORPIPE
.000915527

PUBLIC
MAKEPIPE
.001235962

PUBLIC
OUTBOUNDPIPE
.006538391

PUBLIC
PUBSUBPIPE
1.86437225

vs. the below query

select * from ( select row_number () over ( partition by namespace order by sharable_mem desc ) row_within, namespace, sharable_mem/1024/1024 sharable_mem
, substr(name, 1,40 )
short_name from v$db_object_cache order by sharable_mem desc ) where namespace like '%PIPE' and row_within <= 5 order by sharable_mem desc, namespace, row_within /

Result

ROW_WITHIN NAMESPACE
---------- ----------------------------------------------------------------
SHARABLE_MEM

---

SHORT_NAME

---

         1 PIPE
  2.44610596
PUBSUBPIPE          2 PIPE
   .39125061
OUTBOUNDPIPE          3 PIPE
  .023963928
ROPIPE          4 PIPE
  .008384705
ACXPROD45          5 PIPE
  .008369446
CUSTVEHPIPE In any this is very helpful and will check it during the different times of the day.

Best,

        Hanan

On Mar 17, 2014, at 7:36 PM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:

> 
> 
> Is this the sort of thing you'd be interested in:
> 
> 
> http://jonathanlewis.wordpress.com/2009/01/30/pipes/
> 
> 
> Regards
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
> _at_jloracle
> 
> ________________________________________
> From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Hanan Hit [hithanan_at_gmail.com]
> Sent: 17 March 2014 21:13
> To: Oracle-L_at_FreeLists.org
> Cc: Hanan Hit
> Subject: Question about Oracle pipes sizes and the messages at the queue
> 
> Hi All,
> 
> I have a quick question about Oracle pipes sizes and the messages at the queue.
> 
> Basically what I am looking for is a method to identify the size of the pipe in the the shared pool as well as to identify the number of messages (queue size) and possibly their content.
> 
> I am using the below query (extracted and modified from an Hotsos) presentation about Shared pool , which lets me see the memory usage of the different Pipes but just wanted to check whether there is other option and still looking at the messages at the queue.
> 
> select row_within ,
> namespace ,
> sharable_mem ,
> short_name
> from ( select row_number () over ( partition by namespace order by sharable_mem desc ) row_within, namespace,
> sharable_mem/1024/1024 sharable_mem,
> substr(name, 1,60 ) as short_name
> from v$db_object_cache order by sharable_mem desc )
> where namespace like '%PIPE'
> order by sharable_mem desc, namespace, row_within
> /
> 
> 
> 
> Best,
>        Hanan
> 
> 
> --
> http://www.freelists.org/webpage/oracle-l
> 
> 

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Mar 18 2014 - 05:59:37 CET