OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Timing queries

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Timing queries [message #206633]

Thu, 30 November 2006 22:32

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,

Report message to a moderator

Re: Timing queries [message #206649 is a reply to message #206633]

Fri, 01 December 2006 00:09

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

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!

Report message to a moderator

Re: Timing queries [message #206665 is a reply to message #206653]

Fri, 01 December 2006 00:55

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?

Report message to a moderator

Re: Timing queries [message #206879 is a reply to message #206633]

Fri, 01 December 2006 22:21

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!

Report message to a moderator

Re: Timing queries [message #206881 is a reply to message #206879]

Fri, 01 December 2006 22:36

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!

Report message to a moderator

Previous Topic:	Help Help me plz urgent
Next Topic:	Performance of Lower() function in query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Nov 27 02:47:47 CST 2024