Home » RDBMS Server » Server Administration » finding execution time for a sql script
finding execution time for a sql script [message #226163] Thu, 22 March 2007 12:42 Go to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Hi,

I have a sql script that fires 7 or 8 select/insert/update statements...it is desired to know that how much time this scripts takes from start to end..(not each statement individually but the entire script as a whole) , ...can you help in how this could be done...perhaps by setting some variables in sql but I am not exactly sure of how that is done...

Thanks!
Re: finding execution time for a sql script [message #226165 is a reply to message #226163] Thu, 22 March 2007 12:49 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
HOST out and write the date/time to a file at the beginning and then HOST out at the end and write the date/time again.

In unix:

!date > foo.log
{stuff}
!date >> foo.log
Re: finding execution time for a sql script [message #226166 is a reply to message #226163] Thu, 22 March 2007 12:54 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
SQL > SET TIME ON
Re: finding execution time for a sql script [message #226173 is a reply to message #226166] Thu, 22 March 2007 13:13 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Thank you Joy and Anacedent! I want to know the time on SqL prompt only , not in a text file..and the time should be cumulative from start of script to end...like one select took a minute and another update took 2 minute then its of no use to know these individually- but if the script has only this 2 statements then 3 minutes is what the total time we are looking for..in sql plus.
Re: finding execution time for a sql script [message #226185 is a reply to message #226173] Thu, 22 March 2007 14:54 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
You could do this:
col start_time new_value start_time
col end_time new_value end_time

select to_char(sysdate,'mm/dd/yyyy hh24:mi:ss') start_time
from dual;

{stuff}

select to_char(sysdate,'mm/dd/yyyy hh24:mi:ss') end_time
from dual;

select (to_date('&end_time','mm/dd/yyyy hh24:mi:ss') -
        to_date('&start_time','mm/dd/yyyy hh24:mi:ss')) *24*60*60 total_time_in_seconds
from dual;

[Updated on: Thu, 22 March 2007 14:54]

Report message to a moderator

Re: finding execution time for a sql script [message #226222 is a reply to message #226185] Thu, 22 March 2007 21:56 Go to previous message
orausern
Messages: 817
Registered: December 2005
Senior Member
Great...this is precisely what I was looking for...and I have urgent client delivery for which I have been staying up long nights...this will help me a lot!

Thank you.
Nirav
Previous Topic: create database
Next Topic: Oracle9i Horizontal Partitioning for tables
Goto Forum:
  


Current Time: Sat Dec 03 06:10:56 CST 2016

Total time taken to generate the page: 0.05150 seconds