Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2012
    Posts
    82

    Unanswered: DB2 Explain Nested Loop Join

    Hi,
    I am trying to optimize a query. We have a query that has joins between 5 tables.

    The problem I am getting is an inner join between 2 tables. The join predicates are indexed and it is an inner join but for some reason the database is doing a Nested loop join and it is taking shit loads of time.

    One table has 391 rows and the other has 5.4 million rows.. Its a join between a DIM and a Fact table.


    The whole query takes 144,200 timerons but this operation alone is taking 135,000.. Any ideas why.. The explain snapshot is attached.

    I am running DB 9.7 on SUSE 10 with 10 GHZ and 18GB of RAM.
    Attached Thumbnails Attached Thumbnails explain.JPG  

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    There is not enough information. You should show the entire access plan. That might explain the reason for the NLJOIN. You should also provide the DDL for all tables and indexes as well as the query itself.

    Andy

  3. #3
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    You might also want to check the impact on the access-plan of ensuring your runstats are both up to date, and have 'detailed indexes' (or 'sampled detailed indexes' as appropriate). Depending on your query, distribution statistics may also help. Check what indexes and stats the db2 advisor recommends for the query.

  4. #4
    Join Date
    Jan 2012
    Posts
    82
    Done all the runstats.. distribution and sampled detailed as well.. still nothing

    Attached are the sql and the explain..
    Attached Files Attached Files

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Nested Loop Joins are usually the fastest. Maybe you are missing an index?
    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
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Any reason why you use optlevel 1:

    ---------------
    SQL Type: Dynamic
    Optimization Level: 1
    Blocking: Block All Cursors
    Isolation Level: Cursor Stability

  7. #7
    Join Date
    Jan 2012
    Posts
    82
    this is the one that is giving us the quickest data result for some reason. I also tried using 5 and 9 and the execution cost and plan derived was the same...

    Marcus... I know but we have all indexes on all join columns still it is messing it up and taking loads of time...

    95% execution on just one join while the rest of the joins are working fine :/

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I have heard of people using optimization level 3 for OLTP, but using 1 is a very bad idea. For this particular query you should do an explain using several different optimization levels such as 3.5, and 7. You can set the optimization level just for the explain.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Consider using statistical views on the joins between the fact table and dimensions -- they will give the optimizer an idea of the possible correlations.
    ---
    "It does not work" is not a valid problem statement.

  10. #10
    Join Date
    Jan 2012
    Posts
    82
    This query is actually a view it self.. Can I just turn on query optimization for this view and gather statistics for it or should I create a separate statistical view ?

  11. #11
    Join Date
    Nov 2010
    Posts
    99
    When selecting from the fact table try an ORDER BY ID., and perhaps on the some of the other trouble tables. Also are there any clustered indexes on any of these tables, if so on which column? Are these table all using 4K tablespaces? I noticed your temp page size is 4K.

  12. #12
    Join Date
    Jan 2012
    Posts
    82
    The clustering index is on the ID column.. All joins are on that columns in Dim tables...

    In Facts its the same but I was thinking of changing it to Date_DIM_ID as our reporting is on month level and reporting is done on month data as well.. Any idea if it would be a better approach?

    and yes all of these tables in this query are 4K.

  13. #13
    Join Date
    Nov 2011
    Posts
    334
    Why are you worry about the nljoin?
    From the access plan , we can't see any problem with the nljoin
    ( the step 104,105,196,115)。the out table of the nljoin is very small
    ( about several hundred rows)。

    and how many rows did you get before the final group by operation?
    the step 4 ( sort operation ) shows it will handle the 3.55786e+08 rows,
    if the cardinalities estimated by db2 is accurate, that will take too much time。

    and plz get the opimization class back to level 5。level 1 will disable some function (eg, list prefech ,hash join) which is very usefull in olap environment.

    if you could , you can use the db2caem to get the section acutual which will
    help you to identify which step is under/overestimated by db2 optimizer.

  14. #14
    Join Date
    Nov 2010
    Posts
    99
    I would keep the clustered index on the ID, especially if the ID is sequential - the most recent rows also have the highest ID.

    From a timeron/index perspective the plan looks pretty good, although I prefer looking at the visual explain.

  15. #15
    Join Date
    Nov 2010
    Posts
    99
    Also regarding the 4k page, you would have less i/o if the data is on a larger page like 32K and keeping the index on 4k. This would not be good solution in an OLTP environment where you normally want 1 row, but if you are grabbing 1000's of rows with many fetches then there would be a benefit. Of course there's a AVG row size consideration when choosing the page size.

Posting Permissions

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