hang了,严重的row cache lock 等待事件--就因大sql文本

最新推荐文章于 2024-04-28 15:56:10 发布

转载最新推荐文章于 2024-04-28 15:56:10 发布 · 193 阅读

本文分析了一个严重的rowcachelock等待事件，该事件由大型SQL文本引发，并详细展示了数据库性能监控的各项指标，包括缓存大小、负载配置文件及顶级耗时事件。

好严重的row cache lock 等待事件--就因为大sql文本

Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 17267 28-Nov-14 20:00:13 814 28.8
End Snap: 17268 28-Nov-14 20:28:11 618 6.6
Elapsed: 27.98 (mins)
DB Time: 10,152.08 (mins)

Cache Sizes
~~~~~~~~~~~ Begin End
---------- ----------
Buffer Cache: 4,032M 4,032M Std Block Size: 8K
Shared Pool Size: 6,128M 6,128M Log Buffer: 14,328K

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 41,366.98 19,543.91
Logical reads: 6,366.78 3,008.00
Block changes: 113.25 53.50
Physical reads: 2.76 1.30
Physical writes: 4.48 2.12
User calls: 162.26 76.66
Parses: 80.16 37.87
Hard parses: 1.23 0.58
Sorts: 9.63 4.55
Logons: 1.86 0.88
Executes: 90.83 42.91
Transactions: 2.12

% Blocks changed per Read: 1.78 Recursive Call %: 49.50
Rollback per transaction %: 24.94 Rows per Sort: 14.59

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.96 In-memory Sort %: 100.00
Library Hit %: 96.89 Soft Parse %: 98.47
Execute to Parse %: 11.75 Latch Hit %: 98.86
Parse CPU to Parse Elapsd %: 0.01 % Non-Parse CPU: 95.19

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 75.74 75.89
% SQL with executions>1: 89.96 99.38
% Memory for SQL w/exec>1: 88.70 99.22

Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
row cache lock 180,581 494,883 2741 81.2 Concurrenc
latch: library cache 452 153,199 ###### 25.2 Concurrenc
enq: US - contention 77,294 1,364 18 0.2 Other
CPU time 145 0.0
latch: row cache objects 5,730 92 16 0.0 Concurrenc
-------------------------------------------------------------

Statistic Name Time (s) % of DB Time
------------------------------------------ ------------------ ------------
sql execute elapsed time 530,423.2 87.1
parse time elapsed 92,703.3 15.2
PL/SQL execution elapsed time 16,841.3 2.8
failed parse elapsed time 8,641.3 1.4
DB CPU 144.6 .0

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

转载于:http://blog.itpub.net/8494287/viewspace-1357507/