Professional Web Applications Themes

Performance Problems - IBM DB2

Hi, I'm a DB2 new user, and I'm very concerned about SQL performance using DB2 UDB. I tried Visual Explain Tool, but I didn't find a good article about Cost Time (timerons). - Visual Explain set a Cost Time, but what does it means ? - Is a SQL query with a Cost Time of 300 timerons good or not ? - Which is the Cost Time (timerons) range ? 1 to 1.000 or 1 to 10.000 ? - Which is a poor, medium and good grade - Cost Time (timerons)? (Example: 1 to 400 is good, 500 to 700 ...

  1. #1

    Default Performance Problems

    Hi,

    I'm a DB2 new user, and I'm very concerned about SQL performance
    using DB2 UDB. I tried Visual Explain Tool, but I didn't find a good
    article about Cost Time (timerons).

    - Visual Explain set a Cost Time, but what does it means ?
    - Is a SQL query with a Cost Time of 300 timerons good or not ?
    - Which is the Cost Time (timerons) range ? 1 to 1.000 or 1 to
    10.000 ?
    - Which is a poor, medium and good grade - Cost Time (timerons)?
    (Example: 1 to 400 is good, 500 to 700 is medium and > 700 is poor)
    ????

    Who can explain or indicate a good doentation that really
    explains about Timerons, and tips and tricks about SQL performance ?

    Ivan Tavares
    Ivan Tavares Guest

  2. #2

    Default Re: Performance Problems

    "Ivan Tavares" <ivan_tavareshotmail.com> wrote in message
    news:29031fa.0309161511.31e3be9eposting.google.co m...
    > Hi,
    >
    > I'm a DB2 new user, and I'm very concerned about SQL performance
    > using DB2 UDB. I tried Visual Explain Tool, but I didn't find a good
    > article about Cost Time (timerons).
    >
    > - Visual Explain set a Cost Time, but what does it means ?
    > - Is a SQL query with a Cost Time of 300 timerons good or not ?
    > - Which is the Cost Time (timerons) range ? 1 to 1.000 or 1 to
    > 10.000 ?
    > - Which is a poor, medium and good grade - Cost Time (timerons)?
    > (Example: 1 to 400 is good, 500 to 700 is medium and > 700 is poor)
    > ????
    >
    > Who can explain or indicate a good doentation that really
    > explains about Timerons, and tips and tricks about SQL performance ?
    >
    > Ivan Tavares
    Timerons are a relative measure of performance. It is a mixture of CPU time
    and disk time needed to satisfy the query. DB2 has a cost based optimizer so
    it uses timerons to try various different access paths and the choose the
    best one. The value computed by DB2 for timerons is an "estimate" based
    partially on statistics captured via the runstats utility (how many rows in
    the table, etc.) and may not be accurate.

    Whether or not 300 is good depends on how many rows are in the table, how
    many rows must be read to provide the answer set, and how many rows have to
    be returned in the answer set. So there is no way to assign a "grade" solely
    based on the timeron value.

    When looking at the explain, it is important to understand which indexes are
    being used, joins, sorts, and other things that DB2 uses to access the data
    and return the answer. Tablespace scans read every row in the table (which
    is usually costly), but may be appropriate if the table is very small or if
    it is the only way for DB2 to get the correct answer set.


    Mark A Guest

  3. #3

    Default Re: Performance Problems

    Timeron is an internal value used by the optimizer to estimate the cost
    (built from # of instructions required and i/o costs) od executinf the
    statement.
    It has meaning on that platform with that os with that database with
    those statistics.
    In an of itself, it does not give you any idea of how "fast" the query
    will run. It mor usually ised as a comparative value on that system to
    see if changing table descriptors (stats, indexes, .. ) and or pdb
    performance parameters, will improve or worsen the statement execution.

    You can't use to timerons determine wether a query is good or bad. The
    same query, running on the same table with the same indexes but on
    different platform or with different tuning parameters will give totally
    different timeron values.
    Your example (1 to 400 is good, 500 to 700 is medium and > 700 is poor)
    tells you that the lower is the better; but, this presumes that you have
    run explain to get the three values.
    A timeron value of 499 does NOT tell you if this good, bad, better or
    worse. It just tells, at that time, given parms. and table structure,
    that's the estimated cost.
    It's like saying is 60 mph good, dangerous , a crawl or what. Depends
    if you are in a car, a bicycle or a plane!

    Yor db admin guide does discuss explain. the help function on explain is
    also very detailed.
    HTH, Pierre.

    Ivan Tavares wrote:
    > Hi,
    >
    > I'm a DB2 new user, and I'm very concerned about SQL performance
    > using DB2 UDB. I tried Visual Explain Tool, but I didn't find a good
    > article about Cost Time (timerons).
    >
    > - Visual Explain set a Cost Time, but what does it means ?
    > - Is a SQL query with a Cost Time of 300 timerons good or not ?
    > - Which is the Cost Time (timerons) range ? 1 to 1.000 or 1 to
    > 10.000 ?
    > - Which is a poor, medium and good grade - Cost Time (timerons)?
    > (Example: 1 to 400 is good, 500 to 700 is medium and > 700 is poor)
    > ????
    >
    > Who can explain or indicate a good doentation that really
    > explains about Timerons, and tips and tricks about SQL performance ?
    >
    > Ivan Tavares
    P. Saint-Jacques Guest

Similar Threads

  1. Performance problems (!!!) in Photoshop CS
    By Jay W Walden in forum Adobe Photoshop Mac CS, CS2 & CS3
    Replies: 2
    Last Post: March 3rd, 11:11 PM
  2. ASP / SQL 2000 performance problems
    By Craig McBeath in forum ASP Database
    Replies: 2
    Last Post: January 13th, 08:18 PM
  3. ORACLE ON AIX PERFORMANCE PROBLEMS
    By agis in forum AIX
    Replies: 12
    Last Post: September 26th, 11:20 AM
  4. Locking errors ?? Performance problems
    By Ben Stewart in forum IBM DB2
    Replies: 2
    Last Post: August 5th, 07:55 PM
  5. DB2 performance problems
    By Robert Eigner in forum IBM DB2
    Replies: 10
    Last Post: July 3rd, 12:56 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139