面试宝典:介绍下Oracle数据库动态性能视图V$AQ

Oracle 19c V$AQ 动态性能视图详解

核心作用

V$AQ 是 Oracle 高级队列（Advanced Queuing）的核心监控视图，主要用于：

1. 队列性能监控：实时跟踪消息队列的处理状态
2. 积压消息检测：识别队列中的未处理消息
3. 消费者分析：监控消息消费者状态和性能
4. 异常诊断：发现消息处理失败和异常
5. 资源优化：优化队列资源配置和性能调优

关键特性

• 实时监控：动态更新队列处理状态
• 多维度分析：提供生产者、消费者、消息状态等多维度数据
• 异常跟踪：记录处理失败的消息详情
• 性能指标：包含等待时间、处理速率等关键指标
• 队列管理：支持队列级和系统级监控

---

字段详解 (Oracle 19c)

队列标识字段

字段名	数据类型	描述
QUEUE_SCHEMA	VARCHAR2(30)	队列所属的 schema 名称
QUEUE_NAME	VARCHAR2(30)	队列名称
QUEUE_TYPE	VARCHAR2(8)	队列类型： NORMAL_QUEUE EXCEPTION_QUEUE

消息状态字段

字段名	数据类型	描述
NUM_MSGS	NUMBER	队列中的消息总数
READY_MSGS	NUMBER	就绪状态的消息数量 (可立即处理)
WAITING_MSGS	NUMBER	等待处理的消息数量
EXPIRED_MSGS	NUMBER	已过期的消息数量
AVG_WAIT_TIME	NUMBER	消息平均等待时间 (秒)
MAX_RETRIES	NUMBER	消息最大重试次数

消费者字段

字段名	数据类型	描述
CONSUMER_NAME	VARCHAR2(30)	消费者名称
ADDRESS	VARCHAR2(1024)	消费者地址 (用于通知)
STATUS	VARCHAR2(8)	消费者状态： ENABLED DISABLED
LAST_ENQUEUE_TIME	DATE	最后入队时间
LAST_DEQUEUE_TIME	DATE	最后出队时间

性能统计字段

字段名	数据类型	描述
ENQUEUED_MSGS	NUMBER	累计入队消息数
DEQUEUED_MSGS	NUMBER	累计出队消息数
ENQUEUE_RATE	NUMBER	平均入队速率 (消息/秒)
DEQUEUE_RATE	NUMBER	平均出队速率 (消息/秒)
AVG_PROCESS_TIME	NUMBER	消息平均处理时间 (秒)

异常处理字段

字段名	数据类型	描述
ERROR_MSGS	NUMBER	处理失败的消息数量
RETRY_COUNT	NUMBER	当前重试次数
LAST_ERROR_MSGID	RAW(16)	最后失败的消息 ID
LAST_ERROR_TIME	DATE	最后失败的时间
LAST_ERROR_REASON	VARCHAR2(4000)	最后失败的原因

---

基表与底层原理

底层结构：

• SYS.AQ$_QUEUES (队列元数据)
• SYS.AQ$_QUEUE_TABLES (队列表结构)
• X$KZSRO (AQ 运行时统计)

数据来源：

1. 队列表的数据字典元数据
2. AQ 运行时统计收集器
3. 消息处理引擎的状态更新
4. 异常处理日志

工作原理：

1. 消息入队时更新入队统计
2. 消息出队时更新出队统计
3. 消息处理失败时记录异常信息
4. 后台进程定期更新队列状态
5. V$AQ 聚合这些数据提供实时视图

消息生命周期：

---

核心使用场景

1. 队列健康监控

SELECT queue_name, num_msgs, ready_msgs, waiting_msgs
FROM v$aq;

2. 消息积压检测

SELECT queue_name, ready_msgs + waiting_msgs AS backlog
FROM v$aq
WHERE ready_msgs + waiting_msgs > 100;

3. 消费者性能分析

SELECT consumer_name, dequeued_msgs, avg_process_time
FROM v$aq
WHERE dequeued_msgs > 0;

4. 异常处理监控

SELECT queue_name, error_msgs, last_error_reason
FROM v$aq
WHERE error_msgs > 0;

5. 队列性能调优

SELECT queue_name, enqueue_rate, dequeue_rate, avg_wait_time
FROM v$aq
ORDER BY avg_wait_time DESC;

---

常用查询 SQL 示例

1. 队列状态概览

SELECT 
  queue_schema,
  queue_name,
  queue_type,
  num_msgs AS total_messages,
  ready_msgs,
  waiting_msgs,
  expired_msgs,
  ROUND(avg_wait_time, 2) AS avg_wait_sec
FROM v$aq
ORDER BY num_msgs DESC;

2. 消息积压分析

SELECT 
  queue_name,
  ready_msgs + waiting_msgs AS pending_messages,
  ROUND((ready_msgs + waiting_msgs) * avg_process_time / 60, 2) AS est_minutes_to_clear
FROM v$aq
WHERE ready_msgs + waiting_msgs > 0;

3. 消费者性能报告

SELECT 
  consumer_name,
  status,
  enqueued_msgs,
  dequeued_msgs,
  ROUND(dequeued_msgs / NULLIF(enqueued_msgs, 0) * 100, 2) AS consumption_pct,
  ROUND(avg_process_time, 2) AS avg_process_sec,
  last_dequeue_time
FROM v$aq
WHERE consumer_name IS NOT NULL;

4. 异常队列诊断

SELECT 
  queue_schema,
  queue_name,
  error_msgs,
  retry_count,
  last_error_reason,
  TO_CHAR(last_error_time, 'YYYY-MM-DD HH24:MI:SS') AS last_error
FROM v$aq
WHERE queue_type = 'EXCEPTION_QUEUE'
  OR error_msgs > 0;

5. 队列性能指标

SELECT 
  queue_name,
  enqueue_rate AS enq_rate,
  dequeue_rate AS deq_rate,
  ROUND(avg_wait_time, 2) AS avg_wait_sec,
  ROUND(avg_process_time, 2) AS avg_process_sec,
  ROUND((ready_msgs + waiting_msgs) / NULLIF(dequeue_rate, 0) / 60, 2) AS est_min_backlog
FROM v$aq
WHERE dequeue_rate > 0;

6. 高延迟队列识别

SELECT 
  queue_name,
  ROUND(avg_wait_time, 2) AS avg_wait_sec,
  ROUND(avg_process_time, 2) AS avg_process_sec,
  ready_msgs + waiting_msgs AS pending
FROM v$aq
WHERE avg_wait_time > 5  -- 等待超过5秒
   OR avg_process_time > 10; -- 处理超过10秒

---

AQ 管理操作示例

1. 创建队列

BEGIN
  DBMS_AQADM.CREATE_QUEUE_TABLE(
    queue_table => 'msg_queue_table',
    queue_payload_type => 'SYS.AQ$_JMS_TEXT_MESSAGE');
    
  DBMS_AQADM.CREATE_QUEUE(
    queue_name => 'order_queue',
    queue_table => 'msg_queue_table');
    
  DBMS_AQADM.START_QUEUE(
    queue_name => 'order_queue');
END;
/

2. 添加消费者

BEGIN
  DBMS_AQADM.ADD_SUBSCRIBER(
    queue_name => 'order_queue',
    subscriber => sys.aq$_agent('order_processor', NULL, NULL));
END;
/

3. 入队消息

DECLARE
  enq_options DBMS_AQ.ENQUEUE_OPTIONS_T;
  msg_props DBMS_AQ.MESSAGE_PROPERTIES_T;
  msg_id RAW(16);
  payload SYS.AQ$_JMS_TEXT_MESSAGE;
BEGIN
  payload := SYS.AQ$_JMS_TEXT_MESSAGE.construct;
  payload.set_text('Order #12345');
  
  DBMS_AQ.ENQUEUE(
    queue_name => 'order_queue',
    enqueue_options => enq_options,
    message_properties => msg_props,
    payload => payload,
    msgid => msg_id);
END;
/

4. 出队消息

DECLARE
  deq_options DBMS_AQ.DEQUEUE_OPTIONS_T;
  msg_props DBMS_AQ.MESSAGE_PROPERTIES_T;
  payload SYS.AQ$_JMS_TEXT_MESSAGE;
  msg_id RAW(16);
BEGIN
  deq_options.consumer_name := 'order_processor';
  deq_options.wait := DBMS_AQ.NO_WAIT;
  
  DBMS_AQ.DEQUEUE(
    queue_name => 'order_queue',
    dequeue_options => deq_options,
    message_properties => msg_props,
    payload => payload,
    msgid => msg_id);
    
  DBMS_OUTPUT.PUT_LINE('Message: ' || payload.get_text());
END;
/

5. 队列维护

-- 清除过期消息
BEGIN
  DBMS_AQADM.PURGE_QUEUE_TABLE(
    queue_table => 'msg_queue_table',
    purge_condition => 'qtview.msg_state = ''EXPIRED''');
END;
/

-- 停止队列
BEGIN
  DBMS_AQADM.STOP_QUEUE(
    queue_name => 'order_queue');
END;
/

---

高级队列分析技巧

1. 消息生命周期跟踪

SELECT 
  q.queue_name,
  msg.msg_id,
  msg.state,
  TO_CHAR(msg.enq_time, 'YYYY-MM-DD HH24:MI:SS') AS enq_time,
  TO_CHAR(msg.deq_time, 'YYYY-MM-DD HH24:MI:SS') AS deq_time,
  msg.retry_count
FROM v$aq q
JOIN aq$msg_queue_table msg ON q.queue_name = msg.queue_name
WHERE q.queue_name = 'ORDER_QUEUE';

2. 消费者负载均衡

SELECT 
  consumer_name,
  COUNT(*) AS msg_count,
  MIN(enq_time) AS oldest_msg,
  MAX(enq_time) AS newest_msg
FROM aq$msg_queue_table
WHERE state = 'READY'
GROUP BY consumer_name;

3. 队列表空间监控

SELECT 
  queue_table_name,
  tablespace_name,
  bytes/1024/1024 AS size_mb
FROM dba_queue_tables qt
JOIN dba_segments s ON qt.table_name = s.segment_name;

4. 消息优先级分析

SELECT 
  priority,
  COUNT(*) AS msg_count,
  AVG(SYSDATE - enq_time) AS avg_age_days
FROM aq$msg_queue_table
GROUP BY priority
ORDER BY priority DESC;

---

性能优化实践

1. 优化出队批处理

DECLARE
  deq_options DBMS_AQ.DEQUEUE_OPTIONS_T;
  msg_props DBMS_AQ.MESSAGE_PROPERTIES_T;
  payload SYS.AQ$_JMS_TEXT_MESSAGE;
  msg_id RAW(16);
  no_messages EXCEPTION;
  PRAGMA EXCEPTION_INIT(no_messages, -25228);
BEGIN
  deq_options.consumer_name := 'batch_processor';
  deq_options.dequeue_mode := DBMS_AQ.BROWSE; -- 或 REMOVE
  deq_options.navigation := DBMS_AQ.FIRST_MESSAGE;
  deq_options.visibility := DBMS_AQ.ON_COMMIT;
  deq_options.wait := DBMS_AQ.NO_WAIT;
  
  FOR i IN 1..100 LOOP
    BEGIN
      DBMS_AQ.DEQUEUE(
        queue_name => 'batch_queue',
        dequeue_options => deq_options,
        message_properties => msg_props,
        payload => payload,
        msgid => msg_id);
        
      -- 处理消息
      process_message(payload);
      
      deq_options.navigation := DBMS_AQ.NEXT_MESSAGE;
    EXCEPTION
      WHEN no_messages THEN EXIT;
    END;
  END LOOP;
END;
/

2. 队列分区优化

BEGIN
  DBMS_AQADM.CREATE_QUEUE_TABLE(
    queue_table => 'part_queue_table',
    queue_payload_type => 'SYS.AQ$_JMS_TEXT_MESSAGE',
    sort_list => 'PRIORITY,ENQ_TIME', -- 排序顺序
    multiple_consumers => TRUE,
    comment => 'Partitioned queue table',
    storage_clause => 'TABLESPACE aq_data',
    partitioning_clause => 'PARTITION BY RANGE (ENQ_TIME) (
      PARTITION p1 VALUES LESS THAN (TO_DATE(''2023-01-01'',''YYYY-MM-DD'')),
      PARTITION p2 VALUES LESS THAN (TO_DATE(''2023-07-01'',''YYYY-MM-DD'')),
      PARTITION p3 VALUES LESS THAN (MAXVALUE))');
END;
/

3. 消息保留策略

BEGIN
  -- 设置消息保留时间（秒）
  DBMS_AQADM.ALTER_QUEUE_TABLE(
    queue_table => 'msg_queue_table',
    retention_time => 86400); -- 保留24小时
    
  -- 设置消息过期时间（秒）
  DBMS_AQADM.ALTER_QUEUE(
    queue_name => 'order_queue',
    max_retries => 3,
    retry_delay => 60, -- 重试延迟60秒
    retention_time => 172800); -- 消息保留48小时
END;
/

---

重要注意事项

1. 权限要求：

   • SELECT_CATALOG_ROLE
   • ENQUEUE ANY QUEUE / DEQUEUE ANY QUEUE
   • MANAGE ANY QUEUE (管理操作)

2. 性能影响：

   • 高并发队列需要适当分区
   • 大消息考虑使用 LOB 存储
   • 定期清理过期消息

3. 监控集成：

   -- 查看AQ统计历史
   SELECT * FROM dba_hist_aq_stats;
   
   -- 检查队列表空间
   SELECT tablespace_name, bytes/1024/1024 size_mb
   FROM dba_segments
   WHERE segment_name IN (
     SELECT queue_table FROM dba_queue_tables);
   

4. 高可用配置：

   -- 创建队列表时指定高可用
   BEGIN
     DBMS_AQADM.CREATE_QUEUE_TABLE(
       queue_table => 'ha_queue_table',
       queue_payload_type => 'RAW',
       storage_clause => 'TABLESPACE aq_data_ts 
                          PCTFREE 10 
                          INITRANS 4 
                          PARALLEL 4');
   END;
   /
   

5. 异常处理最佳实践：

   -- 创建异常队列
   BEGIN
     DBMS_AQADM.CREATE_QUEUE(
       queue_name => 'order_exception_queue',
       queue_table => 'msg_queue_table',
       queue_type => DBMS_AQADM.EXCEPTION_QUEUE);
   END;
   /
   
   -- 设置异常处理
   BEGIN
     DBMS_AQADM.ALTER_QUEUE(
       queue_name => 'order_queue',
       exception_queue => 'order_exception_queue');
   END;
   /
   

常见问题解决方案

问题：消息积压严重

解决方案：

-- 增加消费者数量
BEGIN
  DBMS_AQADM.ADD_SUBSCRIBER(
    queue_name => 'order_queue',
    subscriber => sys.aq$_agent('new_processor', NULL, NULL));
END;
/

-- 优化出队批处理
ALTER SYSTEM SET aq_tm_processes = 4; -- 增加AQ进程

问题：消息处理频繁失败

解决方案：

-- 调整重试策略
BEGIN
  DBMS_AQADM.ALTER_QUEUE(
    queue_name => 'order_queue',
    max_retries => 5,
    retry_delay => 300); -- 5分钟重试
END;
/

-- 检查异常队列
SELECT * FROM v$aq 
WHERE queue_name = 'order_exception_queue';

通过 V$AQ 和相关工具，DBA 可以全面监控和管理 Oracle 高级队列，确保消息系统的高效稳定运行，及时发现并解决性能瓶颈和异常问题。

欢迎关注我的公众号《IT小Chen》