Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2012
    Posts
    120

    Unanswered: what actually happens during...

    Hi,
    I'm trying to optimize some DB2 queries and I'm taking benchmarks with db2batch.

    I see timing for execute and fetch is reported in the output.

    I'd like to understand what DB2 does during the execute, and what during the fetch.

    For example, where is the I/O time (time spent loading data from disk into bufferpools) counted?

    What can be done to enhance fetch rate, given that CPU cannot be changed?

    How are these times in relationship with the total cost of the access plan?

    Thanks

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Quote Originally Posted by Robert1973 View Post

    What can be done to enhance fetch rate, given that CPU cannot be changed?

    rqrioblk - Client I/O block size configuration parameter


    http://www.dbforums.com/db2/1679748-...rformance.html
    Last edited by sathyaram_s; 03-27-13 at 17:30.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Have you seen these metrics on time spent ?

    Time-spent monitor element hierarchy


    Does this help ?

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Mar 2012
    Posts
    120
    Thanks sathyaram_s,
    I tried tuning rqrioblk but it has no effect on the query performance (by the way, the application is local to the server).

    About the second link of your first post.. I guess it's suggesting to use EXPORT.. which is not feasible in my case.

    About the metrics, could you please make an example of how you would use them if one of your queries had bad fetch performance?

    Thank you very much.

  5. #5
    Join Date
    Nov 2011
    Posts
    334
    when the first row is ready to return to client, fetch begins and ends until all rows is returned。You need to know that most operation of the executiong plan is executed in a streaming fashion. eg:
    select * from t where c1 =xxxx. the plan may looks like this:
    tablescan->filter->return.

    at first ,the first page is read from the disk to bufferpool ,then db2 get the first row from the page, and put it to the filter, when it is qulified ,fetch begins。then the second page is read and so on ( we assume there is no data prefetching )
    the time after the sql compiled and before the fetch begin is execution time.
    Sometimes execution time is very shortly.

    if you want to improve your fetch time ,why dont you provide more infomation.
    the number of db2batch's fetching time and your application's fetching time.
    how do you identify the problem is on fetching time?
    how your application fetch the resultset ?
    how your application process the record after it has been fetched?
    what is the language you used ?? c? java

Posting Permissions

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