DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2001
    Sheffield, England, UK

    Log File Sync - Horrendous Waits


    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:

    -------- ------------- ------------------------- -------- ------
    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
    Sheffield, England, UK

    Lost all the formatting in there!

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

    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

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.