In general, we write down SQL sentences and we execute them without analyzing any metrics.
However, if the statement is going to be executed hundreds of times per hour, it should be optimized as much as possible in order to avoid potential performance issues.
In this case, we may find out that there is more than one way to write the SQL sentence. Each statement may also have different execution plans depending on the optimizer hints we use.
These hints are instructions we give to the database engine, for example, to use a particular index or not to use indexes at all.
If we compare different execution plans to identify the most optimized query, we may choose the SQL statement with the lower cost.
This might be right when there’s a wide difference between both costs, but when the gap is not significant and the execution time of each statement is similar, it´s not quite evident to identify which SQL better.
So, how can we determine which SQL statement is more efficient?
First of all, we need to take a look on 3 Oracle metrics:
Buffer Gets
When Oracle accesses a data block, it first checks to see if the data block is already inside the data buffer (cache).
If so, Oracle issues a logical I/O (as opposed to a physical get), and fetches the data block directly from RAM.
Disks Reads
When the data block is not in the buffer, Oracle issues a physical I/O to get the data.
Elapsed Time
Is the time (in microseconds) used by the statement for parsing/executing/fetching?
Example:
Let’s suppose we have 2 SQL statements (sql_1 and sql_2) that return the same result and we want to keep the one more optimized.
So we compare the running time and execution plan cost of each one:
Running Time
If we execute sql_1, it always lasts between 1 and 2 seconds.
If we execute sql_2, it always lasts between 1 and 2 seconds.
So we are not sure which statement is more efficient.
Execution Plan Cost
When we analyze the execution plan for sql_1 we see that the cost estimated by Oracle is 1045.
When we analyze the execution plan for sql_2 we see that the cost estimated by Oracle is 1002.
Remember the execution plan cost is calculated by the Oracle optimizer based on the statistics, which is basically an analysis of tables and indexes.
This cost is an estimation of effort needed by the database engine to execute the SQL statement.
Considering both costs we might think sql_2 is better.
Deeper Analysis
Now we should compare the BUFFER GETS, DISK READS and ELAPSED TIME.
We need to add comments to the statements in order to identify them later.
sql_1 : select /*EXEC01_SQL1*/ …
sql_2 : select /*EXEC01_SQL2*/ …
After executing both statements we can begin the analysis. We need to access a system view:
“select sql_text, buffer_gets, disk_reads, elapsed_time/1000000 from v$sql where sql_text like ‘%EXEC01_%’;”
And we get;
sql_text buffer_gets disk_reads elapsed_time/1000000
select /*EXEC01_SQL1*/ … 1503 26 1,402981
select /*EXEC01_SQL2*/ … 1510 26 1,521657
Despite having the same disk_reads, sql_1 got less buffer_gets and it finished in 1,4 seconds when sql_2 needed 1,5.
This indicates that sql_1 is a more efficient SQL statement.
For only one execution the difference of consumption between both statements is not very significant, but if we need to execute this query a thousand times per hour, we would better choose the right one.
We should repeat the test at least one more time.
We need to modify the comments to identify the second execution.
sql_1 : select /*EXEC02_SQL1*/ …
sql_2 : select /*EXEC02_SQL2*/ …
After executing both statements we access the system view again.
“select sql_text, buffer_gets, disk_reads, elapsed_time/1000000 from v$sql where sql_text like ‘%EXEC02_%’;”
And we get;
sql_text buffer_gets disk_reads elapsed_time/1000000
select /*EXEC02_SQL1*/ … 1501 23 1,400210
select /*EXEC02_SQL2*/ … 1509 23 1,519897
The second test confirms the results we got before: sql_1 consumes fewer resources than sql_2.
Conclusion
When you have more than one way to write down a SQL statement and you need to figure out which one is more efficient, you should not make your decision based only in the execution plan cost.
You need to execute each statement (at least twice) and compare the buffer_gets, disk_reads and elapsed_time to get an accurate measure.
Remember a small improvement is multiplied by the times the statement is executed.
