This discussion is archived
12 Replies Latest reply: Dec 14, 2012 5:01 AM by 905562 RSS

How to estimate total execution time of a query...?

OraFighter Newbie
Currently Being Moderated
How can I calculate total execution time of a query .

Example :
   select * from table1  ; -- table size is 100GB. 

  OR

   exec DBMS_STATS.GATHER_SHEMA_STATS('USER1', estimate_percent => 15);
statistics gathering not finished even after 2hrs...

is there any way to calculate total time it will take to run..I mean an approximate time.
  • 1. Re: How to estimate total execution time of a query...?
    Hoek Guru
    Currently Being Moderated
    Is this only one table? Then how about using GATHER_TABLE_STATS instead?
  • 2. Re: How to estimate total execution time of a query...?
    OraFighter Newbie
    Currently Being Moderated
    NO, I am asking in general .. how to calculate time of execution before running a query... is it possible..?
  • 3. Re: How to estimate total execution time of a query...?
    Hoek Guru
    Currently Being Moderated
    Well, you might want to check out: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2945229900346583015
  • 4. Re: How to estimate total execution time of a query...?
    6363 Guru
    Currently Being Moderated
    OraFighter wrote:

    NO, I am asking in general .. how to calculate time of execution before running a query... is it possible..?
    Calculate, no. As per your subject you can get an estimate using explain plan, depending on your version, which you should always provide when asking a question.
    SQL> explain plan for
      2  select sysdate from dual;
    
    Explained.
    
    SQL> select * from table(dbms_xplan.display);
    
    PLAN_TABLE_OUTPUT
    ---------------------------------------------------------------------
    Plan hash value: 1388734953
    
    -----------------------------------------------------------------
    | Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
    -----------------------------------------------------------------
    |   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
    |   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
    -----------------------------------------------------------------
  • 5. Re: How to estimate total execution time of a query...?
    BluShadow Guru Moderator
    Currently Being Moderated
    Well you have two issues don't you.

    1. calculating an estimate of the execution time for a query
    answer: Look at the explain plan

    2. gathering statistics in a reasonable time
    answer: there are lots of ways of gathering stats, from a single table to a schema, including certain columns and excluding others, or sampling a smaller amount of data if you know the spread of values is not very skewed etc. It's all in the documentation.
  • 6. Re: How to estimate total execution time of a query...?
    OraFighter Newbie
    Currently Being Moderated
    1) It is exceeding the Explain plan time. Any other way to calculate the time

    2) can you explain the meaning of following statement
       exec DBMS_STATS.gather_schema_stats('USER1',estimate_percent => 15); 
    it took 12hrs , then I canceled the query.
  • 7. Re: How to estimate total execution time of a query...?
    Justin Cave Oracle ACE
    Currently Being Moderated
    OraFighter wrote:
    1) It is exceeding the Explain plan time. Any other way to calculate the time
    Which isn't terribly surprising. The estimated time in the query plan is just that-- an estimate. It is entirely likely that the query plan's estimate is inaccurate particularly if the statistics are inaccurate.

    I don't think that you're really talking about estimating the total execution time of a query, though. It looks to me like you're talking about the execution time of a stored procedure call which is a very different thing... The term "query" implies a SELECT statement.
    2) can you explain the meaning of following statement
    exec DBMS_STATS.gather_schema_stats('USER1',estimate_percent => 15); 
    it took 12hrs , then I canceled the query.
    Depending on the defaults for the other parameters, you're likely asking Oracle to gather statistics on every object in the schema by reading 15% of the rows in the table. Depending on the version of Oracle, you're probably also generating a ton of histograms on various columns. If there is a lot of data in the schema, it will take quite a while to gather statistics on every object.

    Generally, it's pretty rare that you would really want to gather statistics on every object in the schema at any one time. You'd generally want to gather statistics only on objects whose statistics are stale.

    While you are running GATHER_SCHEMA_STATS, you can query V$SESSION_LONGOPS to monitor the progress of the operation
    SELECT *
      FROM v$session_longops
     WHERE time_remaining > 0
    will show you the progress of the statistics gathering process and will give you a constantly evolving estimate of the time remaining.

    Justin
  • 8. Re: How to estimate total execution time of a query...?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    OraFighter wrote:
    1) It is exceeding the Explain plan time. Any other way to calculate the time
    Of course execution times are different and cannot be accurately calculated before the time!!

    It is surprising that some find this.., well surprising. The standard computer environment is a DYNAMIC environment. Not static. Which means execution times, even for the SAME process, can differ. And usually do differ. All the time.

    Simple example:
    SQL> set timing on
    SQL> select count(*) from all_objects;
    
      COUNT(*)
    ----------
         56215
    
    Elapsed: 00:00:06.93
    SQL> select count(*) from all_objects;
    
      COUNT(*)
    ----------
         56215
    
    Elapsed: 00:00:01.63
    SQL> 
    Same server. Same database. Same data. Same SQL. Same execution plan. DIFFERENT execution times as the environment is DYNAMIC. (with the 2nd execution, the data is no longer on disks, but cached in memory, making I/O calls for reading that data, faster)

    Your are grossly mistaken in thinking that query times can be determined accurately beforehand. To do this, one needs computer architecture, hardware and software, that supports a STATIC environment - where every single instruction will take a known time to execute. Something like the Classic RISC Pipeline architecture.
  • 9. Re: How to estimate total execution time of a query...?
    OraFighter Newbie
    Currently Being Moderated
    I don't think that you're really talking about estimating the total execution time of a query, though. It looks to me like you're >>talking about the execution time of a stored procedure call which is a very different thing... The term "query" implies a SELECT >>statement.
    I mean the query itself. then is it possible to check the status of the query , how long it is finished and how much balance.

    I mean from any dictionary tables. like you mentioned for gather statiscis
    SELECT *
    FROM v$session_longops
    WHERE time_remaining > 0
    or if query is inside a procedure , is it possible to check the status...?
  • 10. Re: How to estimate total execution time of a query...?
    905562 Explorer
    Currently Being Moderated
    What DB version are you on?

    In 11g, with appropriate licences, you can use SQL monitoring functions to have an idea of how much work has been done so far.

    If you don't, well the answer depends on your individual execution plan (if longops isnt helping). Sometimes you can tell by checking the objects it is reading against their position in the plan. This works for unions/hashes, not so much for NL access paths.
  • 11. Re: How to estimate total execution time of a query...?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    mrk wrote:

    In 11g, with appropriate licences, you can use SQL monitoring functions to have an idea of how much work has been done so far.
    Good point, but comes with caveats IMO.

    The same workload (e.g. reading 5 x 8Kb blocks) is inconsistent - it can be slow (physical I/O). It can be fast (logical I/O). It can run into other processes traversing the same buffer chain for the same block - and having to spin to acquire a latch for accessing that chain. Etc.

    There are so many factor that impact performance of a workload - and the vast majority of these are dynamic and can change from one second to the next.

    Besides, what are the reasons for wanting to calculate how long a workload will take? How does that benefit the performance of the process, or the design of that process?

    In my view performance is about designing for flexibility and scalability by getting the basic rights. Not about guestimating workload times.

    Workload times are an output ito design and coding - not an input.
  • 12. Re: How to estimate total execution time of a query...?
    905562 Explorer
    Currently Being Moderated
    Good point, but comes with caveats IMO.

    The same workload (e.g. reading 5 x 8Kb blocks) is inconsistent - it can be slow (physical I/O). It can be fast (logical I/O). It can run into other processes traversing the same buffer chain for the same block - and having to spin to acquire a latch for accessing that chain. Etc.

    There are so many factor that impact performance of a workload - and the vast majority of these are dynamic and can change from one second to the next.

    Besides, what are the reasons for wanting to calculate how long a workload will take? How does that benefit the performance of the process, or the design of that >process?

    In my view performance is about designing for flexibility and scalability by getting the basic rights. Not about guestimating workload times.

    Workload times are an output ito design and coding - not an input.
    I agree, I typically use it if I'm asked to look at a 'long running' batch job - primarily for the rows processed section, in this site often you can work out roughly how many are to be processed and by using the monitor views you can get a rough approximation if the job is a million miles away from ending or not.

    Thing is, it needs the user to have a pretty decent understanding of the underlying data and the query being issued as well as what "normal" looks like.

    It also saves me some typing to check where the query spent most of its time and on what operations.


    However, as you absolutely correctly state, it carries caveats and really you need to know what you're doing with it.

    Edited by: mrk on Dec 14, 2012 1:01 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points