How to add a hint to ORACLE query without touching its text
If you’ve been a DBA long enough, you’ve probably seen multiple cases where certain SQL queries just refuse to cooperate.
I.e. you KNOW that the query is supposed to use index IDX1, but ORACLE stubbornly decides to take IDX2. Rats!
The usual suspect here is bad statistics and thus the usual advise is: start re-collecting them. But while this might help, the success is far from guaranteed! Moreover, with a ‘real production’ data statistics collection could take hours (days?) and you already have people screaming about slow performance … In other words, you need to fix the problem NOW and waiting until stats collection maybe fixes the problem a few hours down the road is just not an option!
Well, if the problem is that a bad index is chosen – the fastest way to fix it is to hint the query to chose a good one.
However, there might be some problems with this approach too. Adding SQL hint is still a “source code change”. And in many cases, the source code is either not accessible or access is so complicated that hinting your query can take weeks … Yikes!
Fortunately, there is a a way to attach SQL hints directly in the database itself without all that hassle … It’s not fully supported, but it works … and it can save your butt if you need to “fix” things quickly …
Here is what you need to do:
Step 1: Find your query block name
Reason being, ORACLE needs to know where in the query to apply the hint.
Assuming, you know your query SQL_ID:
define CHILD_NO=0
SET verify off
colu operation format A20
colu options format A25
colu object_name format A20
colu object_alias format A20
SELECT operation,options,object_name,object_alias
FROM v$sql_plan
WHERE sql_id='&SQL_ID'
AND child_number=&CHILD_NO
/
You should get something like:
-------------------- ------------------------- -------------------- --------------------
SELECT STATEMENT
TABLE ACCESS BY INDEX ROWID T T@SEL$1
INDEX RANGE SCAN T_D_IDX T@SEL$1
where @SEL1 is your query block name.
Step 2: “Construct” extended hint
Hints can be attached to your query in the form of a SQL profile.
However, ORACLE is very peculiar about profile hint syntax and, generally, requires extended form of hints that list table and query block names.
Simple hints (that you are probably used to) will not do!
I.e. to be recognized, this common hint:
will need to be transformed into its “extended” form:
If you are unsure what exactly ORACLE expects in “extended” hints, you can use this script to see extended hints for any SQL that exists in the shared pool.
Step 3: Create SQL profile
This is the final step and it’s the one that is NOT “officially” supported.
Even though SQL profiles are basically just a bunch of hints that attach to your statement, ORACLE originally designed them as a way to “fix” bad cardinality estimations for “special” data.
Thus, the only “supported” way to create profiles is to have ORACLE run SQL tuning job on your query, i.e.:
dbms_sqltune.execute_tuning_task(...);
etc ...
which may create profile at the end if it helps the query (no guarantees, though!)
Plus, the only hints that profiles can officially have are: OPT_ESTIMATE (“extended” form of good old CARDINALITY hints).
But if you look closely at DBMS_SQLTUNE package, you will find undocumented IMPORT_SQL_PROFILE procedure that allows “free form” SQL Profiles to be created.
Like this:
clsql_text CLOB;
BEGIN
SELECT sql_fulltext INTO clsql_text FROM v$sqlarea WHERE sql_id = '&SQL_ID';
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
sql_text => clsql_text,
profile => sqlprof_attr('INDEX(@"SEL$1" T@"SEL$1" "T_D_IDX")'),
name => 'PROFILE_&SQL_ID',
force_match => TRUE
);
END;
/
force_match=TRUE means that SQL profile will be used for all similar statements, regardless of the value of literals (if SQL has any).
Step 4: Verify that the plan has changed
At this point the problem should be resolved. If you look at the new execution plan, you should see message like:
-----
- SQL profile "PROFILE_0vt5t1thw4rzx" used FOR this statement
and execution plan should change.
If it hasn’t, either you made a typo in the hint definition or there is something else that prevents change in plans, i.e. current SPM baseline.
Correcting typos is easy: just drop existing profile and repeat step 3 with the new hint.
Good luck!
P.S. You can also follow this complete, albeit artificial example.