一次OWB資料庫效能問題處理

專案組抱怨最近資料庫突然慢了很多，生成了最近兩小時的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/，如需轉載，請註明出處，否則將追究法律責任。