DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Periodic high io wait on db

  1. #1
    Join Date
    Jan 2001
    Posts
    216

    Angry Periodic high io wait on db

    We have an application that inserts about 100 records into the db in a batch (using JDBC) every 60 seconds. When the application starts off, things go smoothly. DB cpu usage is 0% used and spikes for a few seconds every minute when the insert batch comes in.

    However, after about 15-20 mins, the spikes start staying high for a longer period. It starts getting progressively worse and after about half a day, the spike never comes back down. The spike is mainly an iowait of about 50% average.

    I took a statspack report of the db during one such spikes and have attached it here. From my limited understanding, I cant say "Bingo!" looking at it. There are very few queries on the db, on small tables and the inserts I mentioned about. I cant figure out whats wrong. Could anyone please help!!

    I also did a 10046 trace on all user sessions on a level 12, no other major queries running other than the inserts.

    Thanks in advance..
    Attached Files Attached Files

  2. #2
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Your statpack output is unreadable. And pls don't ask me how, as myself how(haven't tried) to format it here.

    Is your RBS/UNDO is properly configured? you know for long inserts.

  3. #3
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    db file sequential read 1,774 150 55.41
    db file parallel write 21 63 23.31
    TOo much PIO.Got to tune sqls Guv'. Other things seems to be normal
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

  4. #4
    Join Date
    Jan 2001
    Posts
    216
    The only sqls running at this point should be:
    1. the insert statement
    2. few selects from small tables

    Hence cant imagine them causing constant 50% io wait...

    On the other note, the undo tablespace in this db is very huge. I will reduce its size today and observe the results.

  5. #5
    Join Date
    Jan 2001
    Posts
    216
    Could the high "db file sequential read" and "db file parallel write" be due to the large undo tablespace? I checked our init.ora and we have an unusually large value for unto_retention parameter..

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    I noticed that the log buffer is 20 megs. Is this intentional? Anything more than a megs is a waste. Could it be that you are being held up by the logwriter?

    configuring the redo log buffer

    This is the insert statement that stands out to me.

    Code:
      
    Buffer Gets Executions Gets per Exec %Total Time(s) Time(s) Hash Value
    ----------- ---------- ------------- ------ ------- ------- ----------
        343,105         87       3,943.7   99.1    5.91  158.90 2074610996
    Module: JDBC Thin Client
    insert into network_calllog(calllog_id,rsrc_id,calltype,uri,disp
    lay_name,duration,date_made,call_id) values(prov_svr_seq.nextval
    ,:1,:2,:3,:4,:5,TO_DATE(:6,'YYYY/MM/DD HH24:MI:SS'),:7)

  7. #7
    Join Date
    Jan 2001
    Posts
    216
    I had increased the log buffer value during this test to see if it made any difference.

    Yes, the insert statement that you printed out is the one thats being run several times. But its a regular insert statement, has 7-8 columns. Only thing is, the table it is inserting into is huge (4 million rows already).

    What I cant explain is the odd behavior. The app starts out fine with regular spikes during the insert period initially and then the spikes keep staying on for a longer and longer period of time. Is this expected behavior?

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Originally posted by chikkodi
    I had increased the log buffer value during this test to see if it made any difference.

    Yes, the insert statement that you printed out is the one thats being run several times. But its a regular insert statement, has 7-8 columns. Only thing is, the table it is inserting into is huge (4 million rows already).

    What I cant explain is the odd behavior. The app starts out fine with regular spikes during the insert period initially and then the spikes keep staying on for a longer and longer period of time. Is this expected behavior?
    Do an explain plan on the insert. When you insert data, Oracle needs to maintain the indexes that go along with that data. Do you have a bitmapped index on that table? What indexes and constraints do you have on that table? Look to see what insert triggers you have. If you have an insert trigger querying another table with a full table scan, inserts will have a higher cost.

  9. #9
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    From a hardware perspective, what OS, disk and file system?

    Are you using Async or Direct I/O?

  10. #10
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Trace the process. This way you will be able to see how much time is being spent on FK checking.
    Jeff Hunter

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