Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2005
    Posts
    73

    Unanswered: SQL Tuning -- VERY URGENT

    OS: AIX UDB 8.2

    All,

    I am having some tough time with the SQL tuning. I have a couple of Reporting SQLs which are performing very slow.

    I took the explain of the SQLs . But I can't understand it .

    I see a table is using and TBSCAN , even though it has indexes. what could be the raason.

    How do i change HSJOIN to NLJOIN.


    I See a few more TBSCANs after the SORT operation. How do i figure out which is causing TBSCAN in this scenario.




    thanks
    Attached Files Attached Files
    Last edited by dba_udb; 10-10-06 at 17:07.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    How about posting the SQL? Include what your intent of the SQL.

    Andy

  3. #3
    Join Date
    Mar 2005
    Posts
    73
    Here is the SQL.





    1) ois.ord_item_id has an index . But it is not being used. This index was created on two columns one being ois.ord_item_id.
    2) Is this a good idea to create indexes on all the columns of GROUP by function.
    3) I see few TBSCANS after a sort operation in Graph. What does it mean.

    Can some one refer me good documentation for understanding Visual grapsh.
    Last edited by dba_udb; 10-05-06 at 11:52.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Tutorial on Visual Explain:

    http://users.sdsc.edu/~jrowley/db2/V...l%20explain%22

    Can you post the DDL for the tables? Have you done RUNSTATS in the tables?

    Andy

  5. #5
    Join Date
    Jul 2002
    Posts
    48
    HSJOIN is better than NSLOOP JOIN. If you have two big table i will prefer to have HSJOIN. Only problem is for doing HSJOIN we need more memory. Apart from that check the predicates of all the HSJOIN, if you create the index on that it might improve the performance. Moreover try to do runstats on all the tables with indexes all and distribution which might also improve the performance.

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You may also want to consider posting the explain plan and the statistics ... output of db2exfmt with the -g option may do good ...

    Having a quick look at your query (at 2 am ;-) ) , these are my observations :

    a) You have a couple of outer joins ... So, no wonder you get tablescans

    b) I think it may be a good idea to 'apply' some of your conditions in the where clause before you do the left outer join (or may be, db2 already does this optimization)

    For your questions :
    1) previous paragraph answers this
    2) no, given that you have too many columns in the group by.
    3) the sorted table is being read

    Nageswaran, in my opinion, trying to make your NSLOOP joins HSJOINS or vice-versa is not advisable ... Many people have an opinion that Nested-Loop joins are bad, which I generally differ with .. They get really bad if the join columns are not indexed ... To me HSJOINs on base tables mean that there is a scope for index improvements ...

    HTH

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

  7. #7
    Join Date
    Sep 2006
    Posts
    19
    The link below may provide some details

    http://publib.boulder.ibm.com/infoce...n/c0005414.htm

    Istikhar

  8. #8
    Join Date
    Mar 2005
    Posts
    73
    Satya Ram . Thanks for u r reply.

    Currently i am working on other SQL , which is taking a longer time than usual time.


    I am attaching the explain out put of it here. Damn these sqls are killing me .

Posting Permissions

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