Oracle 调整-执行计划已更改

我有一个查询，过去运行时间为2秒，但现在运行时间超过2分钟。

在其他环境中，它仍在2秒内运行。在执行计划中，我只看到过滤器和E行/字节的顺序有所不同。

您能否建议可能是什么问题，如果有任何提示，我可以尝试进行优化。

不到2秒的执行计划
*

SQL_ID  1jz584jsu4y4v, child number 0
-------------------------------------
select   /*+       BEGIN_OUTLINE_DATA       IGNORE_OPTIM_EMBEDDED_HINTS 
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')       
DB_VERSION('12.1.0.2')       OPT_PARAM('_optimizer_use_feedback' 
'false')       OPT_PARAM('_px_adaptive_dist_method' 'off')       
OPT_PARAM('_optimizer_dsdir_usage_control' 0)       
OPT_PARAM('_optimizer_adaptive_plans' 'false')       
OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')       
OPT_PARAM('_optimizer_gather_feedback' 'false')       
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')       
OPT_PARAM('optimizer_index_cost_adj' 20)       ALL_ROWS       
OUTLINE_LEAF(@"SEL$1")       FULL(@"SEL$1" "CRTCL"@"SEL$1")      
 END_OUTLINE_DATA   */ count(case when ts <  trunc(sysdate) then 
1 end) weekly_error_ct             from log.CRTCL          
   where event_cntxt = 'PROCTOR' and             MSG like 
'%ResultId%' and             MSG not like '%ORA-20200%' and    
         MSG not like '%sp_ValidateRecord%' an
 

 
Plan hash value: 1601281016
 
--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name         | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |      1 |        |       |   446 (100)|          |      1 |00:00:00.04 |    1890 |     65 |
|   1 |  SORT AGGREGATE    |              |      1 |      1 |   137 |            |          |      1 |00:00:00.04 |    1890 |     65 |
|*  2 |   TABLE ACCESS FULL|        CRTCL |      1 |      1 |   137 |   446   (1)| 00:00:01 |      0 |00:00:00.04 |    1890 |     65 |
--------------------------------------------------------------------------------------------------------------------------------------
 

 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1
   2 - SEL$1 / CRTCL@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      OPT_PARAM('_px_adaptive_dist_method' 'off')
      OPT_PARAM('_optimizer_dsdir_usage_control' 0)
      OPT_PARAM('_optimizer_adaptive_plans' 'false')
      OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
      OPT_PARAM('_optimizer_gather_feedback' 'false')
      OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
      OPT_PARAM('optimizer_index_cost_adj' 20)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "CRTCL"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(("ts">=TRUNC(SYSDATE@!-7) AND "EVENT_CNTXT"='PROC' 
              AND "MSG" LIKE '%ResultId%' AND "MSG" NOT LIKE '%ORA-20200%' 
              AND "MSG" NOT LIKE '%sp_ValidateRecord%'))
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - (#keys=0) COUNT(CASE  WHEN "TS"

需要2分钟以上的执行计划
*
SQL_ID  1jz584jsu4y4v, child number 0
-------------------------------------
select   /*+       BEGIN_OUTLINE_DATA       IGNORE_OPTIM_EMBEDDED_HINTS 
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')       
DB_VERSION('12.1.0.2')       OPT_PARAM('_optimizer_use_feedback' 
'false')       OPT_PARAM('_px_adaptive_dist_method' 'off')       
OPT_PARAM('_optimizer_dsdir_usage_control' 0)       
OPT_PARAM('_optimizer_adaptive_plans' 'false')       
OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')       
OPT_PARAM('_optimizer_gather_feedback' 'false')       
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')       
OPT_PARAM('optimizer_index_cost_adj' 20)       ALL_ROWS       
OUTLINE_LEAF(@"SEL$1")       FULL(@"SEL$1" "CRTCL"@"SEL$1")      
 END_OUTLINE_DATA   */ count(case when ts <  trunc(sysdate) then 
1 end) weekly_error_ct             from log.CRTCL          
   where event_cntxt = 'PROCTOR' and             MSG like 
'%ResultId%' and             MSG not like '%ORA-20200%' and    
         MSG not like '%sp_ValidateRecord%' an
 
 
Plan hash value: 1601281016
 
--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name         | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |      1 |        |       |  1341 (100)|          |      1 |00:02:14.40 |     448K|    443K|
|   1 |  SORT AGGREGATE    |              |      1 |      1 |   160 |            |          |      1 |00:02:14.40 |     448K|    443K|
|*  2 |   TABLE ACCESS FULL|        CRTCL |      1 |   4692 |   733K|  1341   (1)| 00:00:01 |      0 |00:02:14.40 |     448K|    443K|

 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1
   2 - SEL$1 / CRTCL@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      OPT_PARAM('_px_adaptive_dist_method' 'off')
      OPT_PARAM('_optimizer_dsdir_usage_control' 0)
      OPT_PARAM('_optimizer_adaptive_plans' 'false')
      OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
      OPT_PARAM('_optimizer_gather_feedback' 'false')
      OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
      OPT_PARAM('optimizer_index_cost_adj' 20)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "CRTCL"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(("MSG" LIKE '%ResultId%' AND "MSG" NOT LIKE 
              '%ORA-20200%' AND "MSG" NOT LIKE '%sp_ValidateRecord%' AND 
              "TS">=TRUNC(SYSDATE@!-7) AND "CNTXT"='PROC'))
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - (#keys=0) COUNT(CASE  WHEN "TS" 

这实际上是相同的计划，它只是在较慢的查询中做了更多的工作。

在这两种情况下，查询都从表中获取零行。但是在较慢的计划中，它会做更多的工作 (将近50万个缓冲区，而不是几千个; 请参阅缓冲区列)。

在较慢的数据库中的表中是否有 (很多) 更多的数据？

我看到查询中有一个完整的扫描提示:

FULL(@"SEL$1" "CRTCL"@"SEL$1")      

这将停止优化器使用任何候选索引。尝试删除此内容，并考虑在

( event_cntxt, msg, ts )

如果它还不存在。

最好的情况下，数据库将能够范围扫描此索引，以快速发现查询不返回任何行。

或者，您可以退回到全索引扫描，这通常比全表扫描更快 (由于索引是较小的数据结构)。