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

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

核心作用

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

1. 后台进程监控：跟踪 AQ 协调器进程（QMCn）的状态和活动
2. 任务调度分析：监控队列任务的调度和执行情况
3. 资源消耗追踪：记录协调器进程的 CPU 和内存使用
4. 性能瓶颈诊断：识别队列处理中的性能问题
5. 故障恢复监控：跟踪消息传播和异常处理状态

关键特性

• 实时监控：提供协调器进程的实时运行状态
• 任务级洞察：显示当前执行的任务详情
• 资源使用统计：包含 CPU、I/O 等资源消耗指标
• 历史记录：保留最近的活动历史
• RAC 支持：在集群环境中显示所有实例的协调器

---

字段详解 (Oracle 19c)

进程标识字段

字段名	数据类型	描述
INST_ID	NUMBER	RAC 实例 ID (在 GV$ 视图中有效)
PROCESS_NAME	VARCHAR2(8)	进程名称 (如：‘QMC0’, ‘QMC1’)
PROCESS_ID	NUMBER	操作系统进程 ID
SESSION_ID	NUMBER	关联的数据库会话 ID
SERIAL#	NUMBER	会话序列号

状态与活动字段

字段名	数据类型	描述
STATUS	VARCHAR2(16)	进程状态： IDLE BUSY STOPPED RECOVERING
CURRENT_ACTION	VARCHAR2(64)	当前执行的操作 (如：‘Propagation’, ‘Message Processing’)
LAST_ACTION	VARCHAR2(64)	最后完成的操作
ACTION_START_TIME	TIMESTAMP	当前操作开始时间
LAST_ACTION_TIME	TIMESTAMP	最后操作完成时间

性能统计字段

字段名	数据类型	描述
CPU_USED	NUMBER	使用的 CPU 时间 (秒)
MEMORY_USED	NUMBER	使用的内存大小 (字节)
MESSAGES_PROCESSED	NUMBER	处理的消息总数
TASKS_COMPLETED	NUMBER	完成的任务数
TASKS_FAILED	NUMBER	失败的任务数
AVG_PROCESS_TIME	NUMBER	平均任务处理时间 (秒)

队列管理字段

字段名	数据类型	描述
CURRENT_QUEUE	VARCHAR2(128)	当前处理的队列名称
LAST_QUEUE	VARCHAR2(128)	最后处理的队列名称
QUEUES_MANAGED	NUMBER	管理的队列数量
WORKER_PROCESSES	NUMBER	关联的工作进程数 (QMNn)

错误处理字段

字段名	数据类型	描述
LAST_ERROR_CODE	NUMBER	最后错误代码
LAST_ERROR_MESSAGE	VARCHAR2(4000)	最后错误消息
LAST_ERROR_TIME	TIMESTAMP	最后错误发生时间
ERROR_COUNT	NUMBER	错误计数

---

基表与底层原理

底层结构：X$KZSRO (AQ 运行时统计表) 和 X$KZSRT (AQ 任务表)

数据来源：

1. AQ 协调器进程 (QMCn) 自监控
2. 任务调度器状态更新
3. 资源管理器统计
4. 错误处理日志

工作原理：

1. QMCn 进程启动时注册到系统
2. 执行任务时更新状态和统计信息
3. 每秒刷新性能指标到内存结构
4. V$AQ_BACKGROUND_COORDINATOR 提供这些数据的可读视图
5. GV$AQ_BACKGROUND_COORDINATOR 提供 RAC 全局视图

任务处理流程：

---

核心使用场景

1. 协调器状态监控

SELECT process_name, status, current_action 
FROM v$aq_background_coordinator;

2. 任务性能分析

SELECT process_name, tasks_completed, avg_process_time
FROM v$aq_background_coordinator;

3. 资源消耗诊断

SELECT process_name, cpu_used, memory_used/1024/1024 AS mem_mb
FROM v$aq_background_coordinator;

4. 错误处理监控

SELECT process_name, last_error_message, error_count
FROM v$aq_background_coordinator
WHERE error_count > 0;

5. RAC 负载均衡

SELECT inst_id, process_name, queues_managed
FROM gv$aq_background_coordinator;

---

常用查询 SQL 示例

1. 协调器状态概览

SELECT 
  process_name,
  status,
  current_action,
  TO_CHAR(action_start_time, 'YYYY-MM-DD HH24:MI:SS') AS start_time,
  ROUND((SYSDATE - action_start_time)*24*60, 2) AS minutes_running
FROM v$aq_background_coordinator
WHERE status = 'BUSY';

2. 资源使用报告

SELECT 
  process_name,
  ROUND(cpu_used, 2) AS cpu_sec,
  ROUND(memory_used/1024/1024, 2) AS mem_mb,
  messages_processed,
  ROUND(messages_processed / NULLIF(cpu_used, 0)) AS msg_per_sec
FROM v$aq_background_coordinator
ORDER BY cpu_used DESC;

3. 任务处理效率

SELECT 
  process_name,
  tasks_completed,
  tasks_failed,
  ROUND(tasks_failed / NULLIF(tasks_completed, 0) * 100, 2) AS failure_rate,
  ROUND(avg_process_time, 2) AS avg_sec_per_task
FROM v$aq_background_coordinator
WHERE tasks_completed > 0;

4. 错误诊断分析

SELECT 
  process_name,
  last_error_code,
  last_error_message,
  TO_CHAR(last_error_time, 'YYYY-MM-DD HH24:MI:SS') AS error_time,
  error_count
FROM v$aq_background_coordinator
WHERE error_count > 0
ORDER BY last_error_time DESC;

5. RAC 全局视图

SELECT 
  inst_id,
  process_name,
  status,
  current_queue,
  worker_processes,
  messages_processed
FROM gv$aq_background_coordinator
ORDER BY inst_id, process_name;

6. 长时间运行操作

SELECT 
  process_name,
  current_action,
  TO_CHAR(action_start_time, 'YYYY-MM-DD HH24:MI:SS') AS start_time,
  ROUND((SYSDATE - action_start_time)*24*60, 2) AS minutes_running
FROM v$aq_background_coordinator
WHERE status = 'BUSY'
  AND (SYSDATE - action_start_time)*24*60 > 30; -- >30分钟

---

AQ 协调器管理操作

1. 启动协调器进程

ALTER SYSTEM START BACKGROUND "QMC0";

2. 停止协调器进程

ALTER SYSTEM STOP BACKGROUND "QMC0";

3. 调整协调器数量

-- 增加协调器进程
ALTER SYSTEM SET aq_tm_processes = 2 SCOPE=BOTH;

4. 重置统计信息

-- 需要重启进程
ALTER SYSTEM STOP BACKGROUND "QMC0";
ALTER SYSTEM START BACKGROUND "QMC0";

5. 诊断日志记录

ALTER SYSTEM SET EVENTS '10703 trace name context forever, level 10';

---

高级诊断技巧

1. 协调器进程关联分析

SELECT 
  c.process_name,
  s.sid,
  s.serial#,
  s.event,
  s.seconds_in_wait
FROM v$aq_background_coordinator c
JOIN v$session s ON c.session_id = s.sid AND c.serial# = s.serial#
WHERE c.status = 'BUSY';

2. 任务历史分析

SELECT 
  process_name,
  last_action,
  COUNT(*) AS execution_count,
  AVG((last_action_time - action_start_time)*24*60*60) AS avg_duration_sec
FROM (
  SELECT 
    process_name,
    last_action,
    LAG(action_start_time) OVER (PARTITION BY process_name ORDER BY last_action_time) AS action_start_time,
    last_action_time
  FROM v$aq_background_coordinator
  WHERE last_action IS NOT NULL
)
GROUP BY process_name, last_action;

3. 资源使用趋势

SELECT 
  TRUNC(sample_time, 'MI') AS minute,
  process_name,
  AVG(cpu_used) AS avg_cpu,
  MAX(memory_used) AS max_mem
FROM (
  SELECT SYSDATE AS sample_time, process_name, cpu_used, memory_used
  FROM v$aq_background_coordinator
)
GROUP BY TRUNC(sample_time, 'MI'), process_name
ORDER BY minute DESC;

4. 错误模式识别

SELECT 
  last_error_code,
  last_error_message,
  COUNT(*) AS occurrence_count,
  MIN(last_error_time) AS first_occurrence,
  MAX(last_error_time) AS last_occurrence
FROM v$aq_background_coordinator
WHERE last_error_code IS NOT NULL
GROUP BY last_error_code, last_error_message
ORDER BY occurrence_count DESC;

---

性能优化实践

1. 优化协调器配置

-- 增加协调器数量 (根据 CPU 核心数)
ALTER SYSTEM SET aq_tm_processes = 4 SCOPE=SPFILE;

-- 增加工作进程
ALTER SYSTEM SET aq_tm_processes = 8 SCOPE=SPFILE; -- QMCn + QMNn 总数

2. 任务负载均衡

-- 查看队列分配
SELECT queue_name, consumer_name 
FROM dba_queue_schedules;

-- 重新分配队列
BEGIN
  DBMS_AQADM.ALTER_QUEUE(
    queue_name => 'order_queue',
    subscriber => sys.aq$_agent('processor2', NULL, NULL));
END;
/

3. 内存优化

-- 增加 AQ 内存缓存
ALTER SYSTEM SET stream_pool_size = 1G SCOPE=SPFILE;

-- 优化消息存储
BEGIN
  DBMS_AQADM.ALTER_QUEUE_TABLE(
    queue_table => 'msg_queue_table',
    storage_clause => 'LOB (user_data) STORE AS SECUREFILE (ENABLE STORAGE IN ROW)');
END;
/

4. 错误处理自动化

-- 创建错误处理任务
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'aq_error_handler',
    job_type => 'PLSQL_BLOCK',
    job_action => 'BEGIN aq_error_pkg.handle_errors(); END;',
    start_date => SYSTIMESTAMP,
    repeat_interval => 'FREQ=MINUTELY; INTERVAL=5',
    enabled => TRUE);
END;
/

---

重要注意事项

1. 权限要求：

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

2. 性能影响：

   • 监控查询应避免高频率运行
   • 在高峰期间限制复杂查询
   • 使用过滤条件减少数据量

3. 进程限制：

   • 最大协调器进程数由 aq_tm_processes 参数控制
   • 默认值通常为 1 或 2
   • 需要重启数据库才能增加超过当前进程数的限制

4. RAC 注意事项：

   -- 查看 RAC 全局状态
   SELECT * FROM gv$aq_background_coordinator;
   
   -- 检查跨实例传播
   SELECT * FROM dba_queue_schedules WHERE destination LIKE '%@%';
   

5. 诊断工具：

   -- 生成 AQ 诊断报告
   EXEC DBMS_AQADM.VERIFY_QUEUE_TABLE(queue_table => 'MSG_QUEUE_TABLE');
   
   -- 查看 AQ 跟踪文件
   SELECT value FROM v$diag_info WHERE name = 'Diag Trace';
   

常见问题解决方案

问题：协调器进程无响应

解决方案：

-- 1. 停止进程
ALTER SYSTEM STOP BACKGROUND "QMC0";

-- 2. 查看错误日志
SELECT * FROM v$aq_background_coordinator 
WHERE process_name = 'QMC0' AND last_error_message IS NOT NULL;

-- 3. 重启进程
ALTER SYSTEM START BACKGROUND "QMC0";

-- 4. 检查依赖队列
SELECT queue_name FROM dba_queues WHERE queue_table = 'MSG_QUEUE_TABLE';

问题：任务积压严重

解决方案：

-- 1. 增加工作进程
ALTER SYSTEM SET aq_tm_processes = 8 SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;

-- 2. 优化消息处理
BEGIN
  DBMS_AQADM.ALTER_QUEUE(
    queue_name => 'order_queue',
    max_retries => 3,
    retry_delay => 30);
END;
/

-- 3. 分区处理
BEGIN
  DBMS_AQADM.CREATE_QUEUE_TABLE(
    queue_table => 'part_queue_table',
    queue_payload_type => 'SYS.AQ$_JMS_TEXT_MESSAGE',
    partitioning_clause => 'PARTITION BY RANGE (enq_time) (...)');
END;
/

通过 V$AQ_BACKGROUND_COORDINATOR，DBA 可以深入监控 Oracle 高级队列系统的核心协调机制，确保消息处理系统的高效稳定运行，及时发现并解决性能瓶颈和系统故障。

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