When dealing with complex Oracle queries, the optimizer sometimes prefers a full table scan, which may not be optimal for every scenario. In cases where dropping or making certain indexes invisible is impractical due to their importance in other queries, a SQL Patch with optimizer hints can help achieve optimal performance for specific queries.

Creating a SQL Patch with Full Table Scan Hint

To enforce a full table scan for a particular query, we can use a SQL Patch with hints. First, we examine the query plan and get the appropriate predicate hint from the plan output. Here’s an example process:

Step 1: Display the Execution Plan

Use DBMS_XPLAN.display to retrieve the execution plan and find the predicate to apply:

SELECT * FROM TABLE(DBMS_XPLAN.display(null, null, 'ADVANCED'));

In this example, we find the predicate hint FULL(@"SEL$3" "TTABLE"@"SEL$3") in the output.

Step 2: Apply the SQL Patch

Using DBMS_SQLDIAG.create_sql_patch, we can add the full table scan hint directly to the query via SQL Patch:

DECLARE
    l_patch_name VARCHAR2(32767);
BEGIN
    l_patch_name := SYS.DBMS_SQLDIAG.create_sql_patch(
        sql_id => 'bt5gut1vj6j68',
        hint_text => 'FULL(@"SEL$3" "TTABLE"@"SEL$3")',
        name => 'Patch_2'
    );
END;

Example 2: Using a Hash Join Hint

In cases where hash joins are problematic, we can similarly use a SQL Patch to disable them. Here’s an example of creating a SQL Patch to disable hash joins:

DECLARE
    patch_name VARCHAR2(2000);
BEGIN
    patch_name := SYS.DBMS_SQLDIAG.create_sql_patch(
        sql_text => 'SELECT name FROM dbdeeds WHERE DATA_SET_ID = ''DEEDS'';',
        hint_text => '_hash_join_enabled(FALSE)',
        name => 'PATCH_3'
    );
END;

Example 3: Using sqlid

If the query is very large, you can retrieve the SQL text from the SQL ID and apply hints through the SQL Patch:

DECLARE
    v_sql_text VARCHAR2(1500);
BEGIN
    -- Retrieve SQL text for the given SQL ID
    SELECT sql_fulltext INTO v_sql_text FROM gv$sql WHERE sql_id = '102kyp38prfpn';

    -- Create SQL patch with hints
    DBMS_SQLDIAG.CREATE_SQL_PATCH (
        sql_text => v_sql_text,
        hint_text => 'noparallel', -- Add optimizer hints if needed
        name => 'Patch_for_SQL_ID_102kyp38prfpn',
        status => 'ENABLED',
        enabled => TRUE
    );
END;

Conclusion

Oracle SQL Patch is a powerful tool for enforcing specific optimizer hints on complex queries where default optimizer behavior may not be ideal. By utilizing DBMS_SQLDIAG, we can dynamically influence the execution path without altering the query structure, ensuring that each query runs as efficiently as possible for its unique requirements.