10 Replies Latest reply: May 7, 2010 7:14 AM by Billy~Verreynne RSS

    cost in explain plan vs elapsed time

    573141
      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
          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
            that means, we should trust only trace?
            • 3. Re: cost in explain plan vs elapsed time
              SomeoneElse
              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
                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
                  Timur Akhmadeev
                  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
                    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
                      Billy~Verreynne
                      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
                        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
                          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
                            Billy~Verreynne
                            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.