HUGE WAITS ON US ENQUEUE
请刘大帮分析一下 感谢！aix+9208
alert log
Tue Jun 3 09:20:30 2014
ARC0: Completed archiving log 2 thread 1 sequence 2324
Tue Jun 3 09:23:43 2014
Failure to extend rollback segment 180 because of 3113 conditionFULL status of rollback segment 180 set.
Tue Jun 3 09:25:19 2014
Failure to extend rollback segment 221 because of 3113 conditionFULL status of rollback segment 221 set.
Tue Jun 3 09:29:48 2014
Failure to extend rollback segment 62 because of 3113 conditionFULL status of rollback segment 62 set.
Tue Jun 3 09:29:54 2014
FULL status of rollback segment 62 cleared.
Tue Jun 3 09:35:13 2014
Failure to extend rollback segment 129 because of 3113 conditionFULL status of rollback segment 129 set.
Tue Jun 3 09:35:14 2014
FULL status of rollback segment 129 cleared.
Tue Jun 3 09:44:24 2014
FULL status of rollback segment 221 cleared.
Tue Jun 3 10:06:56 2014
Failure to extend rollback segment 195 because of 3113 conditionFULL status of rollback segment 195 set.
Tue Jun 3 10:08:00 2014
FULL status of rollback segment 195 cleared.
Tue Jun 3 10:08:14 2014
Failure to extend rollback segment 195 because of 3113 conditionFULL status of rollback segment 195 set.
Tue Jun 3 10:08:18 2014
FULL status of rollback segment 195 cleared.
Tue Jun 3 10:09:43 2014
Failure to extend rollback segment 195 because of 3113 conditionFULL status of rollback segment 195 set.
Tue Jun 3 10:24:38 2014
Failure to extend rollback segment 78 because of 3113 conditionFULL status of rollback segment 78 set.
Tue Jun 3 10:24:45 2014
FULL status of rollback segment 78 cleared.
Tue Jun 3 10:28:13 2014
FULL status of rollback segment 180 cleared.
Tue Jun 3 10:35:13 2014
Failure to extend rollback segment 209 because of 3113 conditionFULL status of rollback segment 209 set.
Tue Jun 3 10:35:41 2014
FULL status of rollback segment 209 cleared.
Tue Jun 3 11:01:49 2014
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=2847
Tue Jun 3 11:06:49 2014
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=1674
Tue Jun 3 11:06:49 2014
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=1148
Tue Jun 3 11:06:49 2014
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=3281
Tue Jun 3 11:06:49 2014
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=2991
Tue Jun 3 11:06:49 2014
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=2017
Tue Jun 3 11:07:07 2014
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=1157
Tue Jun 3 11:07:56 2014
statspack
STATSPACK report for
DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
sss 1092143794 ssss 1 9.2.0.8.0 YES sss
_01
Snap Id Snap Time Sessions Curs/Sess Comment
--------- ------------------ -------- --------- -------------------
Begin Snap: 99462 03-Jun-14 08:27:15 799 #########
End Snap: 99465 03-Jun-14 09:57:51 3,273 1.0
Elapsed: 90.60 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 46,448M Std Block Size: 8K
Shared Pool Size: 5,936M Log Buffer: 8,192K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 529,631.73 96,879.94
Logical reads: 114,039.18 20,859.98
Block changes: 3,910.80 715.36
Physical reads: 1,046.47 191.42
Physical writes: 101.73 18.61
User calls: 692.52 126.67
Parses: 432.01 79.02
Hard parses: 11.76 2.15
Sorts: 9,531.93 1,743.58
Logons: 1.88 0.34
Executes: 557.12 101.91
Transactions: 5.47
% Blocks changed per Read: 3.43 Recursive Call %: 67.15
Rollback per transaction %: 1.72 Rows per Sort: 2.77
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.98 Redo NoWait %: 100.00
Buffer Hit %: 99.09 In-memory Sort %: 100.00
Library Hit %: 98.28 Soft Parse %: 97.28
Execute to Parse %: 22.46 Latch Hit %: 99.82
Parse CPU to Parse Elapsd %: 0.01 % Non-Parse CPU: 97.28
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 96.43 97.58
% SQL with executions>1: 26.98 29.54
% Memory for SQL w/exec>1: 10.89 23.66
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
enqueue 8,267,217 4,195,817 65.43
row cache lock 505,031 1,276,622 19.91
library cache lock 1,513,145 567,261 8.85
library cache pin 85,841 208,716 3.25
rdbms ipc reply 42,791 75,539 1.18
-------------------------------------------------------------
Enqueue activity for Snaps: 99462 -99465
-> Enqueue stats gathered prior to 9i should not be compared with 9i data
-> ordered by Wait Time desc, Waits desc
Avg Wt Wait
Eq Requests Succ Gets Failed Gets Waits Time (ms) Time (s)
-- ------------ ------------ ----------- ----------- ------------- ------------
US 112,898 109,940 0 111,514 35,568.30 3,966,364
SQ 15,963 15,691 0 14,985 15,621.60 234,090
TT 6,657 6,641 0 4,702 2,050.56 9,642
TX 248,262 248,270 0 1,584 2,706.35 4,287
CF 8,722 8,706 16 275 3,296.32 906
HW 60,078 60,080 0 606 1,053.65 639
JQ 470 88 382 79 4,631.77 366
TO 2,397 2,397 0 67 2,391.16 160
FB 80 80 0 54 2,541.54 137
PS 81 78 3 73 790.38 58
TM 81,054 81,053 0 12 4,057.00 49
PI 8 8 0 4 4,447.50 18
MD 2 2 0 2 2,589.50 5
DV 20 20 0 7 723.14 5
CU 86,757 86,761 0 2 .50 0
-------------------------------------------------------------
US 是undo segment ，先检查一下undo 吧！
页:
[1]