Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 9995 30-Jun-13 13:00:30 1455 .1
End Snap: 9996 30-Jun-13 13:02:59 2538 .2
Elapsed: 2.49 (mins)
DB Time: 2,655.46 (mins)
采样仅2分钟;而db time可知数据库非常busy
Load Profile
71,894.3 即每秒产生70m左右的日志
Parses: 1,664.5 159.7
Hard parses: 1.6 0.2
硬解析有些高
Transactions: 10.4 事务不多
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.98 In-memory Sort %: 100.00
Library Hit %: 100.09 Soft Parse %: 99.91
Execute to Parse %: 1.29 Latch Hit %: 99.35
Parse CPU to Parse Elapsd %: 0.00 % Non-Parse CPU: 42.25
由上可知花在解析上面的工作量达到58%左右;所以应从减少硬解析入手
Execute to Parse %: 1.29 此值应很大,很小说明大多sql仅运行一次;大多花在解析上;或解析了没有执行;
Top 5 Timed Foreground Events
Event Waits Time(s) Avg wait (ms) % DB time Wait Class
library cache: mutex X 168,275,450 43,339 0 27.20 Concurrency
latch: row cache objects 3,406 40,887 12004 25.66 Concurrency
latch free 3,315 29,356 8856 18.43 Other
latch: shared pool 6,757 24,255 3590 15.22 Concurrency
DB CPU 19,967 12.53
由上可知大多事件与并发有关;
library cache: mutex X的几个产生原因:
1,high version
2,high hard parse
3,invalid and reload
4,known bugs
Host CPU (CPUs: 80 Cores: 40 Sockets: 4)
Load Average Begin Load Average End %User %System %WIO %Idle
392.25 380.41 85.4 7.5 0.1 6.6
Instance CPU
%Total CPU %Busy CPU %DB time waiting for CPU (Resource Manager)
90.8 97.3 0.0
可知cpu相当busy,80个cpu完全跑满了,撑爆了
我们先处理:library cache: mutex X等待事件
SQL ordered by Version Count
Only Statements with Version Count greater than 20 are displayed
Version Count Executions SQL Id SQL Module SQL Text
42