SQL Profile has higher priority than Hint!

Hints on SQL statement do not work???
You may have a syntax error, double check it. But if the syntax is OK, then this problem may be caused by the SQL profile, which doesn’t let hints to work.

Here, in this post I will explain the situation when there is applied SQL profile for some SQL statement and any attempt to use hints on that statement fails.

To solve this problem you should drop that SQL profile or disable it.

Note: This behavior has advantage. It assures that the SQL plan of the SQL statement will not be changed for future runs.

Let’s start…

–Create test table

create table test (n1 number
, n2 number );

–Insert some values

begin
for i in 1 .. 100000
loop
insert into test values(i,i+1);
commit;
end loop;
end;

–Create index

create index test_idx1 on test(n1);

–Analyze table

analyze table test estimate statistics;

–Run the following SQL statement

select /*+ no_index(test test_idx1) */ *
from test where n1=2;

–Calculate explain plan

explain plan for select /*+ no_index(test test_idx1) */ *
from test where n1=2;

select * from table(dbms_xplan.display);

–Output

————————————————————————-
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |     1 |     8 |    49   (5)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     8 |    49   (5)| 00:00:01 |
————————————————————————–

–Create SQL tuning task

declare
my_task_name varchar2(30);
my_sqltext   clob;
begin
my_sqltext := ‘select /*+ no_index(test test_idx1) */ * from test where n1=2’;

my_task_name := dbms_sqltune.create_tuning_task(
sql_text    => my_sqltext,
scope       => ‘comprehensive’,
time_limit  => 60,
task_name   => ‘my_sql_tuning_task’);
end;

–Execute SQL tuning task

begin
dbms_sqltune.execute_tuning_task( task_name => ‘my_sql_tuning_task’);
end;

–Generate report

select dbms_sqltune.report_tuning_task( ‘my_sql_tuning_task’)
from dual;

–Output

…

Recommendation (estimated benefit: 95.87%)
——————————————
– Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name =>
‘my_sql_tuning_task’, replace => TRUE);
…
—————————————————————————————–
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————————–
|   0 | SELECT STATEMENT            |           |     1 |     8 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST      |     1 |     8 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_IDX1 |     1 |       |     1   (0)| 00:00:01 |
—————————————————————————————–

–Accept SQL profile

begin
dbms_sqltune.accept_sql_profile(task_name =>’my_sql_tuning_task’
,replace => true
,name => ‘my_profile_name’);
end;

–Calculate explain plan again

explain plan for select /*+ no_index(test test_idx1) */ *
from test where n1=2;

select * from table(dbms_xplan.display);

–Output

—————————————————————————————–
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————————–
|   0 | SELECT STATEMENT            |           |     1 |     8 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST      |     1 |     8 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_IDX1 |     1 |       |     1   (0)| 00:00:01 |
—————————————————————————————–

Note: We have indicated not to use index(/*+ NO_INDEX(TEST TEST_IDX1) */ ), but as explain plan shows, Oracle still uses this index.

If we drop sql profile hint will work. Let’s check it:

begin
dbms_sqltune.drop_sql_profile(name => ‘my_profile_name’);
end;

–Calculate explain plan

explain plan for select /*+ no_index(test test_idx1) */ *
from test where n1=2;

select * from table(dbms_xplan.display);

–Output

————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |     1 |     8 |    49   (5)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     8 |    49   (5)| 00:00:01 |
————————————————————————–

So SQL profile has higher priority than hints!!!<- Important