9 Oracle Database Advanced Queuing and Messaging Gateway Views
These topics describe the Oracle Database Advanced Queuing (AQ) administrative interface views and Oracle Messaging Gateway (MGW) views.
Note:
All views not detailed in the following sections are described in the Oracle Database Reference.
Oracle AQ Views
-
V$AQ_MESSAGE_CACHE_STAT: Memory Management for Sharded Queues
-
V$AQ_SHARDED_SUBSCRIBER_STAT: Sharded Queue Subscriber Statistics
-
V$AQ_REMOTE_DEQUEUE_AFFINITY: Dequeue Affinity Instance List
-
ALL_QUEUE_TABLES: Queue Tables Accessible to the Current User
-
ALL_QUEUE_SUBSCRIBERS: Subscribers for Queues Where User Has Queue Privileges
-
USER_ATTRIBUTE_TRANSFORMATIONS: User Transformation Functions
-
AQ$INTERNET_USERS: Oracle Database Advanced Queuing Agents Registered for Internet Access
-
V$BUFFERED_SUBSCRIBERS: Subscribers for All Buffered Queues in the Instance
-
V$BUFFERED_PUBLISHERS: All Buffered Publishers in the Instance
-
V$PERSISTENT_QUEUES: All Active Persistent Queues in the Instance
-
V$PERSISTENT_SUBSCRIBERS: All Active Subscribers of the Persistent Queues in the Instance
-
V$PERSISTENT_PUBLISHERS: All Active Publishers of the Persistent Queues in the Instance
-
V$PROPAGATION_SENDER: Buffer Queue Propagation Schedules on the Sending (Source) Side
-
V$PROPAGATION_RECEIVER: Buffer Queue Propagation Schedules on the Receiving (Destination) Side
-
V$SUBSCR_REGISTRATION_STATS: Diagnosability of Notifications
-
V$AQ_JOB_COORDINATOR: Performance Statistics per Coordinator
Oracle Messaging Gateway Views
9.1 DBA_QUEUE_TABLES: All Queue Tables in Database
This view contains information about the owner instance for a queue table.
A queue table can contain multiple queues. In this case, each queue in a queue table has the same owner instance as the queue table. The DBA_QUEUE_TABLES
columns are the same as those in ALL_QUEUE_TABLES
.
See Also:
Oracle Database Reference for more information about DBA_QUEUE_TABLES
.
9.2 USER_QUEUE_TABLES: Queue Tables in User Schema
This view is the same as DBA_QUEUE_TABLES
with the exception that it only shows queue tables in the user's schema.
USER_QUEUE_TABLES
does not contain a column for OWNER
.
See Also:
Oracle Database Reference for more information about USER_QUEUE_TABLES
.
9.3 ALL_QUEUE_TABLES: Queue Tables Accessible to the Current User
This view describes queue tables accessible to the current user.
See Also:
Oracle Database Reference for more information about ALL_QUEUE_TABLES
.
9.4 DBA_QUEUES: All Queues in Database
The DBA_QUEUES
view specifies operational characteristics for every queue in a database.
Its columns are the same as those ALL_QUEUES
. Oracle Database 12c Release 1 (12.1) introduces a new column SHARDED
with data type VARCHAR2(5)
. The value for this column is TRUE
for sharded queue, otherwise FALSE
.
See Also:
Oracle Database Reference for more information about DBA_QUEUES
.
9.5 USER_QUEUES: Queues In User Schema
The USER_QUEUES
view is the same as DBA_QUEUES
with the exception that it only shows queues in the user's schema.
Oracle Database 12c Release 1 (12.1) introduces a new column SHARDED
with data type VARCHAR2(5)
. The value for this column is TRUE
for sharded queue, otherwise FALSE
.
See Also:
Oracle Database Reference for more information about USER_QUEUES
.
9.6 ALL_QUEUES: Queues for Which User Has Any Privilege
The ALL_QUEUES
view describes all queues on which the current user has enqueue or dequeue privileges.
If the user has any Advanced Queuing system privileges, like MANAGE
ANY
QUEUE
, ENQUEUE
ANY
QUEUE
or DEQUEUE
ANY
QUEUE
, this view describes all queues in the database. Oracle Database 12c Release 1 (12.1) introduces a new column SHARDED
with data type VARCHAR2(5)
. The value for this column is TRUE
for sharded queue, otherwise FALSE
.
See Also:
Oracle Database Reference for more information about ALL_QUEUES
.
9.7 DBA_QUEUE_SCHEDULES: All Propagation Schedules
The DBA_QUEUE_SCHEDULES
view describes all the current schedules in the database for propagating messages.
See Also:
Oracle Database Reference for more information about DBA_QUEUE_SCHEDULES
.
9.8 USER_QUEUE_SCHEDULES: Propagation Schedules in User Schema
The USER_QUEUE_SCHEDULES
view is the same as DBA_QUEUE_SCHEDULES
with the exception that it only shows queue schedules in the user's schema.
See Also:
Oracle Database Reference for more information about USER_QUEUE_SCHEDULES
.
9.9 QUEUE_PRIVILEGES: Queues for Which User Has Queue Privilege
The QUEUE_PRIVILEGES
view describes queues for which the user is the grantor, grantee, or owner.
It also shows queues for which an enabled role on the queue is granted to PUBLIC
.
See Also:
Oracle Database Reference for more information about QUEUE_PRIVILEGES
.
9.10 AQ$<Queue_Table_Name>: Messages in Queue Table
The AQ$
<Queue_Table_Name>
view describes the queue table in which message data is stored.
This view is automatically created with each queue table and should be used for querying the queue data. The dequeue history data (time, user identification and transaction identification) is only valid for single-consumer queues.
In a queue table that is created with the compatible
parameter set to '8.1' or higher, messages that were not dequeued by the consumer are shown as "UNDELIVERABLE
". You can dequeue these messages by msgid
. If the Oracle Database Advanced Queuing queue process monitor is running, then the messages are eventually moved to an exception queue. You can dequeue these messages from the exception queue with an ordinary dequeue.
A multiconsumer queue table created without the compatible
parameter, or with the compatible
parameter set to '8.0', does not display the state of a message on a consumer basis, but only displays the global state of the message.
Note:
Queues created in a queue table with compatible
set to 8.0
(referred to in this guide as 8.0-style queues) are deprecated in Oracle Database Advanced Queuing 10g Release 2 (10.2). Oracle recommends that any new queues you create be 8.1-style or newer and that you migrate existing 8.0-style queues at your earliest convenience.
When a message is dequeued using the REMOVE
mode, DEQ_TIME
, DEQ_USER_ID
, and DEQ_TXN_ID
are updated for the consumer that dequeued the message.
You can use MSGID
and ORIGINAL_MSGID
to chain propagated messages. When a message with message identifier m1
is propagated to a remote queue, m1
is stored in the ORIGINAL_MSGID
column of the remote queue.
Beginning with Oracle Database 10g, AQ$
Queue_Table_Name
includes buffered messages. For buffered messages, the value of MSG_STATE
is one of the following:
-
IN MEMORY
Buffered messages enqueued by a user
-
DEFERRED
Buffered messages enqueued by a capture process
-
SPILLED
User-enqueued buffered messages that have been spilled to disk
-
DEFERRED SPILLED
Capture-enqueued buffered messages that have been spilled to disk
-
BUFFERED EXPIRED
Expired buffered messages
For JMS Sharded Queues, the columns RETRY_COUNT
, EXCEPTION_QUEUE_OWNER
, EXCEPTION_QUEUE
, PROPAGATED_MSGID
, SENDER_NAME
, SENDER_ADDRESS
, SENDER_PROTOCOL
, ORIGINAL_MSGID
, ORIGINAL_QUEUE_NAME
, ORIGINAL_QUEUE_OWNER
, EXPIRATION_REASON
are always NULL
.
For JMS Sharded Queues, this view shows messages only for durable subscribers because non durable subscribers are session specific. The view returns data from the in-memory Sharded Queue message cache if available, otherwise from the values on disk. A user is required to be one of the following in order to query from AQ$<
queue_name
>
view for Sharded Queues:
-
user is the owner
-
user has "dequeue" privilege on queue
-
user has "dequeue any queue" privilege
The view has the following difference for Sharded Queues for 12c and future releases:
-
MSG_PRIORITY
is defined asNUMBER(38)
-
MSG_STATE
in a queue table does not haveBUFFERED_EXPIRED
hence the max length ofUNDELIVERABLE
is taken as length gotMSG_STATE
. -
EXPIRATION
is defined asTIMESTAMP(6) WITH TIME ZONE
in a queue table. -
USER_DATA
column is defined using a decode onUSERDATA_RAW
andUSERDATA_BLOB
withUTL_RAW.CAST_TO_VARCHAR2
. -
CONSUMER_NAME
is defined asVARCHAR2(128)
Table 9-1 AQ$<Queue_Table_Name> View
Column | Datatype | NULL | For JMS Sharded Queues 12c Release 1 (12.1) | Description |
---|---|---|---|---|
|
|
- |
Queue name |
|
|
|
- |
|
|
|
|
- |
|
|
|
|
|
Unique identifier of the message |
|
|
|
- |
User-provided correlation identifier |
|
|
|
- |
|
Message priority |
|
|
- |
Message state. 12c Release 1 (12.1) queue table doesnt have |
|
|
|
- |
Time in date format at which the message in waiting state would become ready. Equals |
|
|
|
- |
Time as a timestamp format at which the message in waiting state would become ready. Equals |
|
|
|
- |
|
Number of seconds in which the message expires after being |
|
|
- |
|
|
|
|
- |
Enqueue time |
|
|
|
- |
Enqueue time |
|
|
|
- |
Enqueue user ID |
|
|
|
- |
Enqueue user name |
|
|
|
- |
Enqueue transaction ID |
|
|
|
- |
Dequeue time |
|
|
|
- |
Dequeue time |
|
|
|
- |
Dequeue user ID |
|
|
|
- |
Dequeue user name |
|
|
|
- |
Dequeue transaction ID |
|
|
|
- |
|
Number of retries |
|
|
- |
|
Exception queue schema |
|
|
- |
|
Exception queue name |
|
- |
- |
User data. |
|
|
|
- |
|
Name of the agent enqueuing the message (valid only for 8.1-compatible queue tables) |
|
|
- |
|
Queue name and database name of the source (last propagating) queue (valid only for 8.1-compatible queue tables). The database name is not specified if the source queue is in the local database. |
|
|
- |
|
Protocol for sender address (reserved for future use and valid only for 8.1-compatible queue tables) |
|
|
- |
|
Message ID of the message in the source queue (valid only for 8.1-compatible queue tables) |
|
|
- |
|
Name of the agent receiving the message (valid only for 8.1-compatible multiconsumer queue tables) |
|
|
- |
Queue name and database link name of the agent receiving the message.The database link name is not specified if the address is in the local database. The address is |
|
|
|
- |
Protocol for address of receiving agent (valid only for 8.1-compatible queue tables) |
|
|
|
- |
|
Message ID of the message in the queue of the receiving agent (valid only for 8.1-compatible queue tables) |
|
|
- |
|
Name of the queue the message came from |
|
|
- |
|
Owner of the queue the message came from |
|
|
- |
|
Reason the message came into exception queue. Possible values are |
Note:
A message is moved to an exception queue if RETRY_COUNT
is greater than MAX_RETRIES
. If a dequeue transaction fails because the server process dies (including ALTER
SYSTEM
KILL
SESSION
) or SHUTDOWN
ABORT
on the instance, then RETRY_COUNT
is not incremented.
9.11 AQ$<Queue_Table_Name_S>: Queue Subscribers
The AQ$
<Queue_Table_Name
_S>
view provides information about subscribers for all the queues in any given queue table.
It shows subscribers created by users with DBMS_AQADM.ADD_SUBSCRIBER
and subscribers created for the apply process to apply user-created events. It also displays the transformation for the subscriber, if it was created with one. It is generated when the queue table is created.
This view provides functionality that is equivalent to the DBMS_AQADM.QUEUE_SUBSCRIBERS()
procedure. For these queues, Oracle recommends that the view be used instead of this procedure to view queue subscribers. This view is created only for 8.1-compatible queue tables.
Table 9-2 AQ$<Queue_Table_Name_S> View
Column | Datatype | NULL | Description |
---|---|---|---|
|
|
|
Name of queue for which subscriber is defined |
|
|
- |
Name of agent |
|
|
- |
Address of agent |
|
|
- |
Protocol of agent |
|
|
- |
Name of the transformation (can be null) |
9.12 AQ$<Queue_Table_Name_R>: Queue Subscribers and Their Rules
The AQ$
<Queue_Table_Name
_R>
view displays only the subscribers based on rules for all queues in a given queue table, including the text of the rule defined by each subscriber.
It also displays the transformation for the subscriber, if one was specified. It is generated when the queue table is created.
This view is created only for 8.1-compatible queue tables.
Table 9-3 AQ$<Queue_Table_Name_R> View
Column | Datatype | NULL | Description |
---|---|---|---|
|
|
|
Name of queue for which subscriber is defined |
|
|
- |
Name of agent |
|
|
- |
Address of agent |
|
|
- |
Protocol of agent |
|
|
- |
Text of defined rule |
|
|
- |
Set of rules |
|
|
- |
Name of the transformation (can be null) |
9.13 AQ$Queue_Name_R: Queue Subscribers and Their Rules for Multi-consumer Queue
This table shows queue subscribers and their rules for multi-consumer queue.
Table 9-4 AQ$Queue_Name_R: Queue Subscribers and Their Rules for Multi-consumer Queue
Column | Datatype | NULL |
---|---|---|
|
|
|
|
|
- |
|
|
- |
|
|
- |
|
|
- |
|
|
- |
|
|
- |
9.14 AQ$Queue_Name_S: Queue Subscribers and Their Rules for Multi-consumer Queue
This table shows queue subscribers and their rules for multi-consumer queue.
Table 9-5 AQ$Queue_Name_S: Queue Subscribers and Their Rules for Multi-consumer Queue
Column | Datatype | NULL |
---|---|---|
|
|
|
|
|
- |
|
|
- |
|
|
- |
|
|
- |
|
|
- |
9.15 DBA_QUEUE_SUBSCRIBERS: All Queue Subscribers in Database
The DBA_QUEUE_SUBSCRIBERS
view returns a list of all subscribers on all queues in the database.
Its columns are the same as those in ALL_QUEUE_SUBSCRIBERS
.
See Also:
Oracle Database Reference for more information about DBA_QUEUE_SUBSCRIBERS
.
9.16 USER_QUEUE_SUBSCRIBERS: Queue Subscribers in User Schema
The USER_QUEUE_SUBSCRIBERS
view returns a list of subscribers on queues in the schema of the current user.
Its columns are the same as those in ALL_QUEUE_SUBSCRIBERS
except that it does not contain the OWNER
column.
See Also:
Oracle Database Reference for more information about USER_QUEUE_SUBSCRIBERS
.
9.17 ALL_QUEUE_SUBSCRIBERS: Subscribers for Queues Where User Has Queue Privileges
The ALL_QUEUE_SUBSCRIBERS
view returns a list of subscribers to queues that the current user has privileges to dequeue from.
See Also:
Oracle Database Reference for more information about ALL_QUEUE_SUBSCRIBERS
.
9.18 DBA_TRANSFORMATIONS: All Transformations
The DBA_TRANSFORMATIONS
view displays all the transformations in the database.
These transformations can be specified with Advanced Queue operations like enqueue, dequeue and subscribe to automatically integrate transformations in messaging. This view is accessible only to users having DBA privileges.
See Also:
Oracle Database Reference for more information about DBA_TRANSFORMATIONS
.
9.19 DBA_ATTRIBUTE_TRANSFORMATIONS: All Transformation Functions
The DBA_ATTRIBUTE_TRANSFORMATIONS
view displays the transformation functions for all the transformations in the database.
See Also:
Oracle Database Reference for more information about DBA_ATTRIBUTE_TRANSFORMATIONS
.
9.20 USER_TRANSFORMATIONS: User Transformations
The USER_TRANSFORMATIONS
view displays all the transformations owned by the user.
To view the transformation definition, query USER_ATTRIBUTE_TRANSFORMATIONS
.
See Also:
Oracle Database Reference for more information about USER_TRANSFORMATIONS
.
9.21 USER_ATTRIBUTE_TRANSFORMATIONS: User Transformation Functions
The USER_ATTRIBUTE_TRANSFORMATIONS
view displays the transformation functions for all the transformations of the user.
See Also:
Oracle Database Reference for more information about USER_ATTRIBUTE_TRANSFORMATIONS
.
9.22 DBA_SUBSCR_REGISTRATIONS: All Subscription Registrations
The DBA_SUBSCR_REGISTRATIONS
view lists all the subscription registrations in the database.
See Also:
Oracle Database Reference for more information about DBA_SUBSCR_REGISTRATIONS
.
9.23 USER_SUBSCR_REGISTRATIONS: User Subscription Registrations
The USER_SUBSCR_REGISTRATIONS
view lists the subscription registrations in the database for the current user.
Its columns are the same as those in DBA_SUBSCR_REGISTRATIONS
.
See Also:
Oracle Database Reference for more information about USER_SUBSCR_REGISTRATIONS
.
9.24 AQ$INTERNET_USERS: Oracle Database Advanced Queuing Agents Registered for Internet Access
The AQ$INTERNET_USERS
view provides information about the agents registered for Internet access to Oracle Database Advanced Queuing. It also provides the list of database users that each Internet agent maps to.
Table 9-6 AQ$INTERNET_USERS View
Column | Datatype | NULL | Description |
---|---|---|---|
|
|
- |
Name of the Oracle Database Advanced Queuing Internet agent |
|
|
- |
Name of database user that this Internet agent maps to |
|
|
- |
Indicates whether this agent is allowed to access Oracle Database Advanced Queuing through HTTP ( |
|
|
- |
Indicates whether this agent is allowed to access Oracle Database Advanced Queuing through FTP (always |
9.25 V$AQ: Number of Messages in Different States in Database
The V$AQ
view provides information about the number of messages in different states for the whole database.
In a Oracle Real Application Clusters environment, each instance keeps its own Oracle Database Advanced Queuing statistics information in its own System Global Area (SGA), and does not have knowledge of the statistics gathered by other instances. When a GV$AQ
view is queried by an instance, all other instances funnel their Oracle Database Advanced Queuing statistics information to the instance issuing the query.
See Also:
Oracle Database Reference for more information about V$AQ
.
9.26 V$BUFFERED_QUEUES: All Buffered Queues in the Instance
The V$BUFFERED_QUEUES
view displays information about all buffered queues in the instance. There is one row per queue.
See Also:
Oracle Database Reference for more information about V$BUFFERED_QUEUES
.
9.27 V$BUFFERED_SUBSCRIBERS: Subscribers for All Buffered Queues in the Instance
The V$BUFFERED_SUBSCRIBERS
view displays information about the subscribers for all buffered queues in the instance. There is one row per subscriber per queue.
See Also:
Oracle Database Reference for more information about V$BUFFERED_SUBSCRIBERS
.
9.28 V$BUFFERED_PUBLISHERS: All Buffered Publishers in the Instance
The V$BUFFERED_PUBLISHERS
view displays information about all buffered publishers in the instance.
There is one row per queue per sender. The values are reset to zero when the database (or instance in an Oracle RAC environment) restarts.
See Also:
Oracle Database Reference for more information about V$BUFFERED_PUBLISHERS
.
9.29 V$PERSISTENT_QUEUES: All Active Persistent Queues in the Instance
The V$PERSISTENT_QUEUES
view displays information about all active persistent queues in the database since the queues' first activity time.
There is one row per queue. The rows are deleted when the database (or instance in an Oracle RAC environment) restarts.
See Also:
Oracle Database Reference for more information about V$PERSISTENT_QUEUES
.
9.30 V$PERSISTENT_QMN_CACHE: Performance Statistics on Background Tasks for Persistent Queues
The V$PERSISTENT_QMN_CACHE
view displays detailed statistics about all background activities relating to all queue tables in the database.
There is one row per queue table. The values are reset when the database (or instance in an Oracle RAC environment) restarts.
See Also:
Oracle Database Reference for more information about V$PERSISTENT_QMN_CACHE
.
9.31 V$PERSISTENT_SUBSCRIBERS: All Active Subscribers of the Persistent Queues in the Instance
The V$PERSISTENT_SUBSCRIBERS
view displays information about all active subscribers of the persistent queues in the database.
There is one row per instance per queue per subscriber. The rows are deleted when the database (or instance in an Oracle RAC environment) restarts.
See Also:
Oracle Database Reference for more information about V$PERSISTENT_SUBSCRIBERS
.
9.32 V$PERSISTENT_PUBLISHERS: All Active Publishers of the Persistent Queues in the Instance
The V$PERSISTENT_PUBLISHERS
view displays information about all active publishers of the persistent queues in the database.
There is one row per instance per queue per publisher. The rows are deleted when the database (or instance in an Oracle RAC environment) restarts.
See Also:
Oracle Database Reference for more information about V$PERSISTENT_PUBLISHERS.
9.33 V$PROPAGATION_SENDER: Buffer Queue Propagation Schedules on the Sending (Source) Side
The V$PROPAGATION_SENDER
view displays information about buffer queue propagation schedules on the sending (source) side.
The values are reset to zero when the database (or instance in a Oracle Real Application Clusters (Oracle RAC) environment) restarts, when propagation migrates to another instance, or when an unscheduled propagation is attempted.
See Also:
Oracle Database Reference for more information about V$PROPAGATION_SENDER
.
9.34 V$PROPAGATION_RECEIVER: Buffer Queue Propagation Schedules on the Receiving (Destination) Side
The V$PROPAGATION_RECEIVER
view displays information about buffer queue propagation schedules on the receiving (destination) side.
The values are reset to zero when the database (or instance in a Oracle Real Application Clusters (Oracle RAC) environment) restarts, when propagation migrates to another instance, or when an unscheduled propagation is attempted.
See Also:
Oracle Database Reference for more information about V$PROPAGATION_RECEIVER
.
9.35 V$SUBSCR_REGISTRATION_STATS: Diagnosability of Notifications
The V$SUBSCR_REGISTRATION_STATS
view provides information for diagnosability of notifications.
See Also:
Oracle Database Reference for more information about V$SUBSCR_REGISTRATION_STATS
.
9.36 V$METRICGROUP: Information About the Metric Group
This V$METRICGROUP
view displays information about the metric group for each of the four major Streams components: capture, propagation, apply, and queue.
See Also:
Oracle Database Reference for more information about V$METRICGROUP
.
9.37 V$AQ_MESSAGE_CACHE_STAT: Memory Management for Sharded Queues
The V$AQ_MESSAGE_CACHE_STAT
view displays statistics about memory management for sharded queues in streams_pool
within the System Global Area (SGA). Sharded queue uses streams_pool
in units of subshards. Thus columns of this view shows statistics at subshard level irrespective of the queue. This view shows statistics across all sharded queues.
Table 9-7 V$AQ_MESSAGE_CACHE_STAT View
Column | Datatype | Description |
---|---|---|
|
|
The instance id of the sharded queue |
|
|
Number of evicted subshards across all sharded queues |
|
|
Number of subshards pre-fetched by AQ background Process |
|
|
Number of subshards un-evicted by foreground process. (like dequeue process) |
|
|
Number of subshards stored as uncached. |
|
|
Number of subshards which are actively tracking dequeue rates |
|
|
Number of subshards stored cached in memory |
|
|
Maximum subshard size seen till now, in terms of number of messages per subshard |
|
|
Minimum subshard size seen till now, in terms of number of messages per subshard |
|
|
Mean subshard size seen till now, in terms of number of messages per subshard |
|
|
Average number of subshard evicted per second |
|
|
Average number of subshards pre-fetched or un-evicted per second |
|
|
Average time taken to evict one subshard (in milliseconds) |
|
|
Average time taken to un-evict one subshard (in milliseconds) |
|
|
Average ratio of number of foreground un-evictions versus background pre-fetch |
|
|
Average ratio of number of subshard pre-fetched by background without dequeue attempt versus total number of subshards prefetched |
|
|
The ID of the container to which the data pertains. Possible values include:
|
Note:
Some of the above mentioned columns will be used by sharded queue memory advisor during analysis.9.38 V$AQ_SHARDED_SUBSCRIBER_STAT: Sharded Queue Subscriber Statistics
The V$AQ_SHARDED_SUBSCRIBER_STAT
view displays statistical information about the subscribers of sharded queues. This statistics is used by the memory advisor.
Table 9-8 V$AQ_SHARDED_SUBSCRIBER_STAT View
Column | Datatype | Description |
---|---|---|
|
|
Instance Identifier |
|
|
Queue Identifier |
|
|
Subscriber Identifier |
|
|
Shard Identifier |
|
|
Priority value of the shard |
|
|
Last known dequeue position in this shard |
|
|
Number of enqueued messages |
|
|
Number of dequeued messages |
|
|
Amount of time spent performing dequeues (in seconds) |
|
|
Actual amount of CPU time spent performing dequeues (in seconds) |
|
|
Number of messages dequeued per second |
|
|
Time since last dequeue activity (in seconds) |
|
|
Estimated amount of time to drain the shard (in seconds) with current enqueue and dequeue rates. Null, if enqueue rate is greater than dequeue rate |
|
|
Estimated amount of time to drain the shard (in seconds) with no new enqueues |
|
|
The ID of the container to which the data pertains. |
9.39 V$AQ_MESSAGE_CACHE_ADVICE: Simulated Metrics
The V$AQ_MESSAGE_CACHE_ADVICE
view shows simulated metrics for a range of potential message cache sizes. This view assists in cache sizing by providing information in form of metrics as described below.
Table 9-9 V$AQ_MESSAGE_CACHE_ADVICE View
Column | Datatype | Description |
---|---|---|
|
|
Instance id |
|
|
Cache size for simulation (in megabytes) |
|
|
Size factor with respect to the current cache size (in %) |
|
|
This column can have one of the following values:
|
|
|
Estimated number of cached subshards for this size |
|
|
Estimated number of uncached subshards for this size |
|
|
Estimated number of subshards evicted for this size |
|
|
Estimated number of subshards getting evicted per minute |
|
|
Estimated number of subshards unevicted by foreground processes |
|
|
Estimated number of subshards getting unevicted by foreground processes |
|
|
Estimated number of subshards unevicted by background processes |
|
|
Estimated number of subshards getting unevicted by background processes |
|
|
Estimated number of background processes required for this size |
|
|
Simulated number of messages being enqueued per second |
|
|
Simulated number of messages being dequeued per second |
|
|
Simulated average number of messages per cached subshard |
|
|
Simulated average memory per cached subshard (in megabytes) |
|
|
Simulated average time to evict a cached subshard (in milliseconds) |
|
|
Simulated average time to unevict a cached subshard (in milliseconds) |
|
|
Reserved for internal and future use |
|
|
Amount of time that was simulated for (in minutes) |
|
|
The ID of the container to which the data pertains. |
9.40 V$AQ_REMOTE_DEQUEUE_AFFINITY: Dequeue Affinity Instance List
The V$AQ_REMOTE_DEQUEUE_AFFINITY
view lists the dequeue affinity instance of the subscribers not dequeuing locally from the shard's owner instance. Cross instance message forwarding is used for these subscribers.
Table 9-10 V$AQ_REMOTE_DEQUEUE_AFFINITY View
Column | Datatype | Description |
---|---|---|
|
|
Queue Identifier |
|
|
Queue schema name |
|
|
Queue name |
|
|
Subscriber identifier |
|
|
Shard identifier which is being forwarded from |
|
|
Owner instance from where the shard is being forwarded |
|
|
Dequeue instance id of the subscriber for the shard. Destination instance where shard is forwarded for the subscriber |
9.41 V$AQ_BACKGROUND_COORDINATOR: Performance Statistics for AQ's Master Background Coordinator Process (AQPC)
The V$AQ_BACKGROUND_COORDINATOR
view is applicable for Oracle Database 12c Release 1 (12.1) onwards.
This view lists performance statistics for the Oracle Database Advanced Queueing master background coordinator process (AQPC).
See Also:
Oracle Database Reference for more information about V$AQ_BACKGROUND_COORDINATOR
.
9.42 V$AQ_JOB_COORDINATOR: Performance Statistics per Coordinator
The V$AQ_JOB_COORDINATOR
view is applicable for Oracle Database 12c Release 1 (12.1) onwards.
This view lists performance statistics per coordinator, for every AQ coordinator controlled by the AQ's Master coordinator.
See Also::
Oracle Database Reference for more information about V$AQ_JOB_COORDINATOR
.
9.43 V$AQ_SERVER_POOL: Performance Statistics for all Servers
The V$AQ_SERVER_POOL
view is applicable for Oracle Database 12c Release 1 (12.1) onwards. This view lists performance statistics for all the servers in the pool.
See Also::
Oracle Database Reference for more information about V$AQ_SERVER_POOL
.
9.44 V$AQ_CROSS_INSTANCE_JOBS: Cross Process Jobs Description
The V$AQ_CROSS_INSTANCE_JOBS
view is applicable for Oracle Database 12c Release 1 (12.1) onwards. This view describes each of the cross process jobs.
Each job serves for forwarding messages for a shard from source instance to destination instance for a set of subscribers.
See Also::
Oracle Database Reference for more information about V$AQ_CROSS_INSTANCE_JOBS
.
9.45 V$AQ_IPC_ACTIVE_MSGS
V$AQ_IPC_ACTIVE_MSGS
displays information about long and priority messages being processed by slaves and the short message being processed by the master.
See Also:
Oracle Database Reference for more information about V$AQ_IPC_ACTIVE_MSGS
.
9.46 V$AQ_IPC_MSG_STATS
V$AQ_IPC_MSG_STATS displays cumulative statistics for each message class, for example., cumulative calls, average pending/processing time, and last failure.
See Also:
Oracle Database Reference for more information about V$AQ_IPC_MSG_STATS
.
9.47 V$AQ_IPC_PENDING_MSGS
V$AQ_IPC_PENDING_MSGS displays information about pending messages, present in the local master context.
See Also:
Oracle Database Reference for more information about V$AQ_IPC_PENDING_MSGS
.
9.48 V$AQ_NONDUR_REGISTRATIONS: Non-Durable Registrations
The V$AQ_NONDUR_REGISTRATIONS
view is applicable for Oracle Database 12c Release 1 (12.1) onwards. This view provides information about non-durable subscriptions.
See Also::
Oracle Database Reference for more information about V$AQ_NONDUR_REGISTRATIONS
.
9.49 V$AQ_NOTIFICATION_CLIENTS: Secure OCI Client Connections
The V$AQ_NOTIFICATION_CLIENTS
view is applicable for Oracle Database 12c Release 1 (12.1) onwards. This view displays performance statistics for secure OCI client connections.
See Also::
Oracle Database Reference for more information about V$AQ_NOTIFICATION_CLIENTS
.
9.50 V$AQ_SUBSCRIBER_LOAD: Durable Subscribers
The V$AQ_SUBSCRIBER_LOAD
view is applicable for Oracle Database 12c Release 1 (12.1) onwards. This view describes the load of all subscribers of sharded queues in terms of latency at every instance in an Oracle RAC environment.
Latency denotes the predicted amount of time (in seconds) required from the current time to drain all the messages for that subscriber at each respective instance. The latency calculation considers past enqueue/dequeue rates and future enqueue/dequeue rates based on history.
See Also::
Oracle Database Reference for more information about V$AQ_SUBSCRIBER_LOAD
.
9.51 V$AQ_NONDUR_SUBSCRIBER: Non-Durable Subscribers
The V$AQ_NONDUR_SUBSCRIBER
view is applicable for Oracle Database 12c Release 1 (12.1) onwards. V$AQ_NONDUR_SUBSCRIBER
provides information about non-durable subscribers on sharded queues.
See Also::
Oracle Database Reference for more information about V$AQ_NONDUR_SUBSCRIBER
.
9.52 V$AQ_NONDUR_SUBSCRIBER_LWM: LWM of Non Durable Subscriber
The V$AQ_NONDUR_SUBSCRIBER_LWM
view is applicable for Oracle Database 12c Release 1 (12.1) onwards. The LWM of a non durable subscriber is a combination of shard, priority and LWM (sub-shard).
See Also::
Oracle Database Reference for more information about V$AQ_NONDUR_SUBSCRIBER_LWM
.
9.53 V$AQ_MESSAGE_CACHE: Performance Statistics
The V$AQ_MESSAGE_CACHE
view provides performance statistics of the message cache for sharded queues at the subshard level in the instance.
See Also::
Oracle Database Reference for more information about V$AQ_MESSAGE_CACHE
.
9.54 MGW_GATEWAY: Configuration and Status Information
This view lists configuration and status information for Messaging Gateway.
Table 9-11 MGW_GATEWAY View Properties
Name | Type | Description |
---|---|---|
|
|
The database connect string used by the Messaging Gateway agent. |
|
|
The database instance on which the Messaging Gateway agent is currently running. This should be |
|
|
[Deprecated] Job number of the queued job used to start the Messaging Gateway agent process. The job number is set when Messaging Gateway is started and cleared when it shuts down. |
|
|
Name of the Messaging Gateway agent |
|
|
Gateway agent ping status. Values:
|
|
|
The time when the Messaging Gateway agent job currently running was started. This should be |
|
|
Status of the Messaging Gateway agent. Values:
|
|
|
Database user name used by the Messaging Gateway agent to connect to the database |
|
|
Comments for the agent |
|
|
Connection type used by the agent:
|
|
|
Name of the Messaging Gateway initialization file used by the agent. |
|
|
Date of last Messaging Gateway agent error. The last error information is cleared when Messaging Gateway is started. It is set if the Messaging Gateway agent fails to start or terminates due to an abnormal condition. |
|
|
Message for last Messaging Gateway agent error |
|
|
Time of last Messaging Gateway agent error |
|
|
[Deprecated] Maximum number of messaging connections to Oracle Database |
|
|
Maximum heap size used by the Messaging Gateway agent (in MB) |
|
|
Maximum number of messaging threads created by the Messaging Gateway agent |
|
|
Name of the database service that is associated with an Oracle Scheduler job class used by the agent |
9.55 MGW_AGENT_OPTIONS: Supplemental Options and Properties
This view lists supplemental options and properties for a Messaging Gateway agent.
Table 9-12 MGW_AGENT_OPTIONS View
Column | Type | Description |
---|---|---|
|
|
Name of the Messaging Gateway agent |
|
|
Indicates whether the value is stored as encrypted:
|
|
|
Name of the option |
|
|
Option type or usage: |
|
|
Value for the option. This will be << |
9.56 MGW_LINKS: Names and Types of Messaging System Links
This view lists the names and types of messaging system links currently defined.
Table 9-13 MGW_LINKS View Properties
Name | Type | Description |
---|---|---|
|
|
Name of the Messaging Gateway agent that will process propagation jobs for this link |
|
|
User comment for the link |
|
|
Name of the messaging system link |
|
|
Type of messaging system link. Values
|
9.57 MGW_MQSERIES_LINKS: WebSphere MQ Messaging System Links
This view lists information for the WebSphere MQ messaging system links. The view includes most of the messaging system properties specified when the link is created.
Table 9-14 MGW_MQSERIES_LINKS View Properties
Name | Type | Description |
---|---|---|
|
|
Name of the Messaging Gateway agent that will process propagation jobs for this link |
|
|
Connection channel |
|
|
Name of the WebSphere MQ host |
|
|
Inbound propagation log queue |
|
|
Messaging interface type. Values:
|
|
|
User comment for the link |
|
|
Name of the messaging system link |
|
|
Maximum number of messaging connections |
|
|
Link options |
|
|
Outbound propagation log queue |
|
|
Port number |
|
|
Name of the WebSphere MQ queue manager |
9.58 MGW_TIBRV_LINKS: TIB/Rendezvous Messaging System Links
This view lists information for TIB/Rendezvous messaging system links. The view includes most of the messaging system properties specified when the link was created.
Table 9-15 MGW_TIBRV_LINKS View Properties
Property Name | Type | Description |
---|---|---|
|
|
Name of the Messaging Gateway agent that will process propagation jobs for this link |
|
|
TIB/Rendezvous CM ledger file name |
|
|
TIB/Rendezvous CM correspondent name |
|
|
TIB/Rendezvous daemon parameter for RVD transport |
|
|
User comment for the link |
|
|
Name of the messaging system link |
|
|
TIB/Rendezvous network parameter for rvd transport |
|
|
Link options |
|
|
TIB/Rendezvous service parameter for rvd transport |
9.59 MGW_FOREIGN_QUEUES: Foreign Queues
This view lists information for foreign queues. The view includes most of the queue properties specified when the queue is registered.
Table 9-16 MGW_FOREIGN_QUEUES View Properties
Name | Type | Description |
---|---|---|
|
|
Queue domain type. Values:
|
|
|
Name of the messaging system link |
|
|
Name of the registered queue |
|
|
Optional queue properties |
|
|
Message provider (native) queue name |
|
|
User comment for the foreign queue |
9.60 MGW_JOBS: Messaging Gateway Propagation Jobs
This view lists information for Messaging Gateway propagation jobs. The view includes most of the job properties specified when the propagation job was created, as well as other status and statistical information.
Table 9-17 MGW_JOBS View
Column | Type | Description |
---|---|---|
|
|
Name of the Messaging Gateway agent that processes this job |
|
|
Comments for the propagation job |
|
|
Destination queue to which messages are propagated |
|
|
Indicates whether the job is enabled or not:
|
|
|
Exception queue used for propagation logging purposes |
|
|
Option type or usage: |
|
|
Number of messages moved to exception queue since the last time the agent was started |
|
|
Name of the propagation job |
|
|
Message for the last propagation error |
|
|
Date of the last propagation error |
|
|
Time of the last propagation error |
|
|
Name of the Messaging Gateway link used by this job |
|
|
Job options |
|
|
Propagation poll interval (in seconds) |
|
|
Number of messages propagated since the last time the agent was started |
|
|
Message propagation style:
|
|
|
Propagation type:
|
|
|
Subscription rule used for the propagation source |
|
|
Source queue from which messages are propagated |
|
|
Job status:
|
|
|
Transformation used for message conversion |
9.61 MGW_SUBSCRIBERS: Information for Subscribers
This view lists configuration and status information for Messaging Gateway subscribers. The view includes most of the subscriber properties specified when the subscriber is added, as well as other status and statistical information.
Table 9-18 MGW_SUBSCRIBERS View Properties
Name | Type | Description |
---|---|---|
|
|
Destination queue to which messages are propagated |
|
|
Number of messages moved to the propagation exception queue since the last time the agent was started |
|
|
Exception queue used for logging purposes |
|
|
Number of propagation failures |
|
|
Date of last propagation error |
|
|
Message for last propagation error |
|
|
Time of last propagation error |
|
|
Subscriber options |
|
|
Message propagation style. Values:
|
|
|
Number of messages propagated to the destination queue since the last time the agent was started |
|
|
Propagation type. Values:
|
|
|
Subscriber source queue |
|
|
Subscription rule |
|
|
Subscriber status. Values:
|
|
|
Propagation subscriber identifier |
|
|
Transformation used for message conversion |
9.62 MGW_SCHEDULES: Information About Schedules
This view lists configuration and status information for Messaging Gateway schedules. The view includes most of the schedule properties specified when the schedule is created, as well as other status information.
Table 9-19 MGW_SCHEDULES View Properties
Name | Type | Description |
---|---|---|
|
|
Propagation destination |
|
|
Propagation window latency (in seconds) |
|
|
Reserved for future use |
|
|
Propagation type. Values:
|
|
|
Reserved for future use |
|
|
Indicates whether the schedule is disabled. |
|
|
Propagation schedule identifier |
|
|
Propagation source |
|
|
Reserved for future use |
|
|
Reserved for future use |