DBAs,
We recently got a lot of complaints about one database for its slowness.
I took a snapshot during the slow time whose time window is 15 minuets.
From the stats report, in the top 5 waits, we saw the log file sync.
How can we know it is abnormal or normal?
The important part in the report is as follows:
Code:
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.24 In-memory Sort %: 99.96
Library Hit %: 85.99 Soft Parse %: 44.28
Execute to Parse %: 15.49 Latch Hit %: 98.70
Parse CPU to Parse Elapsd %: 89.44 % Non-Parse CPU: 55.74
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
log file sync 61,458 7,386 47.32
latch free 20,065 3,686 23.61
db file sequential read 17,752 1,641 10.51
log file parallel write 56,093 1,610 10.31
db file parallel write 1,610 908 5.82
------------------------------------------------------
Event Waits Timeouts Time (cs) (ms) /txn
---------------------------- ------------ ---------- ----------- ------ ------
log file sync 61,458 0 7,386 1 1.0
latch free 20,065 15,166 3,686 2 0.3
db file sequential read 17,752 0 1,641 1 0.3
log file parallel write 56,093 0 1,610 0 1.0
db file parallel write 1,610 0 908 6 0.0
Statistic Total per Second per Trans
--------------------------------- ---------------- ------------ ------------
CPU used by this session 35,105 39.0 0.6
Any idea from this report?
Thnaks for the help.