Recently i was asked to assist an ongoing issue at a customers site were one query was giving a a lot of problems. After a short investigation it turned out the application was using USE_NL hint in this select statement, which worked fine on a small table but crippled performance as the table grew. Now for several reasons the application code could not be changed within reasonable timeframe, so i created a SQL profile for this statement, which ignored all optimizer hints used in the statement.
Lets execute a query with an index hint as an example (using pipo_de_clown as way to easily find it in the cursor cache, if your not dutch: Google the phrase) and lets have a look at its plan:
SQL> select /*+ index (test_data t1) pipo_de_clown */* from test_data where 0=1;
no rows selected
SQL> select sql_id,sql_text from v$sql where sql_text like '%pipo_de_clown%';
SQL_ID SQL_TEXT
------------- ----------------------------------------------------------------------------------------------------
3pusfgjmytwzm select sql_id,sql_text from v$sql where sql_text like '%pipo_de_clown%'
33gn3bmt3kkw2 select /*+ index (test_data t1) pipo_de_clown */* from test_data where 0=1
2 rows selected.
SQL> select * from table(dbms_xplan.display_cursor('33gn3bmt3kkw2'));
PLAN_TABLE_OUTPUT
------------- ---------------------------------------------------------------------------------------------------
SQL_ID 33gn3bmt3kkw2, child number 0
-------------------------------------
select /*+ index (test_data t1) pipo_de_clown */* from test_data where
0=1
Plan hash value: 3034433771
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST_DATA | 10000 | 224K| 65 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | T1 | 10000 | | 26 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
Note
-----
- automatic DOP: skipped because of IO calibrate statistics are missing
25 rows selected.
The CBO did exactly what i asked it to do and used my index. Now, lets say that for you need this hint to be removed from this statement and let the CBO do all the smart thinking (which is in general a good idea anyway). In order to do this, we create a SQL Profile for this statement and add the hint IGNORE_OPTIM_EMBEDDED_HINTS as an attribute to this profile. This hint will make the CBO ignore all hints used in this particular statement:
SQL> begin
2 dbms_sqltune.import_sql_profile(
3 name => 'test_profile',
4 category => 'DEFAULT',
5 sql_text => 'select /*+ index (test_data t1) pipo_de_clown */* from test_data where 0=1',
6
profile => sqlprof_attr('IGNORE_OPTIM_EMBEDDED_HINTS')); 7 end;
8 /
PL/SQL procedure successfully completed.
So now we have our profile in place, we can now invalidate this cursor and let the new plan with our newly created SQL Profile take its place:
SQL> select
address, hash_value from v$sqlarea where sql_id='33gn3bmt3kkw2';pr
ADDRESS HASH_VALUE
---------------- ----------
000000008E67D860 3373364990
SQL> exec dbms_shared_pool.purge('000000008E67D860,3373364990', 'C');
PL/SQL procedure successfully completed.
Lets execute the exact same query as i had earlier on, including the index hint and have a look at the execution plan:
SQL> select /*+ index (test_data t1) pipo_de_clown */* from test_data where 0=1;
no rows selected
SQL> select sql_id,sql_text from v$sql where sql_text like '%pipo_de_clown%';
SQL_ID SQL_TEXT
------------- ----------------------------------------------------------------------------------------------------
3pusfgjmytwzm select sql_id,sql_text from v$sql where sql_text like '%pipo_de_clown%'
6rmm76g4j2xry select /*+ index (test_data t1) pipo_de_clown */* from test_data where 0=1
2 rows selected.
SQL> select * from table(dbms_xplan.display_cursor('33gn3bmt3kkw2'));
PLAN_TABLE_OUTPUT
------------- ---------------------------------------------------------------------------------------------------
SQL_ID 33gn3bmt3kkw2, child number 0
-------------------------------------
select /*+ index (test_data t1) pipo_de_clown */* from test_data where
0=1
Plan hash value: 2744875046
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| TEST_DATA | 10000 | 224K| 12 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
Note
-----
- automatic DOP: skipped because of IO calibrate statistics are missing
- SQL profile test_profile used for this statement
25 rows selected.
No more pesky hints that interfere with our beloved CBO.
Future Veterans 