Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    61

    Unanswered: Why DB2 keeps using the old number in building Access Plan?

    Hi everyone,

    When I build an access plan for a SQL query, it does not use an index created even I run RUNSTATS on that table. When I check the sort operation on the access plan, it states that the number of rows in the table is 1,345,227. But from Control Center "Estimate Size...", it shows the table contains 12,107,043. Even I try couple times the access plan, it keeps use the old number and does not use the index and keeps sorting the table. What's problem with the DB2 access plan?

    Thanks,
    Last edited by hiolgc; 12-16-04 at 16:02.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I am not clear on what you are asking. Is the problem that DB2 is not using an index that you think it should use, or is there a question about the number of rows in the table?

    The number of rows sorted (or estimated to be sorted in the explain) is not necesarily the number of rows in the table. Nevertheless, it would be very unusual for DB2 to not use an index on a table with 1,345,227 rows if it would use an index on the same table (and same SQL) with 12,107,043 rows.

    If you want help figuring out why an index is not used, please post the DDL of the table and all indexes (the complete DDL using db2look), and please post the exact SQL satatement in question.
    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
    Mar 2004
    Posts
    61
    Thanks,
    There are four tables in the query. The access plan uses the other two indexes created (much smaller tables), but not the index for the big table. Even the query executing time dropes to 3 minutes from 4, the estimated cost becomes 2,199,096 from 2,036,601 after creating three new indexes (two were used in the access plan).

  4. #4
    Join Date
    Jul 2003
    Location
    Austin, TX, USA
    Posts
    278

    Runstats.

    Hi.

    I shouldn't be asking this but did you do a runstats after you created the index. Try also doing a reorgchk and see what it suggests.

    Please go ahead and try using the db2advis utility and I am sure you will have a good idea of what to do ahead.

    Sometimes db2 optimizer finds it relatively cheaper to avoid the index depending on the algorithm it generates using certain internal routines. What I suggest is that you try setting the optimization class for the specific query and see if you get better results.

    These are just some suggestions which may help.
    HTH

    Nitin

    Ask the experienced rather than the learned

  5. #5
    Join Date
    Sep 2003
    Posts
    85
    Is this a DPF (aka EEE) environment?
    If so, there's a logical explanation why the stats don't seem to jive. Since UDB assumes equal distribution amongst the nodes/database partitions, so if there's data skewing amongst the data partitions, it will be take the number of rows located in the node which it's executing the RUNSTATS and multiply that number by the # of data partitions for the table's (tablespace's) database partition group.
    There is a VERY recent IBM article located at:
    http://www-106.ibm.com/developerwork...pay/index.html
    detailing the ins-and-outs of RUNSTATS.

    In the above scenario, should RUNSTATS execute against the partition where, due to data skewing, there are 0 rows for the given table, CARD would show 0 even if the table has millions of rows.

    HTH,
    Ruby

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you post the exact DDL and SQL as I asked, I am sure that the problem can be explained or corrected. If you don't post the information requested, then everything is pure speculation and is usually a waste of time.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Mar 2004
    Posts
    61
    Thanks for every one who responsing.

    I run db2advis, and get the following result:

    execution started at timestamp 2004-12-20-14.34.50.453926
    found [1] SQL statements from the input file

    Calculating initial cost (without recommmended indexes) [2,199,096.000000] timerons
    Initial set of proposed indexes is ready.
    Found maximum set of [5] recommended indexes
    Cost of workload with all indexes included [2,196,167.250000] timerons
    total disk space needed for initial set [ 16.614] MB
    total disk space constrained to [ -1.000] MB
    excluding index WIZ1. diskspace required now is [ 11.109] MB
    3 indexes in current solution
    [2199096.0000] timerons (without indexes)
    [2196721.2500] timerons (with current solution)
    [%0.11] improvement

    Trying variations of the solution set.
    Maximum number of variations reached.
    --
    -- execution finished at timestamp 2004-12-20-14.34.51.454562

    All the indexes recommeded by db2advis does not include the one on the big table. Even I drop this index, it does not affect the query's performance. So I guess this index is useless to the query. And no more index is needed for this query because even creating the four recommended, the performance increased is little.

    Thanks again and have a safe and happy holiday season,

Posting Permissions

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