SQL Patches: Enhancing SQL Performance with Hints

 In today's blog post, we dive into the art of SQL optimization by creating SQL patches with hints. This technique is invaluable for improving the performance of your SQL statements in Oracle databases. Specifically, we'll focus on adding a hint to an application SQL statement, ensuring efficient execution.

Scenario

Imagine you have the following SQL query that needs optimization:

Our goal is to apply a hint to this query using a SQL patch.

Step-by-step guide

Identify the SQL_ID

First, we need to find the SQL_ID of the query. Execute the following command:

SELECT d.deptno, d.dname, MAX(sal)
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND d.deptno > 10
GROUP BY d.deptno, d.dname;

This query returns the SQL_ID associated with your target query.

Create the SQL Patch

Using the identified SQL_ID, we create a SQL patch with the desired hint. Here's a script to achieve this:

DECLARE
    v1 VARCHAR2(128);
BEGIN
    v1 := dbms_sqldiag.create_sql_patch(
        sql_id  => 'fzsf6kw7q2vxt',
        name    => 'optimizer_extended_cursor_sharing_rel',
        hint_text => q'{opt_param('_optimizer_extended_cursor_sharing_rel' 'none')}'
    );
    dbms_output.put_line(v1);
END;
/

This PL/SQL block creates a SQL patch named optimizer_extended_cursor_sharing_rel for the specified SQL_ID, applying the hint opt_param('_optimizer_extended_cursor_sharing_rel' 'none').

Verify the SQL Patch

After creating the patch, verify it with the following commands:

SET LINESIZE 400
COL sql_text FOR A50
COL name FOR A37
SELECT name, status, created, sql_text 
FROM dba_sql_patches 
WHERE name = 'optimizer_extended_cursor_sharing_rel';

This query displays details of the created SQL patch, ensuring it is enabled and correctly applied.

Review the Execution Plan

To see the impact of the patch on the execution plan, run:

SELECT * 
FROM TABLE(dbms_xplan.display_cursor(sql_id => 'fzsf6kw7q2vxt', cursor_child_no => 2, 

FORMAT => 'ADVANCED +ROWS +BYTES +COST +PARALLEL +PARTITION +IOSTATS +MEMSTATS +ALIAS +PEEKED_BINDS +OUTLINE +PREDICATE -PROJECTION +REMOTE +NOTE'));

This command provides an advanced view of the execution plan, including the applied hints and their effects.

Disable or Drop the SQL Patch

If you need to disable or remove the patch, use the following commands:

To disable the patch:

EXEC DBMS_SQLDIAG.ALTER_SQL_PATCH('optimizer_extended_cursor_sharing_rel', 'STATUS', 'DISABLED');

To drop the patch:

EXEC DBMS_SQLDIAG.DROP_SQL_PATCH(name => 'optimizer_extended_cursor_sharing_rel');

Conclusion 

By following these steps, you can effectively create and manage SQL patches to enhance the performance of your SQL statements. This method allows for precise control over query execution plans, ensuring optimal database performance. Keep experimenting with different hints and patches to master SQL optimization in Oracle.

Happy tuning!