I faced a 12c r2 database performance issue. the log file sync event high, and log file sync: scn ordring event. ADDM show
The redo log I/O throughput was divided as follows: 0% by RMAN and
recovery, 25% by Log Writer, 0% by Archiver, 0% by Streams AQ and 74% by
all other activity.
is the "74% by all other activity" by log file sync: scn ordring event?
Finding 1: Commits and Rollbacks
Impact is 122.39 active sessions, 82.82% of total activity.
-----------------------------------------------------------
Waits on event "log file sync" while performing COMMIT and ROLLBACK operations
were consuming significant database time.
Waits on event "log file sync" were the cause of significant database wait on
"gc buffer busy release" when releasing a data block.
Recommendation 1: Application Analysis
Estimated benefit is 122.39 active sessions, 82.82% of total activity.
----------------------------------------------------------------------
Action
Investigate application logic for possible reduction in the number of
COMMIT operations by increasing the size of transactions.
Rationale
The application was performing 30216 transactions per minute with an
average redo size of 2312 bytes per transaction.
Recommendation 2: Host Configuration
Estimated benefit is 122.39 active sessions, 82.82% of total activity.
----------------------------------------------------------------------
Action
Investigate the possibility of improving the performance of I/O to the
online redo log files.
Rationale
The average size of writes to the online redo log files was 5 K and the
average time per write was 0 milliseconds.
Rationale
The total I/O throughput on redo log files was 3.3 M per second for
reads and 1.1 M per second for writes.
Rationale
The redo log I/O throughput was divided as follows: 0% by RMAN and
recovery, 25% by Log Writer, 0% by Archiver, 0% by Streams AQ and 74% by
all other activity.
Symptoms That Led to the Finding:
---------------------------------
Wait class "Commit" was consuming significant database time.
Impact is 112.25 active sessions, 75.96% of total activity.
Convert to CSV
| Event | Waits | Total Wait Time (sec) | Avg Wait | % DB time | Wait Class |
|---|
| log file sync | 494,716 | 230.1K | 465.15ms | 76.0 | Commit |
| log file sync: SCN ordering | 180 | 22.2K | 123.45 s | 7.3 | Concurrency |
gc buffer busy release | 22,221 | 20.8K | 934.77ms | 6.9 | Cluster |
| buffer busy waits | 41,357 | 9685.1 | 234.18ms | 3.2 | Concurrency |
| row cache lock | 6,999 | 6468.8 | 924.25ms | 2.1 | Concurrency |
| DB CPU | | 4261.8 | | 1.4 | |
| enq: SQ - contention | 50 | 2814.4 | 56.29 s | .9 | Configuration |
| db file scattered read | 317,333 | 1271 | 4.01ms | .4 | User I/O |
| db file sequential read | 1,073,753 | 1009.7 | 940.34us | .3 | User I/O |
| enq: US - contention | 6,922 | 360.2 | 52.03ms | .1 | Other |
- s - second, ms - millisecond, us - microsecond, ns - nanosecond
- Only events with Total Wait Time (s) >= .001 are shown
- ordered by wait time desc, waits desc (idle events last)
- %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
Convert to CSV
Back to Wait Events Statistics
Back to Top
Back to Custom Top
- ordered by wait time desc, waits desc (idle events last)
- Only events with Total Wait Time (s) >= .001 are shown
- %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
Convert to CSV