Log File Sync - Horrendous Waits
All,
I have an 816 Database that is subjected to a nightly batch load of data into a couple of large tables from flat files.
The flat file types are varied, and depending on their naming convention - they can get loaded in via a Korn Shell script or ProC executable.
I have examined most of the Korn Shell scripts - and they tend, on the whole, to perform commits every 1000 records (or at the end of each file).
We tend to have a maximum upper bound of 80,000 records in any one file.
Looking at the major/minor database wait times overall - immediately following a load sequence - I get the kind of results as follows:
MAJOR MINOR WAIT_EVENT SECONDS PCT
-------- ------------- ------------------------- -------- ------
CPU time parsing n/a 570 6.36%
reloads n/a 4 .05%
execution n/a 2849 31.77%
disk I/O normal I/O db file sequential read 145 1.62%
full scans db file scattered read 16 .18%
direct I/O direct path read 250 2.79%
direct path write 130 1.45%
other I/O control file parallel write 15 .17%
control file sequential read 4 .05%
db file single write 2 .02%
disk I/O other I/O control file single write 2 .02%
log file sequential read 0 .00%
waits DBWn writes rdbms ipc reply 16 .18%
LGWR writes log file switch completion 5 .05%
enqueue locks enqueue 5 .06%
other locks latch free 2 .02%
buffer busy waits 0 .00%
latency commits log file sync 3732 41.61%
network SQL*Net more data from client 946 10.55%
latency network SQL*Net more data from dblink 256 2.85%
SQL*Net message to client 4 .04%
SQL*Net break/reset to client 3 .03%
file ops file open 2 .03%
file identify 0 .00%
process ctl process startup 3 .03%
misc refresh controlfile command 5 .06%
switch logfile command 1 .01%
reliable message 0 .00%
As anyone can see - there is a an apparent massive wait-time on log file syncs.
Anyone any ideas how I might improve this kind of performance?
We have a tri-parallel system - with concurrent batch loads overnight into 3 databases. These are always a 'hot' issue in terms of performance and time taken to data availability.
:-(
- Tony
