How to reduce Log file sync wait events
Date: Mon, 11 Feb 2008 14:27:50 -0500
We are testing the application Insert performance and following 2 events are consuming most of the wait time (33% of total time)
- Log file sync
- Log file parallel write
Test involves inserting 10M rows each in 2 tables and following is the configuration:
- 10.2.0.3 ON RHAT linuxAS
- 8 cpu
- 32GB RAM
- 12GB buffer cache
- 1.5GB Shared_pool
- 6 Application clients
- Redo block size is 512 bytes
- Redo buffer cache is 32MB (It was 16MB but after increasing it to 32MB(Oracle round it to 42MB) we are able to reduce wait on redo allocation latch)
Test is taking about 10min and AWR report shows that we are generating 45MB of log per second and iostat also shows all the write activity on LUN containing log files. Hardware can support about 300MB per second.
If we increase the clients to 8 it degrades the performance so 6 clients is the optimal that we can get with current configuration.
When we set the parameter _disable_logging=true test ran in about 8 min so 20% of total time is going in writing to redo log files and most of the other time is in LGWR and application processing.
- What is the default value of parameter _log_io_size on 10.2.0.3 and we are wondering if other people are using this parameter and what are the findings so that we can get the idea whether it is worth changing this parameter?
- Do we need to set the following parameter or this parameter is obsolete since we are getting error:
ORA-32003: error occured processing parameter 'FILESYSTEMIO_OPTIONS'
In the above article writer mentioned the following quote "In fact, I never could understand the fascination with LGWR's I/O since it is about as simple as it gets: sequential writes to a single file of sizes ranging from 512 bytes to a port-defined maximum (usually 128KB but often 1MB)."
In this statement what is meant by "port-defined maximum", Which setting it is referring to?
4) What are the other options that we can try to improve the performance of LGWR or in other terms reduce the wait time of the above log events?
--HarvinderReceived on Mon Feb 11 2008 - 13:27:50 CST