We help IT Professionals succeed at work.

Finding out how long a query will take

Is there any way to do that in Oracle? In TOAD? Any way to get ballpark numbers?
Comment
Watch Question

As mentioned at the following URL: http://www.dba-oracle.com/t_measure_sql_response_time.htm

you can do that from SQL*PLUS:

SQL> set timing on;
SQL> select stuff from mytab;
Also, from PL/SQL I guess you could do something like:

DECLARE
  TIMESTART TIMESTAMP;
  TIMESTOP TIMESTAMP;
BEGIN
  TIMESTART := systimestamp;

  -- Process things here
  
  TIMESTOP := systimestamp;
  DBMS_OUTPUT.PUT_LINE('Time elapsed : ' || (TIMESTOP - TIMESTART));
END;
/

Open in new window

Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
if you're looking to get an estimate of how long it will take without executing it.

No, there isn't reliable method.

Even the "time" portion of an explain plan can be way off.

Author

Commented:
Yes, sdstuber, that's what I was looking for. I guess there isn't a way. Thanks anyway.

Author

Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for soccerplayer's comment http:/Q_27426391.html#37066894

for the following reason:

There is really no solution to this
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
"you can't do that"  is an acceptable answer.   http://www.experts-exchange.com/help.jsp#hs=29&hi=405


and, unfortunately, that is the answer as posted in  http:#37066282
All,

In this instance, sdstuber's comment indicating this is not possible appears to be a valid solution. While I understand it may be frustrating to hear, "you can't do that" is still a technical answer to a question which has required the Experts to draw on their knowledge, so it is worthy of points.

I am starting the close process.

ModernMatt
Experts Exchange Moderator

Author

Commented:
Accepted

Explore More ContentExplore courses, solutions, and other research materials related to this topic.