Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2009
    Posts
    26

    Unanswered: Analysing execution times in Control Center

    Hi all,

    we are trying to do some performance optimizations in our application and are therefore analyzing some slow SQL statements.

    We are using the tool DB2 Monitor (IBM DB2 UDB Performance monitor) for grabbing the long running SQLs.

    Yesterday I found a statement which execution time was 12 seconds (measured by our application itself and by DB2 Monitor). I copied the statement and put it into the DB2 Control Center application in order to let it build the execution plan so that I can find the most expensive parts of the statement. But when I execute this same statement in the Control Center it only takes 0.5 seconds. How is that?

    In order to exclude any caching issues I re-executed the statement several times in our application and in the Control Center and each time it took 12 seconds in the app and 0.5 seconds in the Control Center.

    Can someone explain to me what I am doing wrong? That way I am unable to find my performance leaks.

    Thanks in advance,
    Olel

    DB2 version: "DB2 v9.1.300.257", "s070719", "WR21393" and FixPak "3".
    DB2 type: DB2 Enterprise Server Edition, db2ese, 9.1
    Last edited by olel; 10-28-11 at 03:06. Reason: added version and type

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by olel View Post
    Can someone explain to me what I am doing wrong?
    I bet my shoes that you're substituting literal values for parameter markers in the WHERE clause. Don't do it; use EXPLAIN PLAN FOR SELECT ... to generate explain information for the original statement.

  3. #3
    Join Date
    Oct 2009
    Posts
    26
    Not bad... you're right! Looks like this is a common mistake by newbies...
    As I am such a newbie can you explain how that "EXLAIN PLAN FOR SELECT" works? I would like to stick to the graphical representation of the plan which is part of the Control Center as I am not really good in reading textual explain plans.

    Oh, and why is the query is so much faster with literal values?

    Thanks in advance!

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You can still use the Control Center/Command Editor to get the visual explain using parameter markers. In the Command Editor, enter the SQL with parameter markers. Then press the "Access Plan" button.

    Andy

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by olel View Post

    Oh, and why is the query is so much faster with literal values?
    See if this thread answers your question. http://www.dbforums.com/db2/1671054-...ml#post6522741

  6. #6
    Join Date
    Oct 2009
    Posts
    26
    Quote Originally Posted by n_i View Post
    See if this thread answers your question. http://www.dbforums.com/db2/1671054-...ml#post6522741
    As far as I understand that thread there are some optimization possibilities for DB2 if you use literal values.

    I think that makes clear why I cannot see any differences when executing queries directly in the Control Center with literal values with indexes created or not.

    Is there any way to execute queries in the Control Center with literal values without giving DB2 the opportunity for these optimizations? It would be very helpful for me to test some queries with literal values without having our application running.

Posting Permissions

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