Tags: advance, apologize, commit, database, difficult, event, file, following, log, mysql, operati, oracle, performing, receiving, rollback, search, sql, sync, waits
Waits on event "log file sync" while performing COMMIT and ROLLBACK operati
I am sure this question has been asked before, but these forums are difficult to search ,so I apologize in advance.
I am receiving the following message in an ADDM report. Can someone help me understand how to better improve I/O performance? Is this a hardware issue or can I tune some parameters in ORACLE to make this perform better?
FINDING 1: 67% impact (2230 seconds)
Waits on event "log file sync" while performing COMMIT and ROLLBACK operations
were consuming significant database time.
RECOMMENDATION 1: Application Analysis, 67% benefit (2230 seconds)
ACTION: Investigate application logic for possible reduction in the
number of COMMIT operations by increasing the size of transactions.
RATIONALE: The application was performing 2718 transactions per minute
with an average redo size of 8015 bytes per transaction.
RECOMMENDATION 2: Host Configuration, 67% benefit (2230 seconds)
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 8
K and the average time per write was 7 milliseconds.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "Commit" was consuming significant database time.
(67% impact [2230 seconds])
Leave a comment...
- 4 Comments
- 9 times out of 10, this is an application issue. Somewhere in your code, someone is issuing very frequent commits, which forces Oracle to do a lot of work. Check the application to see whether it is really committing at the end of logical business transactions rather than doing something silly like committing in a FOR loop or running JDBC applications with autocommit on.
Distributed Database Consulting, Inc.#1; Sat, 23 Feb 2008 12:16:00 GMT
- Thanks Justin.
The application does seem to be committing after every update. The app uses ODBC and the Oracle 10g ODBC driver to perform database access.
Is there anything that can be done on the ORACLE side to improve this performance? I have ran a test of the application on MSSQL and it runs significantly faster. Like hundreds to thousands of times faster.
Brian#2; Sat, 23 Feb 2008 12:17:00 GMT
- Not shocking, commits in Oracle are much more expensive than commits in SQL Server, owing to the multi-version read consistency Oracle provides. Committing after every single-row update is a very good way to cause Oracle to grind to a hault.
The right answer here is to tune the application so that it commits at the end of a business transaction, whatever that is, rather than after every statement.
Distributed Database Consulting, Inc.#3; Sat, 23 Feb 2008 12:18:00 GMT
- Jonathan has a good point about few top 5 timed events, including log file sync.
Jaffar#4; Sat, 23 Feb 2008 12:19:00 GMT