项目组抱怨最近数据库突然慢了很多,生成了最近两小时的AWR发现有两个SQL非常慢,拉了最近两小时的AWR,显示第2,3条SQL占用大部分的CPU
Elapsed Time (s) | Executions | Elapsed Time per Exec (s) | %Total | %CPU | %IO | SQL Id | SQL Module | SQL Text |
32,960.61 | 3,215 | 10.25 | 66.31 | 62.49 | 4.32 | fd955stmpsuka | OWB_CCS | BEGIN wb_rt_notification.wait(... |
28,300.17 | 9,125 | 3.10 | 56.94 | 60.76 | 2.41 | 63surt2yd6qmg | JDBC Thin Client | select /*+ FIRST_ROWS(1) */ ta... |
9,252.65 | 3,390 | 2.73 | 18.62 | 68.57 | 11.94 | 79nv5f9ajsg3u | JDBC Thin Client | SELECT MIN(MSGID) FROM WB_RT_N... |
---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 AQ$WB_RT_NOTIFY_QUEUE_TAB 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]
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21605631/viewspace-759715/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/21605631/viewspace-759715/