Home » RDBMS Server » Performance Tuning » Timing queries
Timing queries [message #206633] Thu, 30 November 2006 22:32 Go to next message
tlahyani
Messages: 20
Registered: November 2006
Junior Member
Hello -

I have a few queries that I would like to compare as far as performance goes.
Is the 'Elapsed' time good enough or are there other commands that give a more accurate timing? Thanks,
Re: Timing queries [message #206649 is a reply to message #206633] Fri, 01 December 2006 00:09 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
tlahyani
Is the 'Elapsed' time good enough?

It depends on you, I guess.

[Updated on: Fri, 01 December 2006 00:10]

Report message to a moderator

Re: Timing queries [message #206653 is a reply to message #206649] Fri, 01 December 2006 00:30 Go to previous messageGo to next message
tlahyani
Messages: 20
Registered: November 2006
Junior Member
You re right LittleFoot, the thing is that I m not sure what 'Elapsed' time measures exactly, so let me rephrase the question:
What should one use to compare the performance of two queries? Is there anything other than 'Elapsed time' to compare the performance of two queries? Thank you!
Re: Timing queries [message #206665 is a reply to message #206653] Fri, 01 December 2006 00:55 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
When you run both queries and view their explain plans (using SET AUTOTRACE ON), it should be quite accurate (if they were run under the same conditions). Also, you might try to trace both executions (using ALTER SESSION SET SQL_TRACE = TRUE) and run results through TKPROF and, perhaps, see some extra info.

P.S. I believe your "Elapsed" time was get using SET TIMING ON on the SQL*Plus prompt. Right?
Re: Timing queries [message #206879 is a reply to message #206633] Fri, 01 December 2006 22:21 Go to previous messageGo to next message
tlahyani
Messages: 20
Registered: November 2006
Junior Member
Thanks LittleFoot - That helped! And yes I got the elapsed time using set timing on!

So to get the overall execution time of a query, should I just add up the execution time for each operation?

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |  1250 |   125K|   196   (2)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| PROFS |  1250 |   125K|   196   (2)| 00:00:03 |
---------------------------------------------------------------------------


For example, from the table above, the execution time is 6 msec? Thanks!
Re: Timing queries [message #206881 is a reply to message #206879] Fri, 01 December 2006 22:36 Go to previous message
tlahyani
Messages: 20
Registered: November 2006
Junior Member
OK - Looking closely at other tables, it looks like adding up the times does not really make sense! Is it just the top operation time that is the overall execution time?

For example here:

--------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |   299 | 24518 |   593  (37)| 00:00:08 |
|   1 |  UNION-ALL          |          |       |       |            |          |
|*  2 |   HASH JOIN         |          |   199 | 16318 |   381   (2)| 00:00:05 |
|*  3 |    TABLE ACCESS FULL| DEPTS    |     1 |    31 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| STUDENTS | 49999 |  2490K|   376   (2)| 00:00:05 |
|*  5 |   HASH JOIN         |          |   100 |  8200 |   212   (2)| 00:00:03 |
|*  6 |    TABLE ACCESS FULL| DEPTS    |     1 |    31 |     3   (0)| 00:00:01 |
|   7 |    TABLE ACCESS FULL| TAS      | 25000 |  1245K|   208   (1)| 00:00:03 |
--------------------------------------------------------------------------------


Is it 0.08 seconds? Thanks!
Previous Topic: Help Help me plz urgent
Next Topic: Performance of Lower() function in query
Goto Forum:
  


Current Time: Wed Dec 11 21:35:16 CST 2024