Results 1 to 4 of 4

Thread: sql tuning

  1. #1
    Join Date
    Apr 2013
    Posts
    19

    Unanswered: sql tuning

    Hi Sir's

    i done the sql tuning

    Estimated Cost = 149858.921875 before creating the indexes and exegution time is 6:00 min

    Estimated Cost = 62165.964844 after creating the indexes and exegution time is 6:00 min

    db2 udb v9.7 Fix Pack 4 uses "64" bits

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    cost is NOT directly related to time
    biggest part of cost is IO
    if you need advise : publish the sql - explain output or use db2advis
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Apr 2013
    Posts
    19
    Quote Originally Posted by przytula_guy View Post
    cost is NOT directly related to time
    biggest part of cost is IO
    if you need advise : publish the sql - explain output or use db2advis
    hi

    iam attaching the output
    Attached Files Attached Files

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by naveen kotla View Post
    Hi Sir's

    i done the sql tuning

    Estimated Cost = 149858.921875 before creating the indexes and exegution time is 6:00 min

    Estimated Cost = 62165.964844 after creating the indexes and exegution time is 6:00 min

    db2 udb v9.7 Fix Pack 4 uses "64" bits
    1. There is only a difference in 2x between the estimated relative cost of the two, which is not significant.
    2. The SQL is ridiculously complex, so I am not even sure DB2 can figure out the estimated cost accurately.
    3. Whomever wrote that SQL should get rid of the TO_DATE functions in the WHERE clauses and learn how DB2 handles dates, date arithmetic, and date comparisons. Same with any other functions in the WHERE clause.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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