aq 引起的latch free 的完美解决方案

查出热点块

热点sql

优化sql

问题解决

[@more@]

oracle aq 问题，版主帮忙，江湖救急

252 processes: 216 sleeping, 31 running, 3 stopped, 2 on cpu
CPU states: 0.0% idle, 91.5% user, 8.5% kernel, 0.0% iowait, 0.0% swap
Memory: 4096M real, 147M free, 5835M swap in use, 5522M swap free

PID USERNAME LWP PRI NICE SIZE RES STATE TIME CPU COMMAND
10215 mpaper 17 43 0 547M 402M run 45:22 5.30% java
22646 oracle 1 33 0 2230M 2223M run 22.0H 4.51% oracle
22648 oracle 1 43 0 2230M 2223M run 22.0H 4.50% oracle
22709 oracle 1 33 0 2230M 2223M run 22.0H 4.49% oracle
28703 oracle 1 33 0 2229M 2223M run 2:59 4.49% oracle
28783 oracle 1 43 0 2229M 2223M run 2:51 4.44% oracle
22650 oracle 1 33 0 2230M 2223M run 22.1H 4.43% oracle
28701 oracle 1 33 0 2229M 2223M run 3:01 4.43% oracle
28787 oracle 1 43 0 2229M 2223M run 2:51 4.37% oracle
22705 oracle 1 34 0 2230M 2223M run 22.0H 4.36% oracle
28699 oracle 1 34 0 2229M 2223M run 3:00 4.36% oracle
28789 oracle 1 43 0 2229M 2223M run 2:50 4.36% oracle
22707 oracle 1 33 0 2230M 2223M run 22.0H 4.33% oracle

SQL> set linesize 200;
SQL> select a.sid ,a.event,b.program from v$session_wait a ,v$session b
2 where a.event not like 'SQL%' and a.event not like 'rdbms%' and a.sid=b.sid;

SID EVENT PROGRAM
---------- ---------------------------------------------------------------- ------------------------------------------------
21 latch free sgipgw@MPAPER-APP-1 (TNS V1-V3)
26 latch free sgipgw@MPAPER-APP-1 (TNS V1-V3)
30 latch free sgipgw@MPAPER-APP-1 (TNS V1-V3)
33 latch free tb-mb-d@MPAPER-APP-1 (TNS V1-V3)
73 latch free sgipgw@MPAPER-APP-1 (TNS V1-V3)
102 latch free sgipgw@MPAPER-APP-1 (TNS V1-V3)
118 latch free sgipgw@MPAPER-APP-1 (TNS V1-V3)
133 latch free sgipgw@MPAPER-APP-1 (TNS V1-V3)
140 latch free sgipgw@MPAPER-APP-1 (TNS V1-V3)
125 latch free sgipgw@MPAPER-APP-1 (TNS V1-V3)
104 latch free tb-mb-d@MPAPER-APP-1 (TNS V1-V3)

SID EVENT PROGRAM
---------- ---------------------------------------------------------------- ------------------------------------------------
77 latch free sgipgw@MPAPER-APP-1 (TNS V1-V3)
67 latch free sgipgw@MPAPER-APP-1 (TNS V1-V3)
52 latch free sgipgw@MPAPER-APP-1 (TNS V1-V3)
46 latch free sgipgw@MPAPER-APP-1 (TNS V1-V3)
1 pmon timer oracle@MPAPER-APP-1 (PMON)
80 log file sync sgipgw@MPAPER-APP-1 (TNS V1-V3)
162 log file sync httpd@MPAPER-APP-1 (TNS V1-V3)
165 direct path write plsqldev.exe
5 smon timer oracle@MPAPER-APP-1 (SMON)
115 PL/SQL lock timer tb-mb-d@MPAPER-APP-1 (TNS V1-V3)
9 queue messages spms@MPAPER-APP-1 (TNS V1-V3)

SID EVENT PROGRAM
---------- ---------------------------------------------------------------- ------------------------------------------------
20 queue messages spms@MPAPER-APP-1 (TNS V1-V3)
22 queue messages spms@MPAPER-APP-1 (TNS V1-V3)

24 rows selected.

现在系统非常繁忙 ，

我查了一下，系统主要使用的 aq ，现在的主要问题是 dequeue （出队列非常慢） 。。。

哪位版主能指点一下，如何能使队列出的快一些，这个系统瓶颈就解决拉 。。

我跟踪了一下 sql ：
select /*+ FIRST_ROWS */ 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 from MPAPER.SGIP_SEND_TABLE tab where q_name = :1 and state = :2 order by q_name, state, enq_time, step_no, chain_no, local_order_no for update skip locked

大家有什么好的方法可以使 aq 出队列的快一些 ？？？？？？？？？？？？？？、

查询latch 的名称：
SQL> select sid,event,p1,p2,p3 from v$session_wait where event ='latch free';

SID EVENT P1 P2 P3
---------- ---------------------------------------------------------------- ---------- ---------- ----------
21 latch free 1.6927E+10 98 0
26 latch free 1.6928E+10 98 0
39 latch free 1.6928E+10 98 2
33 latch free 1.6924E+10 98 1
56 latch free 1.6927E+10 98 4
77 latch free 1.6927E+10 98 1
102 latch free 1.6927E+10 98 0
125 latch free 1.6927E+10 98 2
133 latch free 1.6928E+10 98 5
104 latch free 1.6926E+10 98 3
80 latch free 1.6927E+10 98 3

SID EVENT P1 P2 P3
---------- ---------------------------------------------------------------- ---------- ---------- ----------
67 latch free 1.6928E+10 98 0
52 latch free 1.6926E+10 98 0
46 latch free 1.6927E+10 98 0

14 rows selected.

SQL> select * from v$latchname where latch#=98;

LATCH# NAME
---------- ----------------------------------------------------------------
98 cache buffers chains


SQL> select * from (select name,sum(MISSES) from v$latch group by name order by 2 desc) where rownum < 10;

NAME SUM(MISSES)
---------------------------------------------------------------- -----------
cache buffers chains 38755392
library cache 10816326
cache buffers lru chain 6051928
library cache pin 1617126
redo allocation 604821
enqueue hash chains 480556
shared pool 477075
dml lock allocation 447758
undo global data 310675

9 rows selected.

查询热点对象

SQL> select distinct a.owner,a.segment_name from
2 dba_extents a,
3 (select dbarfil,dbablk
4 from x$bh
5 where hladdr in
6 (select addr
7 from (select addr
8 from v$latch_children
9 order by sleeps desc)
10 where rownum < 11)) b
11 where a.RELATIVE_FNO = b.dbarfil
12 and a.BLOCK_ID <= b.dbablk and a.block_id + a.blocks > b.dbablk;

OWNER SEGMENT_NAME
------------------------------ ---------------------------------------------------------------------------------
MPAPER AQ$_SGIP_SEND_TABLE_I
MPAPER SGIP_RECV
MPAPER SGIP_SEND_HIS_1215
MPAPER SGIP_SEND_HIS_1216
MPAPER SGIP_SEND_HIS_1223
MPAPER SGIP_SEND_HIS_1225_IDX2
MPAPER SGIP_SEND_HIS_1226
SYS _SYSSMU16$
SYS _SYSSMU22$

查询热点的sql

SQL> select sql_text
2 from stats$sqltext a,
3 (select distinct a.owner,a.segment_name,a.segment_type from
4 dba_extents a,
5 (select dbarfil,dbablk
6 from (select dbarfil,dbablk
7 from x$bh order by tch desc) where rownum < 11) b
8 where a.RELATIVE_FNO = b.dbarfil
9 and a.BLOCK_ID <= b.dbablk and a.block_id + a.blocks > b.dbablk) b
10 where a.sql_text like '%'||b.segment_name||'%' and b.segment_type = 'TABLE'
11 order by a.hash_value,a.address,a.piece;

no rows selected

SQL> /

no rows selected

SQL> /

no rows selected

SQL> select sql_text
2 from v$sqltext a,
3 (select distinct a.owner,a.segment_name,a.segment_type from
4 dba_extents a,
5 (select dbarfil,dbablk
6 from (select dbarfil,dbablk
7 from x$bh order by tch desc) where rownum < 11) b
8 where a.RELATIVE_FNO = b.dbarfil
9 and a.BLOCK_ID <= b.dbablk and a.block_id + a.blocks > b.dbablk) b
10 where a.sql_text like '%'||b.segment_name||'%' and b.segment_type = 'TABLE'
11 order by a.hash_value,a.address,a.piece;

no rows selected －－－－－－－－－－－没有sql

查询 iostat

$ iostat 3 10
tty sd0 sd1 sd16 nfs1 cpu
tin tout kps tps serv kps tps serv kps tps serv kps tps serv us sy wt id
1 69 46 3 9 0 0 0 1367 41 12 0 0 0 27 10 0 64
0 79 194 17 7 0 0 0 732 91 7 0 0 0 92 8 0 0
0 27 39 3 5 0 0 0 645 81 9 0 0 0 94 6 0 0
0 27 197 17 5 0 0 0 1256 103 10 0 0 0 92 8 0 0
0 27 199 18 5 0 0 0 1373 129 11 0 0 0 91 9 0 0
0 27 173 17 5 0 0 0 664 83 7 0 0 0 92 8 0 0
0 26 202 12 7 0 0 0 711 89 8 0 0 0 92 8 0 0
0 27 513 33 6 0 0 0 1547 122 9 0 0 0 88 12 0 0
0 27 167 15 5 0 0 0 933 101 11 0 0 0 91 8 0 0
0 27 148 14 5 0 0 0 1064 91 9 0 0 0 92 8 0 0
$ sar 3 10

SunOS MPAPER-APP-1 5.10 Generic_118833-36 sun4u 12/26/2007

10:54:43 %usr %sys %wio %idle
10:54:46 92 8 0 0
10:54:49 93 7 0 0
10:54:52 93 7 0 0
10:54:55 92 8 0 0
10:54:58 90 10 0 0
10:55:01 92 8 0 0
10:55:04 92 8 0 0
10:55:07 90 10 0 0
10:55:10 89 8 0 3

iostat -E
sd0 Soft Errors: 0 Hard Errors: 0 Transport Errors: 0
Vendor: FUJITSU Product: MAW3073NCSUN72G Revision: 1703 Serial No: 0709B0LBUT
Size: 73.40GB <73400057856 bytes>
Media Error: 0 Device Not Ready: 0 No Device: 0 Recoverable: 0
Illegal Request: 0 Predictive Failure Analysis: 0
sd1 Soft Errors: 0 Hard Errors: 0 Transport Errors: 0
Vendor: FUJITSU Product: MAW3073NCSUN72G Revision: 1703 Serial No: 0724B0MFF8
Size: 73.40GB <73400057856 bytes>
Media Error: 0 Device Not Ready: 0 No Device: 0 Recoverable: 0
Illegal Request: 0 Predictive Failure Analysis: 0
sd16 Soft Errors: 2 Hard Errors: 0 Transport Errors: 0
Vendor: SUN Product: StorEdge 3320 Revision: 415G Serial No:
Size: 584.10GB <584097726464 bytes>
Media Error: 0 Device Not Ready: 0 No Device: 0 Recoverable: 0
Illegal Request: 2 Predictive Failure Analysis: 0

statspack 的部分信息 ：

--------- ------------------ -------- --------- -------------------
Begin Snap: 5 26-Dec-07 10:47:41 142 6.0
End Snap: 6 26-Dec-07 13:29:51 133 6.5
Elapsed: 162.17 (mins)

Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 1,504M Std Block Size: 8K
Shared Pool Size: 336M Log Buffer: 512K

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 140,983.06 2,946.93
Logical reads: 141,185.24 2,951.15
Block changes: 829.25 17.33
Physical reads: 6,144.57 128.44
Physical writes: 31.16 0.65
User calls: 76.65 1.60
Parses: 56.59 1.18
Hard parses: 2.05 0.04
Sorts: 13.56 0.28
Logons: 0.02 0.00
Executes: 238.74 4.99
Transactions: 47.84

% Blocks changed per Read: 0.59 Recursive Call %: 81.92
Rollback per transaction %: 0.00 Rows per Sort: 1266.12

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.98 Redo NoWait %: 100.00
Buffer Hit %: 95.66 In-memory Sort %: 99.97
Library Hit %: 99.37 Soft Parse %: 96.39
Execute to Parse %: 76.29 Latch Hit %: 99.82
Parse CPU to Parse Elapsd %: 17.06 % Non-Parse CPU: 99.47

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 90.08 56.07
% SQL with executions>1: 2.67 84.73
% Memory for SQL w/exec>1: 4.56 51.91

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
log file sync 911,116 44,209 31.45
latch free 1,055,999 42,972 30.57
CPU time 17,563 12.49
buffer busy waits 309,745 13,122 9.33
log file parallel write 973,945 5,945 4.23
-------------------------------------------------------------
^LWait Events for DB: MPAPER Instance: mpaper Snaps: 5 -6
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)

Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
log file sync 911,116 0 44,209 49 2.0
latch free 1,055,999 966,841 42,972 41 2.3
buffer busy waits 309,745 221 13,122 42 0.7
log file parallel write 973,945 0 5,945 6 2.1
PL/SQL lock timer 5,525 5,471 5,374 973 0.0
db file scattered read 6,952,114 0 5,368 1 14.9
db file parallel write 48,560 0 2,053 42 0.1
direct path write 4,452 0 1,701 382 0.0
db file sequential read 4,001,010 0 1,196 0 8.6
direct path read 7,262 0 394 54 0.0
local write wait 303 114 185 609 0.0
SQL*Net more data to client 223,160 0 103 0 0.5
LGWR wait for redo copy 16,947 2,930 78 5 0.0
control file parallel write 3,295 0 76 23 0.0
log file sequential read 1,515 0 59 39 0.0
enqueue 696 0 38 54 0.0
library cache pin 46 0 6 141 0.0
log file switch completion 46 0 4 82 0.0
library cache lock 1 0 2 2163 0.0
process startup 9 0 2 193 0.0
SQL*Net break/reset to clien 5,235 0 1 0 0.0
async disk IO 2,965 0 1 0 0.0
buffer deadlock 64 64 0 6 0.0
control file sequential read 3,898 0 0 0 0.0
wait list latch free 7 0 0 29 0.0
log file single write 10 0 0 9 0.0
db file parallel read 1 0 0 61 0.0
kksfbc child completion 1 1 0 10 0.0
reliable message 1 0 0 0 0.0
SQL*Net message from client 721,739 0 589,761 817 1.6
queue messages 2,993 2,913 28,510 9526 0.0
wakeup time manager 8 8 78 9771 0.0
SQL*Net message to client 721,733 0 5 0 1.6
SQL*Net more data from clien 22 0 0 1 0.0
-------------------------------------------------------------
^LBackground Wait Events for DB: MPAPER Instance: mpaper Snaps: 5 -6
-> ordered by wait time desc, waits desc (idle events last)

Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
log file parallel write 973,913 0 5,945 6 2.1
db file parallel write 48,555 0 2,053 42 0.1
latch free 2,667 104 81 30 0.0
LGWR wait for redo copy 16,947 2,930 78 5 0.0
control file parallel write 3,295 0 76 23 0.0
log file sequential read 1,515 0 59 39 0.0
process startup 9 0 2 193 0.0
db file scattered read 135 0 1 6 0.0
async disk IO 2,965 0 1 0 0.0
direct path read 96 0 0 4 0.0
buffer busy waits 7 0 0 48 0.0
direct path write 96 0 0 3 0.0
control file sequential read 3,778 0 0 0 0.0
log file single write 10 0 0 9 0.0
db file sequential read 33 0 0 2 0.0
rdbms ipc reply 63 0 0 0 0.0
rdbms ipc message 639,466 5,662 45,133 71 1.4
pmon timer 6,348 3,197 9,450 1489 0.0
smon timer 54 9 9,288 ###### 0.0

latch执行的语句 ：

select /*+ FIRST_ROWS */ 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 from MPAPER.SGIP_SEND_TABLE tab
where q_name = :1 and state = :2 order by q_name, state, enq_time, step_no, chain_no, local_order_no for update skip locked

语句的执行计划：

SQL> set autotrace traceonly;
SQL> select /*+ FIRST_ROWS */ tab.rowid, tab.msgid, tab.corrid, tab.priority, tab.delay,
2 tab.expiration, tab.retry_count, tab.exception_qschema, tab.exception_queue, tab.chain_no,
3 tab.local_order_no, tab.enq_time, tab.time_manager_info, tab.state, tab.enq_tid, tab.step_no,
4 tab.sender_name, tab.sender_address, tab.sender_protocol, tab.dequeue_msgid from MPAPER.SGIP_SEND_TABLE tab
5 where q_name = 'SGIP_SEND_QUEUE' and state = 0
6 order by q_name, state, enq_time, step_no, chain_no, local_order_no
7 for update skip locked
8 ;

515 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=740 Card=1
Bytes=770)

1 0 FOR UPDATE
2 1 SORT (ORDER BY) (Cost=740 Card=1 Bytes=770)
3 2 TABLE ACCESS (FULL) OF 'SGIP_SEND_TABLE' (Cost=734 Car
d=1 Bytes=770)

Statistics
----------------------------------------------------------
0 recursive calls
1137 db block gets
8810 consistent gets
0 physical reads
102204 redo size
65140 bytes sent via SQL*Net to client
1030 bytes received via SQL*Net from client
36 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
515 rows processed

这个语句是 oracle aq 在 dequeue 的时候自动执行的一个语句，不是程序中写的

问题就是这个语句效率不行啊。

select index_name,column_name from dba_ind_columns where table_name='SGIP_SEND_TABLE' ;

select count(*) from SGIP_SEND_TABLE;

select table_name,last_analyzed from user_tables where table_name='SGIP_SEND_TABLE' ;

sgip_send_table 的索引 ：

SQL> set linesize 100;
SQL> select a.index_name,a.table_name,a.column_name from
2 user_ind_columns a,user_indexes b where a.index_name=b.index_name and b.index_name like '%AQ$_SGIP_SEND_TABL%';

INDEX_NAME TABLE_NAME COLUMN_NAME
AQ$_SGIP_SEND_TABLE_T SGIP_SEND_TABLE TIME_MANAGER_INFO

AQ$_SGIP_SEND_TABLE_I SGIP_SEND_TABLE Q_NAME

AQ$_SGIP_SEND_TABLE_I SGIP_SEND_TABLE STATE

AQ$_SGIP_SEND_TABLE_I SGIP_SEND_TABLE ENQ_TIME

AQ$_SGIP_SEND_TABLE_I SGIP_SEND_TABLE STEP_NO

AQ$_SGIP_SEND_TABLE_I SGIP_SEND_TABLE CHAIN_NO

AQ$_SGIP_SEND_TABLE_I SGIP_SEND_TABLE LOCAL_ORDER_NO

7 rows selected.

alter index AQ$_SGIP_SEND_TABLE_I rebuild
好的，晚上重新创建一下把 ，白天想创建一直是报资源忙 。

最后用
alter index AQ$_SGIP_SEND_TABLE_I rebuild online
alter index AQ$_SGIP_SEND_TABLE_T rebuild online

最终问题得到解决

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/66233/viewspace-995904/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/66233/viewspace-995904/