Re: Estimating a SQL stmt execution time

No, there is no query or operation that you can issue to get an estimated time back. Execution time is not a constant; it will always be variable depending on the exact load. You could, however, estimate it:

1. One option might be to issue an explain plan on the query before actually running it. If you are using the CBO, you could get an execution cost back, and you could estimate execution time based on the execution cost. This could get a bit tricky, though. You'd have to make sure that every user has a plan table in their schema.
2. Again, if you're using the CBO, you should be able to SELECT some statistics from the data dictionary tables (num_rows from all_tables, num_distinct from all_tab_columns, etc.), do a little twisting and turning, and come up with some sort of time estimate. This option has some advantages in that you wouldn't need to have a plan table in every user's schema, and you could write this as a PL/SQL function (pass the function the SQL statement you're going to issue), which means it would be very easy to re-use in other applications. I'm not sure how accurate you could get it, but you should certainly be able to determine whether the execution time will be seconds, minutes, or hours, for instance. If you give this a try and get good results, email me back and let me know exactly how you did it.

Hope this helps,
Dave

vfoster2_at_aol.com wrote in message <6ifjnr$efq$1_at_nnrp1.dejanews.com>...
>Is there any way to get Oracle to tell you how long a SQL stmt will run,
>either from SQL*Plus or Power Builder. I need this to tell users in a
>message box that "This statement will that x number of minutes to execute,
do
>you wish to continue?"
>
>Any input appreciated.
>
>
>Vicky D. Foster,
>Oracle DBA,
>Manager, Database Services
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/ Now offering spam-free web-based newsreading
Received on Sat May 02 1998 - 23:10:38 CDT