CODEWITHSUNDEEP
oracle-databaseoracle11g
well_i
well_i

Reputation: 369

Verify how long a query will run, before running?

Does it make sense to try and understand how long a query will take to run, before making the query.

Example:

Users are allowed to do historical query against data, to stop users creating run long running queries, what verification can be done for how long this will take?

Upvotes: 2

Views: 265

Answers (1)

Jon Heller
Jon Heller

Reputation: 36922

Query run time can be estimated with EXPLAIN PLAN.

Create a function to run an EXPLAIN PLAN on a given query, find the time estimate, and return the number.

Sample Function

create or replace function estimate_run_time_in_seconds(p_sql clob) return number is
    v_statement_id number := round(dbms_random.value*100000000);
    v_time_in_seconds number;
begin
    execute immediate 'explain plan set statement_id = '''||v_statement_id||''' for '||p_sql;

    select max(time)
    into v_time_in_seconds
    from plan_table
    where statement_id = to_char(v_statement_id);

    return v_time_in_seconds;
end;
/

Sample Results

begin
    dbms_output.put_line(estimate_run_time_in_seconds('select * from dual'));
    dbms_output.put_line(estimate_run_time_in_seconds(
        'select * from dba_objects cross join dba_users'));
end;
/

Results:
1
34

In practice these estimates will be way off. But this may be good enough if you're just looking for an order-of-magnitude estimate.

There are so many times when the optimizer is horribly wrong, I wouldn't recommend completely blocking any query because of the estimate. Instead, maybe just offer a warning to the user. For example: "Oracle estimates this query will run in X seconds! Are you sure you want to run this query?"

To improve estimate accuracy you may want to look at gathering or manually tweaking the system statistics. Look at the results in the below table. You may want to (very carefully!) change some of the values to align time estimates with the actual time.

select * from sys.aux_stats$;

Upvotes: 1

Related Questions