deltalounge

The agenda for the SOA Symposium 2010 has been posted. Again there are very interesting sessions during this 2 day conference. The largest and most comprehensive in the field of SOA and Cloud Computing. The Real World SOA Case Studies track offers a great opportunity to learn from the experience of others. In this track you will find:

Real-life accounts of successful and failed SOA projects discussed first-hand by those that experienced the project lifecycles and have a story to tell. These veteran practitioners will provide advice and insights regarding challenges, pitfalls, proven practices, and general project information that demonstrates the intricacies of implementing and governing service-oriented solutions in the real world.

I will be presenting the first session in this track on Using a Service Bus to Connect the Supply Chain. If you have any topics or questions in advance that you think I should address, please post them in the comments. Hope to meet you in Berlin.

Although there were already several posts on querying AQ, like “in the queue” and “enqueue“, the next one is great when monitoring queues. We are using this to monitor AQ, especially for Oracle ESB (hence the commented addition):

1
2
3
4
5
6
7
8
9

SELECT aq.name
,      aq.queue_table
,      aq.queue_type
,      v$aq.* 
FROM   v$aq
,      all_queues aq
WHERE  aq.qid = v$aq.qid
-- use if looking for ESB AQ: and    aq.queue_table = 'ESB_JAVA_DEFERRED';
;

The query will result in something like:

It contains the following metrics:

• Waiting – Number of waiting message;
• Ready – Number of messages with status ready;
• Expired – Number of expired messages;
• Total Wait – Total waiting time per queue;
• Average Wait – Average waiting time per queue.

A describtion of the AQ and Streams views can be found here.

If you are using AQ within Oracle ESB there might be a point in time you want to tune AQ performance. In this post you’ll find the results of Metalink research, and our experience on a production system.

Queue compatibility

When creating the ORAESB schema (in version 10.1.3.3) using the script $ORACLE_HOME/integration/esb/sql/oracle/create_esb_topics.sql, the queues are created with 8.1 compatibility. This is solved in 10.1.3.4. To alter this find the statement

1
2
3
4
5
6

dbms_aqadm.create_queue_table
    ( Queue_table => qtablename
    , Queue_payload_type => 'SYS.AQ$_JMS_TEXT_MESSAGE'
    , multiple_consumers => TRUE
    , compatible => '8.1'
    );

and change this to:

1
2
3
4
5
6

dbms_aqadm.create_queue_table
    ( Queue_table => qtablename
    , Queue_payload_type => 'SYS.AQ$_JMS_TEXT_MESSAGE'
    , multiple_consumers => TRUE
    , compatible => '10.2'
    );

if you already created the queues, use this statement:

1
2
3
4

dbms_aqadm.migrate_queue_table
    ( queue_table => 'ESB_JAVA_DEFERRED'
    , compatible => '10.2'
    );

If you’re not sure check the compatibility with this query:

1
2
3
4
5
6

SELECT queue_table
,      compatible
,      recipients
FROM   dba_QUEUE_tables
WHERE  owner = 'ORAESB'
;

Streams pool size

Verify the current stream_pool_size using the following query:

1
2
3
4
5
6
7

SELECT component
,      current_size/1024/1024 "CURRENT_SIZE"
,      min_size/1024/1024 "MIN_SIZE"
,      user_specified_size/1024/1024 "USER_SPECIFIED_SIZE"
,      last_oper_type "TYPE" 
FROM   v$sga_dynamic_components
;

look for the streams pool. There are several Metalink notes on this setting (including 316889.1, 102926.1 and 335516.1). The latter has a general recommandetion per RDBMS version:

• 11g: set STREAMS_POOL_SIZE to be greater or equal to 100 MB;
• 10gR2: set SGA_TARGET > 0 and STREAMS_POOL_SIZE=0 to enable autotuning of the Streams pool;
• 10gR1: use the STREAMS_POOL_SIZE init.ora parameter to configure the Streams memory allocation;

And of course you could use V$STREAMS_POOL_ADVICE to get advice for your specific situation.

Upgrade the JDK

A described here upgrading the JDK can also give a performance boost.

Whether or not your Oracle Advanced Queue (AQ) will enqueue and dequeue messages can be determined with a simple query:

1
2
3
4
5
6
7
8

SELECT name
,      queue_table
,      enqueue_enabled
,      dequeue_enabled
,      max_retries
,      retry_delay
FROM   user_queues
;

The query example is based on user_queues and in that case it has to be performed as the owner of the queue. The result wil be something like:

AQ enqueu dequeue

As a quick follow up on the previous post… If you are using a JMS queue with Oracle´s Advanced Queueing or Streams (as they have been extended to) the previously described tool can´t help you out.

For this case a blog by a former colleague helps us out. This approach boils down to…

Find the queue you want to monitor.

1
2
3
4
5

SELECT owner
,      queue_table
,      TYPE
,      user_comment
FROM   all_queue_tables

Perform a query.

1
2
3
4
5
6

SELECT msgid
,      enq_time
,      enq_uid
,      qt.user_data.text_vc
FROM   my_queue_table qt  --alter the table name
WHERE  q_name = 'MY_QUEUE' --alter the queue name