Oracle AQ性能救星：手动合并索引，让队列速度飙升90%！-程序员充电站

为了不错过每一期干货，强烈建议关注我

写技术文章，纯属“为爱发电”；更新不易，希望大家能够多多支持

1️⃣ 点赞的人，今年都升职加薪了

2️⃣ 点在看人，今年一定会发财

3️⃣ 评论区交流技术，每条留言都会回复

问题描述

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

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

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

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

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

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

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

下面简单了解手动合并高级队列（AQ）相关索引的步骤：保持队列性能，降低QMON CPU使用与重做日志生成

不同 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（合并）操作：

ALTERTABLEAQ$_<队列表名>_ICOALESCE;-- 出队 IOTALTERTABLEAQ$_<队列表名>_TCOALESCE;-- 时间管理 IOTALTERTABLEAQ$_<队列表名>_HCOALESCE;-- 历史 IOTALTERTABLEAQ$_<队列表名>_DCOALESCE;-- 溢出 IOT (可能存在，也可能不存在)

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

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

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

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

重要注意事项

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

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

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

如何设置？

将原文中从create or replace procedure aqcoalesce as ...开始的整个代码块保存为一个 SQL 脚本文件（例如aqcoalesce.sql）。
以SYS用户身份登录数据库并执行这个脚本：
```
sqlplus"sys/<你的密码> as sysdba"@aqcoalesce.sql
```

如何使用？

手动执行：
```
EXECaqcoalesce;
```
定时任务（例如每天凌晨0点运行）：
```
VARIABLE jobno NUMBER;EXECDBMS_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）。