This discussion is archived
10 Replies Latest reply: May 7, 2010 5:14 AM by BillyVerreynne RSS

cost in explain plan vs elapsed time

573141 Newbie
Currently Being Moderated
hi gurus,

i have two tables with identical data volume(same database/schema/tablespace) and the only difference between these two is, one partitioned on a date filed.

statistics are up to date.

same query is executed against both tables, no partition pruning involved.

select ....... from non-partitioned
execution plan cost=92
elapsed time=117612


select ... from partitioned
execution plan cost=3606
elapsed time=19559

though plan cost of query against non-partitioned is quite less than partitioned, elapsed time in v$sqlarea is higher than partitioned.

what could be the reason please?


thanks,
charles

Edited by: user570138 on May 6, 2010 6:54 AM
  • 1. Re: cost in explain plan vs elapsed time
    SomeoneElse Guru
    Currently Being Moderated
    A good example of why it's generally a waste of time to compare Cost numbers between different queries.
  • 2. Re: cost in explain plan vs elapsed time
    573141 Newbie
    Currently Being Moderated
    that means, we should trust only trace?
  • 3. Re: cost in explain plan vs elapsed time
    SomeoneElse Guru
    Currently Being Moderated
    that means, we should trust only trace?
    I'm not sure what you're trying to accomplish.
  • 4. Re: cost in explain plan vs elapsed time
    riedelme Expert
    Currently Being Moderated
    As someone else pointed out the cost is extremely iffy for query comparisions. Its a guess, and one that is not always right. Hard numbers - timings, system resources - are better.
  • 5. Re: cost in explain plan vs elapsed time
    680087 Pro
    Currently Being Moderated
    riedelme wrote:
    Its a guess
    No, it's not. It's an estimation - that is not a guess.
  • 6. Re: cost in explain plan vs elapsed time
    573141 Newbie
    Currently Being Moderated
    if we really want to analyze two queries without executing the query....i thought...explain plan is the only option,
    and if statistics are up to date optimizer will be able to show correct plan in 99% cases.

    is there any other way to analyze queries without actual execution?
  • 7. Re: cost in explain plan vs elapsed time
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    You can use EXPLAIN PLAN to get the execution plan for a SQL statement. You can use DBMS_SQL to parse a SQL without executing.

    But why?

    It seems like you want to write code to play CBO - dynamically decide what is faster/better/optimal and then run that. And in all honesty, that would be a ridiculous thing to do, given the complexity, variables and variances that need to be considered..

    Fact is that the very same SQL with the very same execution plan for the very same data/rows can have different execution/elapsed times.

    Example:
    SQL> set timing on
    SQL> select count(*) from all_objects;
     
      COUNT(*)
    ----------
         72796
     
    Elapsed: 00:00:10.16
    SQL>
    SQL> select count(*) from all_objects;
     
      COUNT(*)
    ----------
         72796
     
    Elapsed: 00:00:03.05
    SQL>
    If this is the case with the same SQL.. how do you want to measure it against another so-called similar SQL to determine which one has a better elapsed execution time?
  • 8. Re: cost in explain plan vs elapsed time
    573141 Newbie
    Currently Being Moderated
    hi bill,

    many thanks for the details.

    if elapsed time value is very volatile(with the difference in explain plan cost) , then how can i compare the performance of the query? in my case i want to compare the performance change before and after table partitioning.


    my aim is to measure the query performance improvements, if any, by partitioning an existing table

    thanks again,
    charles
  • 9. Re: cost in explain plan vs elapsed time
    389403 Journeyer
    Currently Being Moderated
    If Your Optimizer, provides lower costs for longer running queries, it means that it considers not optimal plan. It is not normal! This issue is correctable. You need to gather system statistics, but do it correctly! Check initialization parameters that can affect the cost estimation of the Optimizer.
  • 10. Re: cost in explain plan vs elapsed time
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    user570138 wrote:

    if elapsed time value is very volatile(with the difference in explain plan cost) , then how can i compare the performance of the query?
    Note that the same query with same execution plan and same data can provide different execution times - and due to a number of factors. The primary one being that the first time the query is executed, it performs physical I/O and loads the data into the buffer cache - where the same subsequent query finds this data via cheaper and faster logical I/O in the cache.
    in my case i want to compare the performance change before and after table partitioning.
    Then look at the actual utilisation cost of the query and not (variant) elapsed execution time. The most expensive operation on a db platform is typically I/O. The more I/O, the slower the execution time.

    I/O can be decreased in a number of ways. The usual approach in the database environment is to provide better or alternative data structures, in order to create more optimal I/O paths.

    So instead of using a hash table and separate PK b+tree index, using an index organised table. Instead of a single large table with indexes, a partitioned table with local indexes. Instead of joining plain tables, joining tables that have been clustered. Etc.

    In most cases, when done correctly, these physical data structure changes do not even impact the application layer. The SQL and code in this layer should be blissfully unaware of the physical structure changes done to improve performance.

    Likewise, changes in the logical layer (data model changes) can also improve performance. This of course does impact the application layer - and requires a proper design and implementation in the physical layer. Often proper data modeling is overlooked and flaws in it is attempted to be fixed by hacking the physical layer.
    my aim is to measure the query performance improvements, if any, by partitioning an existing table
    Why not measure current I/O before an operation, and then after the operation - and use the difference to determine the amount of I/O performed by that operation?

    This can be implemented in a start/stop watch fashion (instead of measuring time, measuring I/O) using the v$sesstat virtual performance view.

Legend

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