Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2009
    Location
    Sacramento, CA
    Posts
    5

    Question Unanswered: Query uses index 98% of the time

    Hi experts! Riddle me this.....

    My system is very, very small. It only has 17 tables and about 20 users.

    I have a query that gets executed many times throughout the day. 98% of the time the query uses an index that I created to specifically make the query more efficient. The other 2% of the time the query uses a tablescan. The SQL is always executed dynamically.

    My developer recently added FOR FETCH ONLY WITH UR to the query.

    My system is such that it would be quite possible for one user to be updating a particular row while a different user is viewing that same row.

    My suspicion is that because we are doing a dirty read with this query, if another user happens to be updating that row at the same time, the optimizer says "Hey, I need to update the index as well, so I'm not going to read the table using the index."

    I'm new at this DBA stuff. Do you think that my suspicion is correct?
    Last edited by Sheerah; 10-20-09 at 17:42.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I would doubt that. If the query has literals in the predicate (WHERE clause) DB2 can check the value against the statistics to estimate how many of that value exist in the database and adjust the access plan accordingly. If you don't want DB2 to do that, then get rid of the distribution stats for the columns for the table that are stored in the catalog (and don't ask for distribution stats when you do runstats).

    However, there is good chance that when DB2 is doing a table scan, that is the most efficient access path for that query (assuming you have accurate stats).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2009
    Location
    Sacramento, CA
    Posts
    5
    Hi Marcus. I do have literals in the WHERE clause and my stats should be accurate.

    I don't know what "distribution stats" are. My RUNSTATS looks like this:

    RUNSTATS TABLESPACE LIST PRDLIST3
    TABLE (ALL)
    INDEX (ALL)
    SHRLEVEL CHANGE
    REPORT YES
    UPDATE ALL

    Anyhow, off to read about distribution statistics now.

  4. #4
    Join Date
    Jan 2009
    Location
    Sacramento, CA
    Posts
    5
    Quote Originally Posted by Marcus_A
    I would doubt that. If the query has literals in the predicate (WHERE clause) DB2 can check the value against the statistics to estimate how many of that value exist in the database and adjust the access plan accordingly. If you don't want DB2 to do that, then get rid of the distribution stats for the columns for the table that are stored in the catalog (and don't ask for distribution stats when you do runstats).

    However, there is good chance that when DB2 is doing a table scan, that is the most efficient access path for that query (assuming you have accurate stats).
    After reading about distribution statistics, it seems to me that it is best to collect them. (I'm still trying to figure out if we're collecting them or not.) However, I don't understand why, when executing the exact same query, just a few hours apart, one execution would use the index and one execution would not.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I didn't realize until now that you are using DB2 z/OS. Also, I didn't realize it was the exact same query (with exact same literals) each time. So I am not sure about why that is happening.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    is this a dynamic query or something that is bound to the DB? How are you determining that it is not using the index, are you performing an explain or looking at some monitor?
    Dave

  7. #7
    Join Date
    Jan 2009
    Location
    Sacramento, CA
    Posts
    5
    Quote Originally Posted by dav1mo
    is this a dynamic query or something that is bound to the DB? How are you determining that it is not using the index, are you performing an explain or looking at some monitor?
    Dave
    This is a dynamic query, Dave. I am running an explain that tells me if the query uses any indexes or if it does a tablescan.

  8. #8
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    You don't mention which version of DB2 you are running or platform, but based off the runstats that you showed above, we determined you are on Z/OS. I would suggest you try collecting freqval values for your indexes. Also, You may want to ask your question on the DB2-L listserv on idug.org as you may get an accurate explanation from Pat Bossman or Terry Purcell over there.
    I can't think of any reason the exact same query with the exact same literals being supplied would give you different access paths, unless runstats had been run in between the times you explained the query. If, however, it were the same query with different literals being used it could be explained that you have uneven distribution of values in that column(skewed data). For instance, 98 - 99% of the time a flag has a value of N and the remaining rows have a value of Y. The index could be chosen for the rows with a value of Y, but the optimizer would opt for a tablespace scan if the SQL was looking for the value N.
    Dave Nance

Posting Permissions

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