DB version: 19.11
OS : RHEL 8.10
2-Node RAC Primary DB with standalone Standby DB (Dataguard)
Using ASM on SSD disks
Flashback logging enabled
I have a 2-Node RAC Primary DB.
One of our critical applications connect to Instance1 of 2-Node RAC cluster via a user-defined RAC service (app_xyz) whose 'preferred' instances is Instance1. Back in the day, app_xyz service used to both Instance 1 and Instance 2 as the 'Preferred Instance'.
But, we had to change this to just Instance1 because of some cluster wait issues.
So, now, during peak hours, we get high amount of 'log file sync' waits.
I have also noticed that, during peak hours, there are between 40 to 70 Online redo log switches happening per hour.
Ideally, it should be between 3 to 4.
Currently, the redo log size is 1 GB with 10 groups for each thread (2 thread).
The redo logs are multiplexed across 2 diskgroups.
For SQLs with elapsed time, I see two INSERT statements on two different tables. One of the table has a BLOB column. I assume these are the SQLs that are causing log file sync waits
Application team has informed that they cannot change the code from vendor-provided application.
Two questions:
Question1. Increasing the redo log size should reduce the "log file sync" waits. Right ?
Question2. Is there any other way to reduce the "log file sync", i.e., without increasing the redo log file size?
Below is from Node1 for a 1 hour period. You guys need anything else from the AWR report ?
