How to manually add hints into a SQL using SQL Profiles without changing the SQL text?

Assumptions

This article assumes that the user following instructions from this article has detail understanding of the Oracle RDBMS and know how to use the SQL profiles in the Oracle RDBMS. Instructions laid down in this article are only tested on Oracle RDBMS 10.2.0.4 and may not work on other releases of the Oracle RDBMS.

Identify the query block of the SQL to be hinted

Oracle CBO references the SQL Text using the Query Block Name so first of all identify the name of the Query Block using the following SQL query. You can either find the query block by using the text of your SQL or by SQL id.

-- Search the Query Block Name by text of your SQL

SELECT operation,options,object_owner,object_name,object_alias "Query Block"
FROM v$sql_plan
WHERE sql_id in (SELECT sql_id FROM v$sqlarea WHERE sql_text like '%&yoursqltext%' and sql_text not like '%sqlarea%')
ORDER BY id;
-- Search the Query Block by SQL ID
SELECT operation,options,object_owner,object_name,object_alias "Query Block"
FROM v$sql_plan
WHERE sql_id in (SELECT sql_id FROM v$sqlarea WHERE sql_id = '&yoursqlid' and sql_text not like '%sqlarea%')
ORDER BY id;

Force Oracle CBO to use hint by creating and importing custom SQL profile

Once the name of the Query Block is know then one can create and import the custom SQL profile having only the hints that one may want to add into the SQL. By following the following instructions, one is not changing the SQL text at all because SQL profile will just append the specified hints to the SQL when ever Oracle CBO parse and execute this SQL.

One has to be very careful while using the following procedure because if there is a mistake in referring the query block or table alias then SQL profile will not be implemented successfully.

The correct syntax to refer an object inside the DBMS_SQLTUNE package is (@"<Name of Query Block>" "<Object / Table Alias>"@"Name of Query Block"). For example to add a leading hint on a table alias T24 inside SEL$1 query block will look like LEADING (@"SEL$1" "T24"@"SEL$1")

Execute the following PL/SQL block to import the custom SQL profile with the hint you may wish to append to your SQL. We are adding a leading hint to T24 table inside the SEL$1 query block on sqlid d23vknpka8rvr. Replace the SEL$1 with your actual query block name and d23vknpka8rvr with your actual sqlid.

DECLARE
clsql_text CLOB;
BEGIN
SELECT sql_fulltext INTO
clsql_text
FROM v$sqlarea
where sql_id = 'd23vknpka8rvr'<br />DBMS_SQLTUNE.IMPORT_SQL_PROFILE(sql_text =clsql_text,profile = sqlprof_attr('LEADING (@"SEL$1" "T24"@"SEL$1")'),name ='PROFILE_ d23vknpka8rvr',force_match =TRUE);</p>'
January 24, 2012