Oracle 优化器不会使用活动SQL配置文件的任何场景

嗨，团队，

如果SQL查询具有活动 (启用) 配置文件，那么优化器将不使用它的情况是否会出现？

如我所知，该配置文件是基础SQL查询引用的所有对象的辅助统计信息。因此，如果我们使用estimate_percle = 100收集所有对象 (表，列，索引级别) 的统计信息。现在，优化器还会使用配置文件吗？

我们不要考虑具有不同类别的配置文件，这些配置文件将被用作sqldune_category参数的值集。

我不知道有什么案子。你的问题真的是:

为什么优化器在应用SQL配置文件后使用的计划与我预期的不同？

如果是这样，请记住SQL配置文件是一组扩展的提示。与基线不同，它不会锁定计划。正如医生所说:

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.

因此，使用SQL配置文件的查询仍然可以更改计划，特别是在以下情况下:

1.数据库环境的变化
2.原来的计划现在无效

导致1的事情包括升级、补丁、更改init参数和gathering stats!

2的明显原因是下降了一个指数。在下面的示例中，配置文件包括索引提示。但是删除索引后，查询必须使用全表扫描。然而它仍然 “使用” SQL配置文件:

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
复制

如果您认为SQL语句在应该的时候没有使用配置文件，请检查计划的注释部分。这将告诉你它使用了哪个配置文件，如果有的话。