昨晚做压力测试，出现了大量gc buffer busy等待事件，如下：
SQL> select
2 SE.Sid,
3 s.Username,
4 SE.Event,
5 se.Total_Waits,
6 SE.Time_Waited,
7 SE.Average_Wait
8from v$session S,v$session_event SE
9where S.Username is not null
10 and SE.Sid=S.Sid
11 and S.Status='ACTIVE'
12 and SE.Event not like '%SQL*Net%'
13
and total_waits>150000
14order by se.Total_Waits desc;
SID USERNAME
EVENT
TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
---------- ------------------------------ ---------------------------------------------------------------- ----------- ----------- ------------
1415 JKC
gc buffer busy
2936892 3688751 1.26
1434 JKC
gc buffer busy
2911441 3629917 1.25
1521 JKC
gc buffer busy
2829701 3769929 1.33
1447 JKC
gc buffer busy
2635781 3448018 1.31
1363 JKC
gc buffer busy
2625496 3440133 1.31
1458 JKC
gc buffer busy
2614556 3498412 1.34
1538 JKC
gc buffer busy
2604827 1566742 .6
1450 JKC
gc buffer busy
2543903 3595502 1.41
1615 JKC
gc buffer busy
2412795 1538299
.64
1464 JKC
gc buffer busy
2280610 3253063 1.43
1588 JKC
gc buffer busy
2255976 1445442
.64
SID USERNAME
EVENT
TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
---------- ------------------------------ ---------------------------------------------------------------- ----------- ----------- ------------
1332 JKC
gc buffer busy
2235856 1463170
.65
1630 JKC
gc buffer busy
2033715 3119433 1.53
1553 JKC
gc buffer busy
1852581 3083188 1.66
1391 JKC
gc buffer busy
1821384 3136508 1.72
1423 JKC
gc buffer busy
1815049 3063927 1.69
1534 JKC
gc buffer busy
1594993 2752731 1.73
1452 JKC
gc buffer busy
1580329 2779786 1.76
1419 JKC
gc buffer busy
1572279 2870302 1.83
1546 JKC
gc buffer busy
1547509 2928365 1.89
1337 JKC
gc buffer busy
1537591 2730626 1.78
1580 JKC
gc buffer busy
1535791 2687212 1.75
SID USERNAME
EVENT
TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
---------- ------------------------------ ---------------------------------------------------------------- ----------- ----------- ------------
1484 JKC
gc buffer busy
1478015 2687571 1.82
1340 JKC
gc buffer busy
1362909 2500092 1.83
1294 JKC
gc buffer busy
1362790 2545905 1.87
1498 JKC
gc buffer busy
1345789 2420412
1.8
1438 JKC
gc buffer busy
1339486767424
.57
1349 JKC
gc buffer busy
1310676 2560579 1.95
1280 JKC
gc buffer busy
1253899 2441730 1.95
1569 JKC
gc buffer busy
1221976 2806811
2.3
1277 JKC
gc buffer busy
1215732 2445881 2.01
1171 JKC
gc buffer busy
1118599 2252515 2.01
1512 JKC
gc buffer busy
1074694 2663630 2.48
SID USERNAME
EVENT
TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
---------- ------------------------------ ---------------------------------------------------------------- ----------- ----------- ------------
1224 JKC
gc buffer busy
997285 2445547 2.45
1244 JKC
gc buffer busy
987832 2435497 2.47
1297 JKC
gc buffer busy
968649 2519789
2.6
1253 JKC
gc buffer busy
960732 2388830 2.49
1159 JKC
gc buffer busy
912694 2264362 2.48
1434 JKC
gc cr block 2-way
885171135689
.15
1250 JKC
gc buffer busy
875887 2163163 2.47
1415 JKC
gc cr block 2-way
872698135059
.15
1447 JKC
gc cr block 2-way
843982129209
.15
1363 JKC
gc cr block 2-way
836710129263
.15
1538 JKC
gc cr block 2-way
832328132567
.16
SID USERNAME
EVENT
TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
---------- ------------------------------ ---------------------------------------------------------------- ----------- ----------- ------------
1521 JKC
gc cr block 2-way
823352138936
.17
1458 JKC
gc cr block 2-way
819091135278
.17
1615 JKC
gc cr block 2-way
779691131810
.17
1450 JKC
gc cr block 2-way
778205134247
.17
1588 JKC
gc cr block 2-way
754926126075
.17
1464 JKC
gc cr block 2-way
741157119590
.16
1332 JKC
gc cr block 2-way
739105124039
.17
1498 JKC
gc cr block 2-way
665989 57545
.09
1630 JKC
gc cr block 2-way
654810109158
.17
1553 JKC
gc cr block 2-way
576125 96470
.17
1252 JKC
gc buffer busy
560729 2318120 4.13
SID USERNAME
EVENT
TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
---------- ------------------------------ ---------------------------------------------------------------- ----------- ----------- ------------
1391 JKC
gc cr block 2-way
554124 97094
.18
1423 JKC
gc cr block 2-way
551079 96981
.18
1438 JKC
gc cr block 2-way
543519 68336
.13
1340 JKC
gc cr block 2-way
533081 59913
.11
1534 JKC
gc cr block 2-way
514053 72386
.14
1452 JKC
gc cr block 2-way
512377 78616
.15
1580 JKC
gc cr block 2-way
500591 70198
.14
1213 JKC
gc buffer busy
485637 2033938 4.19
1419 JKC
gc cr block 2-way
484332 79977
.17
1337 JKC
gc cr block 2-way
483492 71391
.15
1484 JKC
gc cr block 2-way
462034 65677
.14
SID USERNAME
EVENT
TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
---------- ------------------------------ ---------------------------------------------------------------- ----------- ----------- ------------
1546 JKC
gc cr block 2-way
453493 76858
.17
1294 JKC
gc cr block 2-way
446617 56533
.13
1280 JKC
gc cr block 2-way
409047 44452
.11
1349 JKC
gc cr block 2-way
393001 52530
.13
1171 JKC
gc cr block 2-way
382896 29492
.08
1277 JKC
gc cr block 2-way
378019 43291
.11
1569 JKC
gc cr block 2-way
343035 61220
.18
1512 JKC
gc cr block 2-way
285014 47140
.17
1224 JKC
gc cr block 2-way
262995 36350
.14
1244 JKC
gc cr block 2-way
257870 30291
.12
1253 JKC
gc cr block 2-way
248812 26661
.11
SID USERNAME
EVENT
TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
---------- ------------------------------ ---------------------------------------------------------------- ----------- ----------- ------------
1297 JKC
gc cr block 2-way
245641 34072
.14
1498 JKC
gc cr multi block request
239771 38276
.16
1250 JKC
gc cr block 2-way
238137 14312
.06
1159 JKC
gc cr block 2-way
225307 21164
.09
1174 JKC
gc buffer busy
195766 1933527 9.88
1220 JKC
gc buffer busy
195735 1933532 9.88
1115 JKC
gc buffer busy
186629 1807710 9.69
1377 JKC
gc buffer busy
184848 1827596 9.89
1257 JKC
gc buffer busy
178962 1768972 9.88
1415 JKC
latch: cache buffers chains
161789 93467
.58
1434 JKC
latch: cache buffers chains
159529 86911
.54
SID USERNAME
EVENT
TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
---------- ------------------------------ ---------------------------------------------------------------- ----------- ----------- ------------
1521 JKC
latch: cache buffers chains
158398 92518
.58
1538 JKC
latch: cache buffers chains
154109 91558
.59
关于gc buffer 等待事件描述：
gc buffer busy
This wait event, also known as global cache buffer busy prior to Oracle 10g, specifies the time the remote instance locally spends accessing the requested data block. This wait event is very similar to the buffer busy waits wait event in asingle-instance database and are often the result of:
1. Hot Blocks - multiple sessions may be requesting a block that is either not in buffer cache or is in an incompatible mode. Deleting some of the hot rows and re-inserting them back into the table may alleviate the problem. Most of the time the rows will be placed into a different block and reduce contention on the block. The DBA may also need to adjust the pctfree and/or pctused parameters for the table to ensure the rows are placed into a different block.
2. Inefficient Queries ? as with the gc cr request wait event, the more blocks requested from the buffer cache the more likelihood of a session having to wait for other sessions.Tuning queries to access fewer blocks will often result in less contention for the same block
我有点看不明白，谁能帮忙详细解释一下？？
|
