How to tune a SQL that cannot be tuned?

Discussion in 'Oracle' started by Richard To, Sep 13, 2021.

  1. Richard To

    Richard To Member

    Joined:
    Sep 18, 2017
    Messages:
    63
    Likes Received:
    21
    Trophy Points:
    8
    Gender:
    Male
    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?
    [​IMG]
    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.
    [​IMG]
    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)

    [​IMG]
    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.
    [​IMG]
    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://tosska.com/tosska-sql-tuning-expert-pro-tse-pro-for-oracle/
    [​IMG]
     
    shabbir likes this.

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice