Home » RDBMS Server » Performance Tuning » more precise timing with TKPROF
more precise timing with TKPROF [message #207438] Tue, 05 December 2006 10:03 Go to next message
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 #207476 is a reply to message #207438] Tue, 05 December 2006 13:26 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Any ideas?
Stop wasting time & effort on any SQL statement that completes in less than 0.01 seconds.
Re: more precise timing with TKPROF [message #207478 is a reply to message #207476] Tue, 05 December 2006 13:44 Go to previous messageGo to next message
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 #207493 is a reply to message #207438] Tue, 05 December 2006 14:55 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
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).

Re: more precise timing with TKPROF [message #207496 is a reply to message #207493] Tue, 05 December 2006 15:48 Go to previous message
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. Sad
Previous Topic: PGA_AGGREGATE_TARGET & SORT_AREA_SIZE
Next Topic: Alter Table parallel complexity
Goto Forum:
  


Current Time: Sun Dec 04 04:24:51 CST 2016

Total time taken to generate the page: 0.16242 seconds