Skip to Main Content
  • Questions
  • Any scenairo where Optimizer will not use an active SQL profile

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Prithviraj.

Asked: January 31, 2018 - 7:27 am UTC

Last updated: January 31, 2018 - 2:52 pm UTC

Version: 11gR2

Viewed 1000+ times

You Asked

Hi team,

If a SQL query has an active(ENABLED) profile, will there be any scenario when Optimizer will not use it?

E.g. As I know the profile is auxiliary statistics on all objects being referenced by underlying SQL query. So if we gather statistics on all objects (Table, column, index level) with Estimate_Percent = 100. Now, will Optimizer still use the profile?

Let's not consider profiles having different CATEGORY, those will be used as per value set for SQLTUNE_CATEGORY parameter.

and Chris said...

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.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Excellent Information

Prithviraj Patil, January 31, 2018 - 11:40 am UTC

Thanks for your swift reply. This helps a lot

always look at the plan_hash_value

Houri Mohamed, January 31, 2018 - 12:48 pm UTC

When I create a SQL profile I always manage to include the plan_hash_value of the execution plan I want the SQL profile to generate. As such I can easily know whether the plan_hash_value of my subsequent query executions have "really" used the SQL profile or not. For example in your case when you dropped the index the plan_hash_value becomes 2498539100 while initially at the SQL profile creation it was 3995057492

Best regards
Mohamed Houri
Chris Saxon
January 31, 2018 - 2:52 pm UTC

You mean in the name of the profile? That's a neat idea!

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.