I'm not aware of any cases. Is your question really:
Why is the optimizer using a different plan than I expected after applying a SQL profile?
If so, remember a SQL profile is an extended set of hints. Unlike baselines, it doesn't lock the plan. As the docs say:
SQL profiles are also implemented using hints, but these hints do not specify any specific plan. Rather, the hints correct miscalculations in the optimizer estimates that lead to suboptimal plans. For example, a hint may correct the cardinality estimate of a table.So a query with a SQL profile can still change plans, particularly if:
1. The database environment changes
2. The the original plan is now invalid
Things which cause 1 include upgrades, patches, changing init parameters and
gathering stats!
The obvious cause of 2 is dropping an index. In the example below, the profile includes an index hint. But after dropping the index the query has to use a full table scan. Yet it still "uses" the SQL profile:
create table t as
select level x, lpad('x', 100, 'x') y
from dual
connect by level <= 100;
create index i on t (x);
set serveroutput off
select * from t where x = 1;
select *
from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST +OUTLINE'));
PLAN_TABLE_OUTPUT
EXPLAINED SQL STATEMENT:
------------------------
select * from t where x = 1
Plan hash value: 3995057492
----------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T |
| 2 | INDEX RANGE SCAN | I |
----------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
OPT_PARAM('_optimizer_cost_model' 'fixed')
OPT_PARAM('_fix_control' '6670551:0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."X"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T"@"SEL$1")
END_OUTLINE_DATA
*/
begin
dbms_sqltune.import_sql_profile (
sql_text => 'select * from t where x = 1',
name => 'PROF',
profile => sqlprof_attr(q'| /*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
OPT_PARAM('_optimizer_cost_model' 'fixed')
OPT_PARAM('_fix_control' '6670551:0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."X"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T"@"SEL$1")
END_OUTLINE_DATA
*/ |')
);
end;
/
select * from t where x = 1;
select *
from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST +NOTE'));
PLAN_TABLE_OUTPUT
EXPLAINED SQL STATEMENT:
------------------------
select * from t where x = 1
Plan hash value: 3995057492
----------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T |
| 2 | INDEX RANGE SCAN | I |
----------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- SQL profile PROF used for this statement
drop index i;
select * from t where x = 1;
select *
from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST +NOTE'));
PLAN_TABLE_OUTPUT
EXPLAINED SQL STATEMENT:
------------------------
select * from t where x = 1
Plan hash value: 2498539100
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| T |
----------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- SQL profile PROF used for this statement
If you think a SQL statement is NOT using a profile when it should, check the Note section of the plan. This will tell you which profile it used, if any.