Hi All,
I have Oracle 12c(12.1.0.2) Standard Edition database, where i am facing log file sync wait events. Below is the information from statspack.
STATSPACK report for
Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
3009339762 dlssdb 1 25-Jun-19 04:16 12.1.0.2.0 NO
Host Name Platform CPUs Cores Sockets Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
medinquirerdb.th Linux x86 64-bit 4 4 1 15.4
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- ------------------
Begin Snap: 68 27-Jun-19 14:00:26 55 3.8
End Snap: 74 27-Jun-19 16:00:06 77 6.8
Elapsed: 119.67 (mins) Av Act Sess: 1.8
DB time: 212.81 (mins) DB CPU: 6.27 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 3,648M Std Block Size: 8K
Shared Pool: 572M Log Buffer: 10,240K
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
DB time(s): 1.8 2.7 0.18 0.13
DB CPU(s): 0.1 0.1 0.01 0.00
Redo size: 971,922.9 1,470,686.3
Logical reads: 6,030.5 9,125.2
Block changes: 5,533.9 8,373.7
Physical reads: 0.3 0.4
Physical writes: 90.9 137.6
User calls: 14.1 21.3
Parses: 5.7 8.6
Hard parses: 0.1 0.1
W/A MB processed: 2.0 3.0
Logons: 0.1 0.2
Executes: 9.7 14.7
Rollbacks: 0.0 0.0
Transactions: 0.7
Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.99 Redo NoWait %: 99.57
Buffer Hit %: 100.00 Optimal W/A Exec %: 100.00
Library Hit %: 98.69 Soft Parse %: 99.10
Execute to Parse %: 41.76 Latch Hit %: 99.53
Parse CPU to Parse Elapsd %: 83.28 % Non-Parse CPU: 99.31
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 92.17 90.05
% SQL with executions>1: 71.09 76.74
% Memory for SQL w/exec>1: 82.05 86.48
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
LGWR worker group idle 45,279 13,560 299 26.6
log file switch (checkpoint incomplete) 528 7,613 14419 15.0
AQPC idle 240 7,202 30008 14.1
lreg timer 2,392 7,181 3002 14.1
heartbeat redo informer 7,178 7,179 1000 14.1
-------------------------------------------------------------
Host CPU (CPUs: 4 Cores: 4 Sockets: 1)
~~~~~~~~ Load Average
Begin End User System Idle WIO WCPU
------- ------- ------- ------- ------- ------- --------
0.17 0.11 2.24 0.65 97.11 6.64