I have performance issue with Oracle instance.
There is some java process that makes massive inserting into database. And the top wait event is "log file sync". I tried to change some Oracle database parameters, but result still not satisfied users…
I'm newbie in Oracle, so please be patient 🙂
Settings that I changed:
- MEMORY_TARGET = 34359738368
- MEMORY_MAX_TARGET = 34359738368
- OPTIMIZER_MODE = FIRST_ROWS
- OPTIMIZER_USE_PENDING_STATISTICS = TRUE
- COMMIT_WRITE = BATCH, NOWAIT
- REDO LOG FILES = 3 x 1000MB
- _use_single_log_writer = TRUE
- commit_logging=batch
- commit_wait=nowait
Things that I can't change (unfortunately):
- RAID 5
- Java code
- count of commits
- SQL code
- Also I can't disable logging
Best Answer
If your archiver is not keeping up. you should probably create smaller online redo log files, hence they will be faster to archive, and more online redo log groups. Try having 10 250MB online redo log groups and see if that works better.
You may want to check the IO sub system to see what kind of throughput you are getting. In 11gR2 and 12cR1 Orion is already installed. You should be able to take a copy of a datafile and trash it with Orion to find out what the reads and writes look like.
Using Orion Calibration to check I/O numbers
As a separate thought, can you create a new volume for the temp and online redo logs with mirroring? It could be that no one here can help you if your issue is related to your storage sub system, or code that commits way too often. You should look into changing the things that you said that you can't change, since that could be where the issue is.
I would not say that you can't do anything. You can look for poor performing SQL and see if you can add an index. You can look at the large insert statements and see what constraints, indexes and most likely triggers exist and see if there is something there that is causing an issue. How many tables are getting inserts, and do they have row by row triggers. If so start tuning there.