Re: Question about Oracle pipes sizes and the messages at the queue
- From: Hanan Hit <hithanan@xxxxxxxxx>
- To: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- Date: Mon, 17 Mar 2014 21:59:37 -0700
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@xxxxxxxxxxxxxxxxxx> 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
> @jloracle
>
> ________________________________________
> From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
> of Hanan Hit [hithanan@xxxxxxxxx]
> Sent: 17 March 2014 21:13
> To: Oracle-L@xxxxxxxxxxxxx
> 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
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
Other related posts:
