Oracle 12.2: Optimizer changes the explain plan of a SELECT query when adding an FOR UPDATE OF ... block at the end

user_10708183 0
Viewed 95

I have a (rather complicated) 200+ lines SELECT query initiated by a billing application that performs well. When the application executes THE SAME query with an FOR UPDATE OF .. block added at the end, then the optimizer changes the execution plan dramatically and the performance becomes poor.

Actually the problem is located to a specific area where the optimizer abandons the "VIEW PUSHED PREDICATE" approach leading to poor performance. I tried to enforce the "PUSHED PREDICATE" approach using the PUSH_PRED hint without any luck, most-likely due to the complexity of the query (having subqueries in all SELECT , FROM and WHERE blocks) Apart from re-writing the query from scratch to reach an acceptable performance and then applying an SQL-profile for the optimizer, and then is there any quick hint about how I can overcome the situation. Have you experienced a similar issue ?

Thanks and Regards Dimitris G

query-optimization oracle
1 Answers

You'll need to post more details for us to help you figure out why the plan has changed in your case and how to avoid it, but adding for update certainly can change the plan.

Here's a simple example

The query joins t1 and subquery of t2. All the referenced columns of t2 are in an index. Thus the database is able to get all the values it needs for t2 from the index and the optimizer only read the index, without accessing the table t2 at all:

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

But add in a for update clause that includes values from t2 and the database now has to access this table in order to lock the rows. So the previous plan is no longer valid and it switches to full scanning t2 instead:

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

So adding for update has likely disabled some access paths, leading to the different plan you get.

user_14859550
Related