| more precise timing with TKPROF [message #207438] |
Tue, 05 December 2006 10:03  |
HibsMax
Messages: 4
Registered: December 2006
Location: Massachusetts
|
Junior Member |
|
|
Hi, Group.
Just joined, I hope this is the correct forum for this question.
We use Oracle10g running on Windows.
A developer has asked me to run some queries and gather accurate timings. She runs the query and sees runtimes of between 100ms and 900ms. The method of gathering timings is:
1. select systimestamp from dual;
2. run query
3. repeat 1
4. calcluate difference betweem 3 and 1
That, of course, is only good for elapsed time and includes all other things as well e.g. network latency.
I tried playing around with TKPROF yesterday and noted that the CPU and Elapsed times are repored in 1/100ths of a second. When I looked at the times for the three queries, they all said 0.00. I checked, timed_statistics is TRUE. Is there a way that I can get TKPROF to be more precise and report something other than 0? The times stored in v$sqlarea are in microseconds so it seems that Oracle has the data I want, I just don't know how to get at it.
Any ideas?
Thanks, Max
[Updated on: Tue, 05 December 2006 10:05] Report message to a moderator |
|
|
|
|
|
| Re: more precise timing with TKPROF [message #207478 is a reply to message #207476] |
Tue, 05 December 2006 13:44  |
HibsMax
Messages: 4
Registered: December 2006
Location: Massachusetts
|
Junior Member |
|
|
| anacedent wrote on Tue, 05 December 2006 14:26 | >Any ideas?
Stop wasting time & effort on any SQL statement that completes in less than 0.01 seconds.
|
Thanks for your input. Very helpful.
Edit : what may appear like a waste of time to one person is not necessarily a waste of time to someone else. Looking back at the original question, you will see reports of the same query taking between 100ms and 900ms. That's a 900% difference from best to worst. All I want to do is find out how much time the query is actually taking. Not too much to ask. If Oracle can store CPU time and Elapsed time in microseconds, one would think it could also report the time in those units.
[Updated on: Tue, 05 December 2006 13:54] Report message to a moderator |
|
|
|
|
|
| Re: more precise timing with TKPROF [message #207496 is a reply to message #207493] |
Tue, 05 December 2006 15:48 |
HibsMax
Messages: 4
Registered: December 2006
Location: Massachusetts
|
Junior Member |
|
|
| michael_bialik wrote on Tue, 05 December 2006 15:55 | Run a NUMBER of problematic SQL statements (with a different parameters) and post TKPROF.
It may happen that the difference is caused by SQLNet(for example).
|
Thanks, Michael. I agree, I believe the difference is caused by some other factor e.g. SQLNet. Being able to view the CPU time with enough precision would perhaps help but I am thinking that this cannot be done. 
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
