When we search for untuned SQLs we can look for SQL that its execution time is objectively long, but we should also search for SQL which has high amount of short executions, or in other words frequently executed SQL. When dealing with frequently executed SQLs, even a small improvement in one execution (average execution time) will yield a performance improvement, since this “small” improvement of one execution is multiplied by its many executions.
In other words, when looking at v$sql, look for the ones with high executions:
select * from v$sql order by executions desc;
or for the ones with high average execution time:
select * from v$sql order by elapsed_time/executions desc;
I was visiting a customer which one of his top three heaviest SQLs was a query executed 1.5M a day, with average execution of 0.013 sec. Allegedly, when we look at its average execution time, this SQL won’t be our first candidate for tuning, but when we look at its overall consumption (execution time of all its executions), well, that would be another story.
I have tuned this query by simply changing the order of index columns, causing average execution to go down to 0.005 sec (2.6 times faster).
After that overall resource consumption of the query went down dramatically. Complete tuning flow can be visually seen in the following pdf:
https://www.dboptimize.co.il/Oracle_frequently_executed_sql.pdf
Note: The example shown is for Oracle, thought the methodology of tuning fast and frequently executed query is relevant for all databases: Oracle, MSSQL, DB2, Sybase, Mysql, etc.