Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Scott.

Asked: May 02, 2004 - 7:41 pm UTC

Last updated: May 16, 2005 - 1:18 pm UTC

Version: 9.2.0.5

Viewed 1000+ times

You Asked

Hi Tom

We frequently run large data loads or complicated select statements that take many minutes, sometimes hours, to complete. Our users are a pretty impatient and unforgiving lot, and are forever giving us support guys the "is it ready yet? is it ready yet?" treatment.

What would be really great is to be able to give them a reasonably accurate estimate of when a given sql statement will complete. Is there an easy way to do this? If so can you please demonstrate how this is done. It would greatly help myself and the other support guys out.

At present the best we have to go by is past experience when the job being run is a regularly scheduled one. This doesn't help much with one-off or new jobs (both are usually higher profile in the users eyes). There estimates also tend to become less accurate when the server is under a lot of load, at month end for example when everyone is running reports at the same time.

Thanks a lot for your help.

and Tom said...

Long running queries may be monitored in v$session_longops

In fact, long running stored procedures that were written to give you this information may be monitored as well -- if the developers added the calls to dbms_application_info.


Search this site for v$session_longops, or if you have my book "Expert one on one Oracle" -- read the appendix on dbms_application_info for lots of detail

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

great!!

Lou, May 14, 2005 - 8:37 pm UTC

But how can I use the information from v$session_longops
and pass it back to less say dbms_lock? so that I know how long the operation took and so passing the time it took dinamically to dbms_lock so that the next procedure run in in serial?


Thanks ;:

Tom Kyte
May 14, 2005 - 9:19 pm UTC

don't get it....

dbms_lock is a SERIALIZATION device. there is no "timing" information to be fed into it.

If you want to serialize
then
use dbms-lock to create a lock in EXCLUSIVE MODE
end if




A reader, May 16, 2005 - 12:20 pm UTC

Hi Tom,

What if it is a NESTED LOOP / unique index reads / index range scans etc ?

Is there any way we can estimate the time to completion for those operatins ?

thanks
Regi

Tom Kyte
May 16, 2005 - 1:18 pm UTC

not really (but if they are taking long -- it probably means you should have them in place, they are for "quick" answers....)

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library