项目组抱怨最近数据库突然慢了很多，生成了最近两小时的AWR发现有两个SQL非常慢,拉了最近两小时的AWR，显示第2,3条SQL占用大部分的CPU

---63surt2yd6qmg完整SQL

select /*+ FIRST_ROWS(1) */ tab.rowid, tab.msgid, tab.corrid, tab.priority, tab.delay, tab.expiration , tab.retry_count, tab.exception_qschema, tab.exception_queue, tab.chain_no, tab.local_order_no, tab.enq_time, tab.time_manager_info, tab.state, tab.enq_tid, tab.step_no, tab.sender_name, tab.sender_address, tab.sender_protocol, tab.dequeue_msgid, tab.user_prop, tab.user_data from "OWBSYS"."WB_RT_NOTIFY_QUEUE_TAB" tab where q_name = :1 and corrid = :2 and (state = :3 ) for update skip locked

---79nv5f9ajsg3u完整SQL

SELECT MIN(MSGID) FROM WB_RT_NOTIFY_QUEUE_TAB N1 WHERE PRIORITY = ( SELECT MIN(N2.MSG_PRIORITY) FROM N2 WHERE N2.CORR_ID = :B1 AND MSG_STATE = 'READY') AND N1.CORRID = :B1

两个SQL都是查询AQ$WB_RT_NOTIFY_QUEUE_TAB，这个表是用于存放消息队列的，查了一下返回423万条记录，几个条件过虑性都不强，我们看一下这些记录为什么这么多

SQL> select count(1)  from

  2  "OWBSYS"."WB_RT_NOTIFY_QUEUE_TAB" tab

  3  ;

  COUNT(1)

----------

   4234854

通过查找metalink确认这是一个bug,过期的消息列队可以进行清理，下面为操作过程

[oracle4@svr1-219 sql]$ sqlplus owbsys/owbsys

SQL*Plus: Release 11.2.0.2.0 Production on Tue Apr 23 14:19:03 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @stop_service.sql

Role set.

Available

Client managed service

PL/SQL procedure successfully completed.

SQL>

SQL> set verify off;

SQL> declare

  2    l_po_t dbms_aqadm.aq$_purge_options_t;

  3  begin

  l_po_t.block := TRUE;

  4    5    dbms_aqadm.purge_queue_table(

  6         queue_table => 'WB_RT_NOTIFY_QUEUE_TAB',

  7         purge_condition => 'QTVIEW.MSG_STATE=''EXPIRED''',

  8         purge_options => l_po_t); 

  9  end;

10  /

PL/SQL procedure successfully completed.

SQL> select count(1) from wb_rt_notify_queue_tab;

  COUNT(1)

----------

   4091184

SQL>

SQL>

SQL>  truncate table wb_rt_notify_queue_tab;

truncate table wb_rt_notify_queue_tab

                *

ERROR at line 1:

ORA-24005: Inappropriate utilities used to perform. DDL on AQ table

OWBSYS.WB_RT_NOTIFY_QUEUE_TAB

SQL>

SQL> set verify off;

SQL> declare

  2  l_po_t dbms_aqadm.aq$_purge_options_t;

  3  begin

  4  l_po_t.block := TRUE;

  5  dbms_aqadm.purge_queue_table(

  6  queue_table => 'WB_RT_NOTIFY_QUEUE_TAB',

  7  purge_condition => NULL,

  8  purge_options => l_po_t);

  9  end;

10  /

PL/SQL procedure successfully completed.

SQL> select count(1) from wb_rt_notify_queue_tab;

  COUNT(1)

----------

         0

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle4@svr1-219 sql]$ sqlplus owbsys/owbsys

SQL*Plus: Release 11.2.0.2.0 Production on Tue Apr 23 14:24:49 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @start_service.sql 

Role set.

Available

Database managed service using 172.17.1.219:1525:g219u4 and home

/opt/software/oracle4/product/11.1.0/db_1

PL/SQL procedure successfully completed.

参考资料：

Truncating The OWB 11.2 Notifications Queue Table Fails With ORA-24005: Inappropriate utilities used to perform. DDL on AQ table [ID 1317254.1]

OWB 10.2 Notification Queue Table WB_RT_NOTIFY_QUEUE_TAB Does Not Dequeue [ID 578399.1]