Home » SQL & PL/SQL » SQL & PL/SQL » How to measure the appox. proper exection time of query (merged)
How to measure the appox. proper exection time of query (merged) [message #416741] Tue, 04 August 2009 03:10 Go to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
Hi,

Count of Query Output Resultant is : 70,000 records from a very huge volumetric partition table.

If the query is run on Toad than it shows the query resultant in 3 Min 18 Sec, when we drag the to view the resultant data than it gets hang for a while.
If the query is run on SQL prompt than it shows the query resultant as there are lots of data to view it, so it keep the data view running and after a while it gets hang.

How to measure the appox. proper exection time of query?
Is Toad execution time can be taken account, if yes than what about the above depicted scenario?
Does oracle stores the current query execution time, number of records , etc..?
Please suggest.
Re: How to measure the appox. proper exection time of query [message #416743 is a reply to message #416741] Tue, 04 August 2009 03:15 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
DBMS_UTILITY.GET_TIME() can help you.

regards,
Delna
Re: How to measure the appox. proper exection time of query (merged) [message #416752 is a reply to message #416741] Tue, 04 August 2009 03:59 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
SET TIMING ON
sqlplus
Re: How to measure the appox. proper exection time of query (merged) [message #416753 is a reply to message #416752] Tue, 04 August 2009 04:05 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
SET TIMING ON
wont give query execution time.

regards,
Delna
Re: How to measure the appox. proper exection time of query (merged) [message #416759 is a reply to message #416741] Tue, 04 August 2009 04:38 Go to previous messageGo to next message
Neo06
Messages: 11
Registered: January 2008
Junior Member
I use to insert systimestamp in a temporary table before and after my script to check the execution time.
Re: How to measure the appox. proper exection time of query (merged) [message #416761 is a reply to message #416753] Tue, 04 August 2009 04:40 Go to previous messageGo to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
Hi Delna,

Required to check the SELECT Statement, will this be fine.
If not please suggest

BEGIN
SELECT * FROM TABLE_NAME WHERE FIELD1='xxx';
-- Yileds 70K Record
DBMS_OUTPUT.put_line(DBMS_UTILITY.GET_TIME);
END;
Re: How to measure the appox. proper exection time of query (merged) [message #416769 is a reply to message #416753] Tue, 04 August 2009 04:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
delna.sexy wrote on Tue, 04 August 2009 11:05
SET TIMING ON
wont give query execution time.

regards,
Delna

If so, neither DBMS_UTILITY.GET_TIME.

Regards
Michel

Re: How to measure the appox. proper exection time of query (merged) [message #416775 is a reply to message #416741] Tue, 04 August 2009 05:02 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hi tapaskmanna,
get example here

regards,
Delna
Re: How to measure the appox. proper exection time of query (merged) [message #416779 is a reply to message #416741] Tue, 04 August 2009 05:14 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Michel sir,
Quote:
delna.sexy wrote on Tue, 04 August 2009 11:05
SET TIMING ON
wont give query execution time.

regards,
Delna

If so, neither DBMS_UTILITY.GET_TIME.

Regards
Michel


I think, SET TIMING ON will include "print" overhead also.
If I am wrong, please correct me.

regards,
Delna
Re: How to measure the appox. proper exection time of query (merged) [message #416784 is a reply to message #416779] Tue, 04 August 2009 05:25 Go to previous messageGo to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
Hi,

I am looking for actual execution time taken, which will be more accurate TOAD, SQL PROMT?

Thanks Delna/Michel.

Regards
Tapas
Re: How to measure the appox. proper exection time of query (merged) [message #416788 is a reply to message #416779] Tue, 04 August 2009 05:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
think, SET TIMING ON will include "print" overhead also.
If I am wrong, please correct me.

It depends on what you mean by "execution time" and where you put the DBMS_UTILITY.GET_TIME.

Regards
Michel
Re: How to measure the appox. proper exection time of query (merged) [message #416792 is a reply to message #416784] Tue, 04 August 2009 05:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am looking for actual execution time taken, which will be more accurate TOAD, SQL PROMT?

Explain what you mean with "execution time".

Regards
Michel
Re: How to measure the appox. proper exection time of query (merged) [message #416795 is a reply to message #416788] Tue, 04 August 2009 05:44 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Quote:
It depends on what you mean by "execution time" and where you put the DBMS_UTILITY.GET_TIME.

Regards
Michel


Obviously, execution time is the time taken by the Oracle to execute the query. Front-end may be anything and it should not be considered in execution time.
And that is given correctly by GET_TIME() ,and not by SET TIMING ON.

regards,
Delna
Re: How to measure the appox. proper exection time of query (merged) [message #416796 is a reply to message #416795] Tue, 04 August 2009 05:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Obviously, execution time is the time taken by the Oracle to execute the query.

Not so obvious for me and even don't know what OP means with "execution time" and even don't know what you think when you say "time taken by the Oracle to execute the query"; explain it.
For most users, execution time means time to get the result.

Regards
Michel

[Updated on: Tue, 04 August 2009 05:54]

Report message to a moderator

Re: How to measure the appox. proper exection time of query (merged) [message #416799 is a reply to message #416796] Tue, 04 August 2009 05:59 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
As an Oracle developer (not a front-end programmer), I mean execution time by
Time taken by the CPU to execute query + time taken for disk IO

regards,
Delna
Re: How to measure the appox. proper exection time of query (merged) [message #416803 is a reply to message #416799] Tue, 04 August 2009 06:13 Go to previous messageGo to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member

Quote:
Explain what you mean with "execution time".


Execution Time mean here is:
i) Parsing the Query
ii)Fetching the records

As you are correct Michel, Its the fetching records.

If this is not correct, pls suggest.
Re: How to measure the appox. proper exection time of query (merged) [message #416808 is a reply to message #416803] Tue, 04 August 2009 06:56 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Strictly speaking "execution time" is the time of the step between "parsing" and "fetching" (if you omit binding).

Regards
Michel

[Updated on: Tue, 04 August 2009 06:57]

Report message to a moderator

Previous Topic: problem in union sql statment
Next Topic: utl_smtp
Goto Forum:
  


Current Time: Wed Dec 07 22:17:59 CST 2016

Total time taken to generate the page: 0.05794 seconds