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

eymit發表於2013-05-03
原文網址 : http://blog.itpub.net/21605631/viewspace-759715/
資料庫

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

相關文章