Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

how to analyze "log file sync" wait event

Simon SouvJan 5 2023

Good morning community,
One of our client is planning to move from oracle 11 EE to oracle 19 EE without chaning our application.
In order to analyze the impacts, I did the same operation and check how an existing test will behave on oracle 19 EE.
The test consists in inserting data (trades) in the system and measure the elapsed time
In terms of infrastructure we have
One server for our application
One server hosting Oracle 11
One server hosting Oracle 19
An expdp of the schema prior to execute the test. This backup is loaded on Oracle [11 | 19] then a gather_schema_stats is executed. By doing so I try to make sure the conditions are the same for any test iteration
Results show regression (that is where the fun part start, isn't it?), big difference

|baseline |~36mins  |
|oracle 19|~1h46mins|

Any kinds of DML look slower
an AWR report is generated after the test and when I compare both html outputs I can see a new foreground wait event (top2, top 1 event is DB CPU for almost 99% in both cases)

|event        |waits    |total waits(s)|avg waits|%DB time|wait class|
|log file sync|1,902,979|1490.9        |783.47us |1.2     |commit    |

Therefore I'm trying to find out the root cause of this and this is where I'd need your expertise.
From a server point of view I have the following

|              |baseline                         |oracle 19                           |
|cpu           |Xeon(R) CPU E5-2697A v4 @ 2.60GHz|Xeon(R) CPU E5-2697 v3 @ 2.60GHz    |
|hyperthreading|disable                          |disable                             |
|cores         |32                               |28                                  |
|os            |RHEL release 6.6 (Santiago)      |CentOS Linux release 7.6.1810 (Core)|

/etc/sysctl.conf is aligned on both servers

Regarding Oracle server setup, I tried to 'mimic' the oracle 11 parameters on oracle 19 parameters, that is compare 'show parameters' outputs and set oracle 11 value on oracle19 value when possible
In terms of memory, I have the following values

|                              |baseline      |oracle 19     |
|SGA (Total System Global Area)|94 871 552 000|95 294 583 264|
|Buffer cache                  |85 899 345 920|85 899 345 920|
|Shared pool                   |4 831 838 208 |4 831 838 208 |
|Undo tablespace               |195 600       |195 584       |

In terms of ASM setup I have the following (please note that each schema is using a tablespace created on one datafile)

|                      |baseline   |oracle 19|
| asm group            |2          |2        |
| asm group +DATA disks|6          |4        |
| asm group +REDO disks|4          |4        |

Today, my question are:
Could the difference come from the CPU differences? I guess it impacts but not to that level
I'm suspecting IO, especially if I look at the load profile section in both awr reports. I'm surprised of such difference knowing I'm using SSD on oracle 19EE so I was expecting better throughput

|                       |baseline per second|oracle19 per second|
|disk type              |HDD                |SSD                |
|Physical read (blocks) |3.0                |1.4                |
|Physical write (blocks)|395.3              |23.3               |
|Read IO requests       |2.9                |1.2                |
|Write IO requests      |92.2               |23.2               |
|Read IO (MB)           |0.0                |0.0                |
|Write IO (MB)          |3.1                |0.2                |

I thank you in advance for reading this and for any suggestions

Comments
Post Details
Added on Jan 5 2023
22 comments
2,087 views