deltalounge

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.

One of the projects I’m involved in, uses Oracle ESB (from SOA Suite 10.1.3.3 MLR 18) running on AIX 5.3. Instead of using the default in-memory JMS the ESB is configured to use Oracle Advanced Queueing (AQ). Although there were tuning efforts before, the performance wasn’t up to par. AQ seemed to be the bottleneck, especially the dequeueing part. During the day we saw the number of messages queue up. Generally these numbers would decrease after business hours. This should have been a big problem if the number of messages wasn’t going to be tripled or quadrupled in the next month(s).

Upgrade the JVM

Previous attempts in cooperation with Oracle Consulting hadn’t done the trick, yet. To get a fresh view and some out-of-the-box thinking Marc joined the team. He performed a scan of the systems settings. Researching Oracle’s knowledge system resulted (searching on AIX & ESB & DB Adapter) in the clue to upgrade the JVM (6848406 – SLOW PERFORMANCE ON AIX ESB USING FILE AND DB ADAPTER). Although we were skeptic at first, because why tried a similar path before, we decided to give it a shot. The previous attempt resulted in errors in our code without enhancing the performance of the system.
With the AIX system a Java 5 is delivered in /usr/java5, to be more precise:

1
2
3
4
5
6
7
8

$:/jdk/bin>./java -version
java version "1.5.0"
Java(TM) 2 Runtime Environment, Standard Edition (build pap32dev-20080315 (SR7))
IBM J9 VM (build 2.3, J2RE 1.5.0 IBM J9 2.3 AIX ppc-32 j9vmap3223-20080315 (JIT enabled)
J9VM - 20080314_17962_bHdSMr
JIT  - 20080130_0718ifx2_r8
GC   - 200802_08)
JCL  - 20080314

To change this stop the SOA Suite and:

1
2
3

cd $ORACLE_HOME
mv jdk jdk.orginal           # rename jdk directory
ln –s /usr/java5 jdk         # Creates symbolic link

and the start the SOA Suite. With this newer Java version we had a significant performance improvement.

Remaining issue

If the change is performed as described above Enterprise Manager doesn’t allow you to browse the oc4j_soa anymore. This will result in the following error message:

Unable to make a connection to OC4J instance oc4j_soa on Application Server someserver.local. A common cause for this failure is an authentication error. The administrator password for each OC4J instance in the Cluster must be the same as the administrator password for the OC4J instance on which Application Server Control is running.

This can be solved by applying the patch for base bug 5261515. The patch adds some security jars and properties that are not in the AIX Java5 version.
Since this patch introduces issues in our code, we are working around the Em problems for now, while trying to resolve the issue.

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