Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2007
    Posts
    7

    Unanswered: High row reads issue

    Hello everyone, first time posting here and just browsing around it looks like a great community

    First the Versions :
    DB2 v8.1.1.128
    Type DB2ESE
    Running on AIX

    One of our customers was noticing an extremely slow response speeds. I ran some monitoring and the snapshot of the database revealed a dramaticly increasing rows read. I'm talking in the region of 3-4 million an hour! This is far too much for the system.

    Is there a way of tracking down which is the offending table or query that is causing this?

    Many thanks,

    Darren Bridle
    Last edited by dbridle; 12-11-07 at 10:29.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Yes. Use the snapshot monitor for Dynamic SQL or Table to get this info.

    You can use the freeware monitor application located at:

    http://chuzhoi_files.tripod.com/index.html

    It is very useful and you can sort the columns to find those with the most rows read.

    Andy

  3. #3
    Join Date
    Dec 2007
    Posts
    7
    ARWinner thanks for your reply.

    Problem is the database has hundreds of tables so it would be like looking for a needle in a haystack to use snapshot on a table. Also I have no idea what the offending sql statement is that would cause it. Maybe I do need to go through every table?

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Download and use the monitor I mentioned earlier. It has separate sections for SQL and Tables among others. You can then sort the lists presented by rows_read and quickly see the objects with the most.

    Andy

  5. #5
    Join Date
    Dec 2007
    Posts
    7
    I can't use any software like that due to remote access to the aix box through an F5

    It would have to be a command line based query.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You can also use SQL to get the snapshot data, sort it the way you want, and then fetch the first n rows.

    Andy

  7. #7
    Join Date
    Dec 2007
    Posts
    7
    Most of the high reads, are 1 statement reading upwards of 7,000 reads. I can understand that maybe it should be an indexed field and so on to reduce hte reads, interestingly though there are 0 sql executions?

    Any reason why so many reads without an execution?

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I think it is how snapshots work. You probably have the the monitor switches turned off by default. So the numbers are not being accumulated until you run the snapshot. If you keep the session live for the snapshots, and turn on the monitor switches, and run subsequent snapshots, you should see the executions go up as time progresses.

    Andy

Posting Permissions

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