Oracle12.2:在添加FOR UPDATE时，优化器会更改SELECT查询的解释计划...结尾处的块

Question

我有一个(相当复杂的) 200+行SELECT查询，它是由一个执行良好的计费应用程序发起的。当应用程序使用..的FOR UPDATE执行相同的查询时。块，那么优化器就会极大地改变执行计划，性能就会变得很差。

实际上，问题定位于优化器放弃“视图推送谓词”方法导致性能低下的特定区域。我尝试使用PUSH_PRED提示执行“推入谓词”方法，但没有任何幸运，这很可能是由于查询的复杂性(在所有SELECT、FROM和WHERE块中都有子查询)，除了从头开始重写查询以达到可接受的性能，然后对优化器应用SQL-profile之外，是否有任何关于如何克服这种情况的快速提示。你有没有遇到过类似的问题？

感谢并问候Dimitris G

Answer 1

您需要为我们发布更多详细信息，以帮助您找出在您的情况下计划更改的原因以及如何避免更改，但添加for update肯定可以更改计划。

下面是一个简单的例子

该查询连接t2的t1和子查询。t2的所有引用列都在一个索引中。因此，数据库能够从索引中获得t2所需的所有值，优化器只读取索引，而根本不访问表t2：

create table t1 as 
  select level c1, 'xxxxx' c2 
  from   dual
  connect by level <= 100;
  
create table t2 as 
  select c1, c2, c3, 'yyyy' c4 
  from   t1
  cross join ( 
    select level c3
    from   dual
    connect by level <= 10
  );

create index i2 on t2 ( c1, c4 );

set serveroutput off

select * from t1
join ( 
  select c1, c4
  from   t2
) s 
on    t1.c1 = s.c1
where t1.c1 = 1
and   s.c4 = 'yyyy';

select * 
from   table(dbms_xplan.display_cursor( format => 'BASIC LAST'));

-------------------------------------                                       
| Id  | Operation            | Name |                                       
-------------------------------------                                       
|   0 | SELECT STATEMENT     |      |                                       
|   1 |  MERGE JOIN CARTESIAN|      |                                       
|   2 |   TABLE ACCESS FULL  | T1   |                                       
|   3 |   BUFFER SORT        |      |                                       
|   4 |    INDEX RANGE SCAN  | I2   |                                       
-------------------------------------  

但是添加一个包含来自t2和数据库的值的for update子句，现在有来访问这个表，以便锁定行。因此，以前的计划不再有效，它将切换到全扫描t2：

select * from t1
join ( 
  select c1, c4
  from   t2
) s 
on    t1.c1 = s.c1
where t1.c1 = 1
and   s.c4 = 'yyyy'
for   update;

select * 
from   table(dbms_xplan.display_cursor( format => 'BASIC LAST'));

-------------------------------------                                       
| Id  | Operation            | Name |                                       
-------------------------------------                                       
|   0 | SELECT STATEMENT     |      |                                       
|   1 |  FOR UPDATE          |      |                                       
|   2 |   BUFFER SORT        |      |                                       
|   3 |    HASH JOIN         |      |                                       
|   4 |     TABLE ACCESS FULL| T1   |                                       
|   5 |     TABLE ACCESS FULL| T2   |                                       
------------------------------------- 

因此，添加for update可能会禁用一些访问路径，从而导致您得到不同的计划。