Intermediate SQL Color Coded SQL, UNIX and Database Essays


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 SQL_ID='your 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:

OPERATION            OPTIONS                   OBJECT_NAME          OBJECT_ALIAS
-------------------- ------------------------- -------------------- --------------------
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:

INDEX(t t_d_idx)

will need to be transformed into its “extended” form:

INDEX(@"SEL$1" T@"SEL$1" "T_D_IDX")

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.:

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;
SELECT sql_fulltext INTO clsql_text FROM v$sqlarea WHERE sql_id = '&SQL_ID';

  sql_text => clsql_text,
  profile => sqlprof_attr('INDEX(@"SEL$1" T@"SEL$1" "T_D_IDX")'),
  name => 'PROFILE_&SQL_ID',
  force_match => TRUE

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.

EXEC dbms_sqltune.drop_sql_profile('PROFILE_&SQL_ID');

Good luck!

P.S. You can also follow this complete, albeit artificial example.

Comments (6) Trackbacks (0)
  1. Very well explained – thanks. I found it easier to get the required extended hint syntax as follows: –


  2. Thanks for the great article. Can one use this technique if there are multiple select clauses in the query? In my case I have a query with 4 select segments and multiple tables. One of the select segments does a Full table scan where I believe an index would help so i want to add the hint;2 other select pieces the indexes are being used and a 4 th select piece where the Oracle suggested sql profile enabled a correct index to be used. Thus i have a sql profile that is helping to some extent but I want to make it even better with an hint in one section. if that makes sense. I cannot tweak an existing profile since the import function says profile already exists and i am afraid i create a new profile with just 1 select section that won’t cut it.

  3. Hello Ranjith,

    You can definitely use this technique for complex SQLs with multiple nested parts. When ORACLE parses a complex query, it splits it into “query blocks”, which you can reference by name in hints. You can also find what the query block names are for any query already in a shared pool (alternatively, you can supply your own names in a query).

    So, to place the hint into a proper place in a query, find the required query block name and then supply it along with the hint. My post explains how to do it in detail.

    Maxym Kharchenko

  4. Nice article Maxym !!

    Another way to extract those hints is from plan_table of explain plan as we have other_xml column in that table as well.

    I have similar article on using that


    Advait Deo

  5. Thanks, Advait. I’ m glad that you found it useful. And looks like I found another good blog to read đŸ™‚

Leave a comment

No trackbacks yet.