How to tune a SQL that cannot be tuned ? — Tosska Technologies Limited

Some mission-critical SQL statements are already reached their maximum speed within the current indexes configuration. It means that those SQL statements are not able to be improved by syntax rewrite or Hints injection. Most people may think that the only way to improve this kind of SQL may be by upgrading hardware. For example, the following SQL statement has every column in WHERE clause is indexed and the best query plan is generated by Oracle already. There is no syntax rewrite or hints injection that can help Oracle to improve the SQL performance.

SELECT EMP_ID,
EMP_NAME,
SAL_EFFECT_DATE,
SAL_SALARY
FROM EMPLOYEE,
EMP_SAL_HIST,
DEPARTMENT,
GRADE
WHERE EMP_ID = SAL_EMP_ID
AND SAL_SALARY <200000
AND EMP_DEPT = DPT_ID
AND EMP_GRADE = GRD_ID
AND GRD_ID<1200 AND EMP_DEPT<’D’

Here the following is the query plan and execution statistics of the SQL, it takes 2.33 seconds to extract all 502 records. It is not acceptable for a mission-critical SQL that is executed thousands of times in an hour. Do we have another choice if we don’t want to buy extra hardware to improve this SQL?

Introduce new plans for Oracle’s SQL optimizer to consider
Although all columns in the WHERE clause are indexed, can we build some compound indexes to help Oracle’s SQL optimizer to generate new query plans which may perform better than the original plan? Let’s see if we adopt the common practice that the following EMPLOYEE’s columns in red color can be used to compose a concatenated index (EMP_ID, EMP_DEPT, EMP_GRADE).

WHERE EMP_ID = SAL_EMP_ID
AND SAL_SALARY <200000
AND EMP_DEPT = DPT_ID
AND EMP_GRADE = GRD_ID
AND GRD_ID<1200
AND EMP_DEPT<’D’

CREATE INDEX C##TOSSKA.TOSSKA_09145226686_V0043 ON C##TOSSKA.EMPLOYEE
(
EMP_ID,
EMP_DEPT,
EMP_GRADE
)

The following is the query plan after the concatenated index is created. Unfortunately, the speed of the SQL is 2.40 seconds although a new query plan is introduced by Oracle’s SQL optimizer.

To be honest, it is difficult if we just rely on common practices or human knowledge to build indexes to improve this SQL. Let me imagine that if we got an AI engine that can help me to try the most effective compound indexes to explore Oracle’s SQL optimizer potential solutions for the SQL. The following concatenated indexes are the potential recommendation by the imagined AI engine.

CREATE INDEX C##TOSSKA.TOSSKA_13124445731_V0012 ON C##TOSSKA.EMP_SAL_HIST
(
SAL_SALARY,
SAL_EFFECT_DATE,
SAL_EMP_ID
)
CREATE INDEX C##TOSSKA.TOSSKA_13124445784_V0044 ON C##TOSSKA.EMPLOYEE
(
EMP_GRADE,
EMP_DEPT,
EMP_ID,
EMP_NAME
)

The following is the query plan after these two concatenated indexes are created and the speed of the SQL is improved to 0.13 seconds. It is almost 18 times better than that of the original SQL without the new indexes.

The above indexes include some columns that appear on the SELECT list of the SQL and there is a correlated indexes relationship for Oracle’s SQL optimizer to generate the query plan, it means that missing any columns of the recommended indexes or reshuffling of the column position of the concatenated indexes may not be able to produce such query plan structure. So, it is difficult for a human expert to compose these two concatenated indexes manually. I am glad to tell you that this kind of AI engine is actually available in the following product.

https://www.tosska.com/tosska-sql-tuning-expert-pro-tse-pro-for-oracle/

Originally published at https://tosska.com on October 28, 2021.