Log File Sync - Horrendous Waits
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Log File Sync - Horrendous Waits

  1. #1
    Join Date
    Nov 2001
    Location
    Sheffield, England, UK
    Posts
    78

    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

  2. #2
    Join Date
    Nov 2001
    Location
    Sheffield, England, UK
    Posts
    78
    Hmmm...

    Lost all the formatting in there!

    I'll attach from a cut and paste into notepad instead!


    -T.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width