How to use SQL Patches to tuning SQL without the need to change your source code

Discussion in 'Oracle' started by Richard To, Jul 19, 2019.

  1. Richard To

    Richard To Member

    Sep 18, 2017
    Likes Received:
    Trophy Points:
    SQL Patches is part of the features provided by SQL Repair Advisor which is used to fix a SQL statement’s critical failures such as returning wrong result. The SQL Repair Advisor analyzes the problematic statement and in many cases recommends a SQL patch to repair the statement. The SQL patch is to influence the Oracle SQL optimizer to choose an alternate execution plan for future executions, instead of using the original problematic execution plan. There is a public API call to create SQL patches manually provided by Oracle Database 12c Release 2 onwards. The DBMS_SQLDIAG.CREATE_SQL_PATCH package can help users to create a SQL Patch for specific SQL statement for SQL tuning purpose. You can change a bad performance SQL statement’s execution plan without the need to modify the program source code as the following example:

    If you have a SQL with

    SELECT * FROM employees
    WHERE emps_dept IN (SELECT dpts_id FROM departments
    WHERE dpts_avg_salary <200000)​

    You may want to add Hints to improve the SQL statement and don’t want to change your source code. The following SQL Patches deployment script will be useful.


    Patch_name VARCHAR2(32767);


    Patch_name := SYS.DBMS_SQLDIAG.create_sql_patch(

    sql_text => 'SELECT * FROM employees WHERE emps_dept IN (SELECT dpts_id FROM departments WHERE dpts_avg_salary <200000)',


    name => 'my_sql_patch_name');


    If your database version is before Oracle database 12c Release 2, you must use this package DBMS_SQLDIAG_INTERNAL.i_create_patch instead. Both SQL text and SQL ID is able to be used for SQL hints injection. The injected hints for your SQL should be placed in hint_text input parameter. There is only one line of Hints text you can use for a SQL and there is no way to define your own query block name for any subqueries’ block. So, if your SQL has multiple subqueries and you want to instruct Oracle to do something in subqueries’ blocks, you must use Oracle default query block names in your injected hints text.


    This hints text in the above example shows that @SEL$1 and @SEL$2 are default query block names provided by Oracle in the execution plan of the SQL. The Hints tells Oracle use index search for EMPLOYEES table in query block @SEL$1 and also use index search for DEPARTMENTS in query block @SEL$2.

    Pros and cons of using SQL Patches to tune SQL

    SQL Patches is more flexible to accept hints instructions without SQL Plan Baselines’ limitations, complex hints with parallel operations are normally accepted by SQL patches. There is no additional maintenance effort to tell Oracle to use the SQL Patches after it is created. Oracle will use the stored hints to optimize any SQL with the same SQL ID or SQL Text and generate a better performance execution plan. Furthermore, you can also use SQL Patches to disable a SQL with a destructive hints already written in a package application or even use it to control a bind-aware SQL execution behavior.

    As the injected hints text must be placed in one text line and using default query block name only, manually compose a desire Hints to improve a SQL statement will be a difficult task for most SQL developers especially for complex SQL statements with many subqueries.

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