Home » SQL & PL/SQL » SQL & PL/SQL » Getting the exact elapsed time of a SQL (10g)
Getting the exact elapsed time of a SQL [message #325394] Fri, 06 June 2008 02:16 Go to next message
beetel
Messages: 96
Registered: April 2007
Member
i have a query that retrieves 500,000 records. When I use PL/SQL developer or Toad, they just display the first 100 or so, then in order to get all records, I have to click the next records button. Therefore, I cannot tell the exact elapsed time when the query actually completes to retrieve all records. How can I get the exact elapsed time?

i dont think the "SELECT COUNT(1) FROM (the SQL query)" will be the same as actually fetching the data and also have TRUNC, TO_DATE and TO_CHAR functions in my query.

Please help.

[Updated on: Fri, 06 June 2008 02:18]

Report message to a moderator

Re: Getting the exact elapsed time of a SQL [message #325401 is a reply to message #325394] Fri, 06 June 2008 02:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and "set timing on"

Regards
Michel
Re: Getting the exact elapsed time of a SQL [message #325404 is a reply to message #325401] Fri, 06 June 2008 02:32 Go to previous messageGo to next message
beetel
Messages: 96
Registered: April 2007
Member
I did that If I use SQLPlus, it hangs since it displays 500,000 records with long column values. And how about if i need to get millions of records?
Re: Getting the exact elapsed time of a SQL [message #325411 is a reply to message #325404] Fri, 06 June 2008 02:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It does not hang it is just doing work.
Who need to display million of rows? Who is able to read million of rows?

Regards
Michel
Re: Getting the exact elapsed time of a SQL [message #325443 is a reply to message #325411] Fri, 06 June 2008 04:04 Go to previous messageGo to next message
beetel
Messages: 96
Registered: April 2007
Member
Would you agree then that 'SELECT COUNT(1) FROM (original query)' is faster than running the original query in SQL Plus?

And by the way, my query is too big (3000+) chars. Even if I save it as a file, SQL plus does not accept: " Input is too long (> 2499 characters) - line ignored"

[Updated on: Fri, 06 June 2008 04:10]

Report message to a moderator

Re: Getting the exact elapsed time of a SQL [message #325448 is a reply to message #325443] Fri, 06 June 2008 04:20 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Use Toad and set autotrace on in it.
Re: Getting the exact elapsed time of a SQL [message #325452 is a reply to message #325443] Fri, 06 June 2008 04:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Would you agree then that 'SELECT COUNT(1) FROM (original query)' is faster than running the original query in SQL Plus?

Yes but it is not the same query.

Quote:
And by the way, my query is too big (3000+) chars. Even if I save it as a file, SQL plus does not accept: " Input is too long (> 2499 characters) - line ignored"

What about using a SQL Formatter and putting new lines from time to time in your query instead of having it in a single line?

Regards
Michel
Re: Getting the exact elapsed time of a SQL [message #325453 is a reply to message #325443] Fri, 06 June 2008 04:35 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
It is very simple. If you ask any client to do more work it will take more time. If you ask it to do less work it will take less time.

There is a slight difference but these two queries but the time difference will be significant if the number of bytes returned is quite significant (like few hundred thousands).
select * from <table_name>;
select count(*) from
(select * from <table_name>);

As you can see clearly my first query will display all the records whereas my second query will just display the count. So my final output from the first query will be <number_of_bytes> * <number_of_rows>. Add the network round trip, sql*net check etc. Whereas in the second query it will just return only the number of rows which is hardly anything. So if you ask me it is a pointless exercise.

Regards

Raj

[Updated on: Fri, 06 June 2008 04:37]

Report message to a moderator

Re: Getting the exact elapsed time of a SQL [message #325607 is a reply to message #325453] Fri, 06 June 2008 12:53 Go to previous message
beetel
Messages: 96
Registered: April 2007
Member
Makes sense.. thanks a ton!
Previous Topic: update the same table in trigger
Next Topic: create trigger
Goto Forum:
  


Current Time: Thu Dec 08 14:25:12 CST 2016

Total time taken to generate the page: 0.08906 seconds