Oracle 11g AQ性能优化：手动维护索引解决队列性能下降与碎片化

在 Oracle 高级队列（AQ）中，队列表（Queue Table）有几个关键的索引组织表（IOT）和索引，它们直接影响入队（Enqueue）和出队（Dequeue）的性能。

默认情况下，队列监控进程（QMON）会自动清理出队索引（名称类似 AQ$_<队列表名>_I）的碎片，但有一个例外：如果队列表存放在 ASSM 表空间里，它可能不会自动清理（具体见 KB127779）。

更重要的是，在 Oracle 11.1.0.7 及更早的版本中，QMON 进程从来不会自动清理以下三种 IOT：

1. 时间管理 IOT（名称带 _T）
2. 历史 IOT（名称带 _H）
3. 溢出 IOT（名称带 _D）

如果不定期整理这些 IOT/索引，它们会变得碎片化，导致：

• 队列性能下降（入队/出队变慢）
• QMON 进程消耗更多 CPU
• 生成更多重做日志（Redo）

因此，对于 11.2 之前的版本，定期手动“重建”或“合并”这些索引/IOT 是非常有益的。

不同 Oracle 版本的区别

11.2 版本之后

从 11.2 开始，QMON 进程通常会自动维护所有与队列表相关的索引和 IOT，目的是防止出队操作因索引碎片而变慢。

它是怎么做的？ QMON 会在出队调用过程中，识别出空的索引页，并将它们释放回段的空闲列表。这个过程不是真正的索引重建，但能有效保持性能。你可以通过查询 GV$PERSISTENT_QMN_CACHE 视图来查看释放了多少索引页。

但是，自动清理不是万能的！ 在某些特定场景下（例如“按消息ID出队”或“按条件出队”），出队操作可能不会遍历到那些空索引页，因此 QMON 也无法释放它们。

所以，即使在 11.2 之后，你仍然可以手动重建索引。 特别是当你发现出队性能有问题，或者我们可以通过设置初始化参数 _aq_tm_scanlimit=0（并重启实例）禁用了自动维护功能时。

10g 和更早的版本

在 10g 中，有一个未公开的 Bug 修复（Bug 2890421）帮助减少了 QMON 生成的重做日志量，但上述手动维护步骤在 10.1 到 11.1.0.7 版本中仍然非常有益。注意，这个修复无法向后移植到 9.2 或更早版本。

具体要做什么操作呢？

核心操作分为两类，取决于你的队列是单消费者还是多消费者：

1. 对于多消费者队列表： 你需要对几个 IOT 执行 COALESCE（合并）操作：

ALTER TABLE AQ$_<队列表名>_I COALESCE;  -- 出队 IOT
ALTER TABLE AQ$_<队列表名>_T COALESCE;  -- 时间管理 IOT
ALTER TABLE AQ$_<队列表名>_H COALESCE;  -- 历史 IOT
ALTER TABLE AQ$_<队列表名>_D COALESCE;  -- 溢出 IOT (可能存在，也可能不存在)

2. 对于单消费者队列表： 你需要对几个索引执行 REBUILD ONLINE（在线重建）：

ALTER INDEX AQ$_<队列表名>_I REBUILD ONLINE;  -- 出队索引
ALTER INDEX AQ$_<队列表名>_T REBUILD ONLINE;  -- 时间管理索引

注意：如果你用的是 Standard Edition（标准版），必须去掉 ONLINE 关键字，因为标准版不支持在线重建。

额外操作： 还会重建两个复制队列表 DEF$_AQCALL 和 DEF$_AQERROR 上的索引。

重要注意事项

• 执行时机：你可以在队列正在运行（入队/出队进行中）时执行这些操作，但前台进行队列操作的会话可能会感到性能下降。
• 推荐频率：建议至少每天运行一次，最好在数据库负载较低的时段（例如深夜）。
• 关于单消费者队列：根据另一篇文档（Note 1622498.1），由于 Bug 17480600 的影响，最好在停止单消费者队列后，再重建其索引。如果已经应用了该 Bug 的修复，则可以在线进行。

如何自动化？提供一个存储过程

Oracle 提供了一个名为 aqcoalesce 的存储过程，可以自动遍历数据库中的所有队列表，并执行相应的重建或合并操作。

如何设置？

1. 将以下存储过程代码保存为一个 SQL 脚本文件（例如 aqcoalesce.sql）。
2. 以 SYS 用户身份登录数据库并执行这个脚本：

   sqlplus "sys/<你的密码> as sysdba"
   @aqcoalesce.sql

存储过程代码如下：

create or replace procedure aqcoalesce as
  cursor multi_consumer_tables is
    select owner, queue_table
      from dba_queue_tables
     where multiple_consumers = 'TRUE'
       and queue_table not like 'AQ$_REP%';
  cursor single_consumer_tables is
    select owner, queue_table
      from dba_queue_tables
     where multiple_consumers = 'FALSE'
       and queue_table not like 'AQ$_REP%';
  cursor rep_tables is
    select owner, queue_table
      from dba_queue_tables
     where queue_table in ('DEF$_AQCALL', 'DEF$_AQERROR');
  v_sql varchar2(200);
begin
  -- For multi-consumer queue tables
  for tab in multi_consumer_tables loop
    begin
      v_sql := 'alter table "' || tab.owner || '"."AQ$_' || tab.queue_table ||
               '_I" coalesce';
      execute immediate v_sql;
      dbms_output.put_line('Coalesced _I for ' || tab.owner || '.' ||
                           tab.queue_table);
    exception
      when others then
        dbms_output.put_line('Error coalescing _I for ' || tab.owner || '.' ||
                             tab.queue_table || ': ' || SQLERRM);
    end;
    -- Similar blocks for _T, _H, and _D IOTs...
  end loop;
  -- For single-consumer queue tables
  for tab in single_consumer_tables loop
    begin
      v_sql := 'alter index "' || tab.owner || '"."AQ$_' || tab.queue_table ||
               '_I" rebuild online';
      execute immediate v_sql;
      dbms_output.put_line('Rebuilt _I for ' || tab.owner || '.' ||
                           tab.queue_table);
    exception
      when others then
        dbms_output.put_line('Error rebuilding _I for ' || tab.owner || '.' ||
                             tab.queue_table || ': ' || SQLERRM);
    end;
    -- Similar block for _T index...
  end loop;
  -- For replication queue tables
  for tab in rep_tables loop
    begin
      v_sql := 'alter table "' || tab.owner || '"."AQ$_' || tab.queue_table ||
               '_I" coalesce';
      execute immediate v_sql;
      dbms_output.put_line('Coalesced _I for ' || tab.owner || '.' ||
                           tab.queue_table);
    exception
      when others then
        dbms_output.put_line('Error coalescing _I for ' || tab.owner || '.' ||
                             tab.queue_table || ': ' || SQLERRM);
    end;
    -- Similar blocks for _T, _H IOTs...
  end loop;
end;
/

如何使用？

• 手动执行：

  EXEC aqcoalesce;

• 定时任务（例如每天凌晨0点运行）：

  VARIABLE jobno NUMBER;
  EXEC DBMS_JOB.SUBMIT(:jobno, 'aqcoalesce', TRUNC(SYSDATE) + 1, 'SYSDATE+1');
  COMMIT;

  • 如果想在早上8点运行，把 TRUNC(SYSDATE) + 1 改为 TRUNC(SYSDATE) + 1 + 8/24。

过程逻辑简述： 这个过程会通过查询 DBA_QUEUE_TABLES 等数据字典视图，找出所有的单消费者和多消费者队列表，然后根据其兼容性版本（8.0， 8.1， 10.0等），动态生成并执行对应的 ALTER INDEX ... REBUILD 或 ALTER TABLE ... COALESCE 语句。它还会处理可能不存在的溢出 IOT（_D）。

一个更稳妥的替代方案（针对单消费者队列）

如果你担心在线重建索引时出现问题，可以采用一个更保守的“停止-重建-启动”工作流程：

1. 首先，停止所有关联的队列。
2. 然后，重建索引。
3. 最后，重新启动这些队列。

以下是一个简化的逻辑示例，展示了如何在过程中嵌入停止和启动队列的逻辑（你需要根据实际情况将其集成到主过程中）：

BEGIN
  -- Stop the queue
  DBMS_AQADM.STOP_QUEUE(queue_name => 'YOUR_QUEUE_NAME');
  -- Rebuild the index (without ONLINE clause since queue is stopped)
  EXECUTE IMMEDIATE 'ALTER INDEX ... REBUILD';
  -- Start the queue
  DBMS_AQADM.START_QUEUE(queue_name => 'YOUR_QUEUE_NAME');
END;

定期对 Oracle AQ 的索引进行手动维护，尤其是在 11g 早期版本中，是保障消息队列稳定高效运行的重要手段。通过本文介绍的存储过程，你可以轻松地将这一维护任务自动化。

如果你对数据库性能调优和后端架构的深度实践感兴趣，欢迎到 云栈社区 与更多同行交流探讨。