How to Tune SQL Statements to Run SLOWER… but Make Users Feel BETTER (Oracle)?

Your end-users may keep on complaining about some functions of their database application are running slow, but you may found that those SQL statements are already reached their maximum speed in the current Oracle and hardware configuration. There may be no way to improve the SQL unless you are willing to upgrade your hardware. To make your users feel better, sometimes, you don’t have to tune your SQL to run faster but to tune your SQL to run slower for certain application’s SQL statements.

This is an example SQL that is used to display the information from tables Emp_sal_hist and Employee if they are satisfied with certain criteria. This SQL is executed as an online query and users have to wait for at least 5 seconds before any data will be shown on screen after the mouse click.

select * from employee a,emp_sal_hist c
where a.emp_name like 'A%'

and a.emp_id=c.sal_emp_id
and c.sal_salary<1800000​

order by c.sal_emp_id

Here the following is the query plan and execution statistics of the SQL, it takes 10.41 seconds to extract all 79374 records and the first records return time ”Response Time” is 5.72 seconds. The query shows a MERGE JOIN of EMPLOYEE and EMP_SAL_HIST table, there are two sorting operations of the corresponding tables before it is being merged into the final result. It is the reason that users have to wait at least 5 seconds before they can see anything shows on the screen.

As the condition “a.emp_id = c.sal_emp_id”, we know that “ORDER BY c.sal_emp_id“ is the same as “ORDER BY a.emp_id“, as SQL syntax rewrite cannot force a specified operation in the query plan for this SQL, I added an optimizer hint /*+ INDEX(@SEL$1 A EMPLOYEE_PK) */ to reduce the sorting time of order by a.emp_id.


SELECT /*+ INDEX(@SEL$1 A EMPLOYEE_PK) */ *
FROM employee a, emp_sal_hist c
WHERE a.emp_name LIKE 'A%'

AND a.emp_id = c.sal_emp_id
AND c.sal_salary < 1800000​

ORDER BY c.sal_emp_id

Although the overall Elapsed Time is 3 seconds higher in the new query plan, the response time is now reduced from 5.72 seconds to 1.16 seconds, so the users can see the first page of information on the screen more promptly and I believe most users don’t care whether there are 3 more seconds for all 79374 records to be returned. That is why SQL tuning is an art rather than science when you are going to manage your users’ expectations.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for Oracle automatically.
https://tosska.com/tosska-sql-tuning-expert-pro-tse-pro-for-oracle/