Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2013
    Posts
    4

    Unanswered: Query runs slowly only the first time

    But thereafter runs were fast, I guess it's cached. So what's the possible reasons? I have increased the applheapsz, but did not help.

    DB2 v9.7 on RHEL with single-partitioned instance

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    My guess would be that your query causes much disk I/O. Check the query plan to see where most I/O happens and whether you can minimize disk reads or writes.
    Last edited by n_i; 05-01-13 at 10:41.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Nov 2010
    Posts
    99
    The pages are in the bufferpool - this is why it's faster the second time.

    If you can run the query using db2batch like this:

    db2batch -i complete -d <your db> -f <SQL file>

    It will show the elapsed time for the 3-phases - prepare, fetch, execute. Your slowdown is probably with execute.

    To do the test correctly you need to capture the 1st run performance. If the query has already run then the pages are in the bufferpool and you will need to clear those out before running db2batch - since the runtime will be skewed. If you can deactivate and reactivate the db to clear out the bufferpool. Else change the SQL slightly or simply run the SQL if in another db - that it hasn't run already.

    Please post the results.

  4. #4
    Join Date
    Apr 2013
    Posts
    4
    Here are the outputs: looked like the fetch time the bottle neck in the first time run. The subsequent run was apparently much faster.

    $ db2batch -i complete -d <db> -f qry.k2.call
    ...
    * 1 row(s) fetched, 1 row(s) output.

    * Prepare Time is: 0.034604 seconds
    * Execute Time is: 0.236564 seconds
    * Fetch Time is: 4.268504 seconds
    * Elapsed Time is: 4.539672 seconds (complete)

    Subsequent run:
    ==================
    * 1 row(s) fetched, 1 row(s) output.

    * Prepare Time is: 0.000176 seconds
    * Execute Time is: 0.067774 seconds
    * Fetch Time is: 0.005452 seconds
    * Elapsed Time is: 0.073402 seconds (complete)

  5. #5
    Join Date
    Nov 2010
    Posts
    99
    Please post the 2nd runtime? - when it's much faster.

  6. #6
    Join Date
    Apr 2013
    Posts
    4
    The bottom sets of output after ================

  7. #7
    Join Date
    Nov 2010
    Posts
    99
    Sorry

    Okay so the db is having a hard time finding the page on disk.

    If you can try a REORG and RUNSTATS then rerun tests 1 and 2.

    Seems like the table is not clustered well for the index its using.

    Does the access plan show just indexes or is there a fetch from table, sorts, etc? Maybe post the SQL.

    Is this an OLTP - i see you returned one row.

    What is the page size for the table and index?

    Is there a clustered index on the table?

  8. #8
    Join Date
    Apr 2013
    Posts
    4
    thanks for your reply. That's OLTP, both tablespaces are 4 k.

    I will tried to explain and reorg, then will post the resuts.

  9. #9
    Join Date
    Nov 2010
    Posts
    99
    Before running the REORG run REORGCHK with CURRENT STATISTICS keyword so you have a "before" look of the statistics on this table. Also post this

Posting Permissions

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