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?
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>
user570138 wrote: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.
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.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.
my aim is to measure the query performance improvements, if any, by partitioning an existing tableWhy 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?