Oracle Database 19c -- How to Add a Hint using SQL Patch

Problem

You want to add a hint to an SQL statement using the SQL Patch feature

Solution

Starting with 12.2 release, there is a public API - DBMS_SQLDIAG.CREATE_PATCH, which allow to create an sql patch for a SQL statement

For versions below 12.2, there is a undocumented internal API procedure available to create an SQL Patch, thus making this option to be applicable for any database version starting with 11.2.0.3:

begin
  sys.dbms_sqldiag_internal.i_create_patch (
     sql_text  => '<sql_text>',
     hint_text => '<hint>',
     name      => '<sql_patch_name>');
end;

For <sql_text> use either DBA_HIST_SQLTEXT.SQL_TEXT or V$SQL.SQL_FULLTEXT by sql_id or any other means to get the SQL text for the statement

For <hint> use any valid supported hint, the only requirement is to use proper alias format, for example FULL(A) might need to be specified as FULL(@"SEL$1" "A"@"SEL$1").

To help identify the right alias for a table, use either

select * from table(dbms_xplan.display_cursor('&SQL_ID', &CHILD_NUMBER, 'OUTLINE'); 

or

select * from table(dbms_xplan.display_awr('&SQL_ID', '&PLAN_HASH_VALUE', NULL, 'OUTLINE');

and in the 'Outline Data' section, the alias can be found by text search against the table name or table alias as used in the sql text

Once an SQL Patch created, to find the hints in the SQL Patch, use:

select cast(extractvalue(value(x), '/hint') as varchar2(500)) as outline_hints
from xmltable('/outline_data/hint'
passing (select xmltype(comp_data) xml
from sys.sqlobj$data
where signature = (select EXACT_MATCHING_SIGNATURE from v$sqlarea where sql_id = '<sql_id>'))) x