Jagdish,
You cannot make any assumptions about time based solely on timerons. As the description from Nadeem indicates, timerons are a measure that tries to mix apples (CPU) and oranges (IO). They are really a way for the optimizer to evaluate and compare several access plans for the query before coming up the one that it uses - the one that shows up in the Explain Plan.
You say you changed the original query and the timerons changed. Is that all that changed? Did the access path change too?
When you change a query, all bets are off. What looks like a slight change to you, may have a significant effect on the optimizer.
Timerons are at best a vague indicator. In general, if the query stays the same, but you make other changes like runstats or new indexes, then the probability is that lower timerons will also mean shorter response time. That's not ironclad however. In general, if the query changes in anyway, then you are starting new with the timeron values.
Of course, any of us would be foolish to disregard a consistently high performing query based on the fact that the timerons were 200000.
Regards,
>>> "Jagdish Singh Rawat NCS " <db2udbdba-ezmlmshield-x25129299.[Email address protected] 1/15/2008 2:12:15 AM >>>
Oh Nadeem
Thanks
Now can I assume
So can I assume
That if my timeron for one query is 200000 and other is 10 then the
query 200000 can be much faster than 10.
Regards
Jagdish S. Rawat
IBM Certified Advance DB2DBA
NCS Pte. Ltd.
5 Ang Mo Kio St 62, NCS Hub
Singapore - 569141
Ph.# +65-65565870(Direct) +65-65568000 Ext. # 5870
HP.# +65-96425135
Fax# +65-64830495
-----Original Message-----
From: Khan Nadeem
[mailto:db2udbdba-ezmlmshield-x90214631.[Email address protected]
Sent: Tuesday, January 15, 2008 5:30 PM
To: LazyDBA Discussion
Subject: RE: Timeruns Confusion.
timeron is a combination of CPU time and the time spent for input and
output.
a timeron does not directly equate to any actual elapsed time, but gives
a rough relative estimate of the resources required by the database
manager to execute the plan (strategy for access data) for a query.
the measurement does not reflect factors such as the workload on the
system or the cost of returning rows of data to the user client.
is a measure unit for SQL query invented from IBM DB2 engineer.
Thanks,
Nadeem Khan
-----Original Message-----
From: Jagdish Singh Rawat NCS
[mailto:db2udbdba-ezmlmshield-x23545253.[Email address protected]
Sent: Tuesday, January 15, 2008 2:25 PM
To: LazyDBA Discussion
Subject: Timeruns Confusion.
Hi,
Want to know your views.
I have a query when I use explain plan it shows 30 timeruns as the cost
of the query.
And when I run it it take fractions of millisecond.
Now I change some criteria in the query and explain it it give me 33
timeruns but when I run it it take almost 4 min to run..
Can any one explain why.
What is the significance of timerun then.?
How can I get the time from time runs.
Regards
Jagdish S. Rawat
IBM Certified Advance DB2DBA
NCS Pte. Ltd.
5 Ang Mo Kio St 62, NCS Hub
Singapore - 569141
Ph.# +65-65565870(Direct) +65-65568000 Ext. # 5870
HP.# +65-96425135
Fax# +65-64830495
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
This message contains information that may be privileged or confidential
and is the property of the Capgemini Group. It is intended only for the
person to whom it is addressed. If you are not the intended recipient,
you are not authorized to read, print, retain, copy, disseminate,
distribute, or use this message or any part thereof. If you receive this
message in error, please notify the sender immediately and delete all
copies of this message.
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
******************************************************************************
This e-mail is intended only for the use of the individual or entity
to which it is addressed and may contain information which is
privileged, confidential, and exempt from disclosure under applicable
law. If you are not the intended recipient, or an employee or agent
of the intended recipient, you are hereby notified that any
dissemination, distribution, or copying of this communication is
strictly prohibited. If you have received this communication in error
please notify us immediately by replying to [Email address protected]
******************************************************************************
**************************************************************************************
This e-mail is intended only for the use of the individual or entity
to which it is addressed and may contain information which is
privileged, confidential, and exempt from disclosure under applicable
law. If you are not the intended recipient, or an employee or agent
of the intended recipient, you are hereby notified that any
dissemination, distribution, or copying of this communication is
strictly prohibited. If you have received this communication in error
please notify us immediately by replying to [Email address protected]
**************************************************************************************
DB2 & UDB email list listserv db2-l LazyDBA home page