Re: Batch performance

Posted by ErmanArslansOracleBlog on Jan 30, 2024; 9:30am
URL: http://erman-arslan-s-oracle-forum.124.s1.nabble.com/Batch-performance-tp12269p12289.html

*I couldn't see anything that supports your theory here..
LGWR I/O perf looks good.
However; you do lots of commits.. ( transaction per second and user commits per seconds are almost equal)
Frequent commits in between each transaction/DML can cause the foreground session to wait for "log file sync" event.
Just tell your developers to remove the unnecessary commits from their PL/SQL blocks..

Also from MOS (Ref : Oracle) ->

If there are lots of short duration transactions, see if it is possible to group transactions together so there are fewer distinct COMMIT operations. Since it is mandatory for each commit to receive confirmation that the relevant REDO is on disk, additional commits can add significantly to the overhead. Although commits can be "piggybacked" by Oracle, reducing the overall number of commits by batching transactions can have a very beneficial effect.
See if any of the processing can use the COMMIT NOWAIT option (be sure to understand the semantics of this before writing application code to use it).
See if any activity can safely be done with NOLOGGING / UNRECOVERABLE options.


*You got high log file switch count.. Investigate it.. If necessary you may consider increasing your redo sizes. (having lots of log switches will have an affect on the time foreground process will need to wait for 'log file sync' waits to complete because the overhead of initiating the sync operation more than necessary)

*Check ->
Exadata:

Page 11 of following pertains to not putting redo on SSD/Flash storage except with Engineered Systems.

http://www.oracle.com/technetwork/database/exadata/exadata-smart-flash-cache-366203.pdf

General Ref.:
Troubleshooting: 'Log file sync' Waits (Doc ID 1376916.1)
How to interpret AWR when there are Log File Sync Waits due to high number of COMMITS (Doc ID 2277523.1)