This content has been marked as final. Show 10 replies
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.
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.
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>
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
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.
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.
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 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?
This can be implemented in a start/stop watch fashion (instead of measuring time, measuring I/O) using the v$sesstat virtual performance view.