Tuning query plan regression using AWR

After changing the available CPU cores on one of the Servers, we have started noticing “enq: TX – row lock contention” wait events taking nearly 40% of the DB Time and

large number of blocking sessions. Further investigation shows this excessive lock contention is due to one of the ‘select’ queries taking unusally longer than its previous runtime

and thus finally resulting in excessive row-level locking. The plan hash value(PHV) of the query has changed and the new plan is expensive and slow.

Going through the AWR for the plan prior to the change confirmed that it was using a different plan hash value earlier and the here’s the approach used to fix this offending query issue.

1. Create a SQL Tuning Set (STS) either manually or via OEM

SQL_ID of the sql in this instance is “8yrszxkhbsv65”

BEGIN 
DBMS_SQLTUNE.CREATE_SQLSET ( sqlset_name  => 'STS_CEAQUERY_20210420', 
                                                       description  => 'SQL Tuning Set for CEA Query'); 
END;
/

2. Load the SQL Plan (good plan) into the STS based on the AWR Snap id’s

DECLARE 
 cur sys_refcursor; 
 BEGIN 
  OPEN cur FOR    SELECT VALUE(P)    FROM TABLE(   dbms_sqltune.select_workload_repository(begin_snap=>124157,end_snap=>124158,basic_filter=>'sql_id = ''8yrszxkhbsv65''',attribute_list=>'ALL')  ) p; 
 DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'STS_CEAQUERY_20210420', populate_cursor=>cur);   
CLOSE cur; 
 END;
 /

3 . Once the better plan is loaded into STS, load the same into the S QL Plan baseline as below.

DECLARE 
 my_plans pls_integer; 
 BEGIN 
  my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(    sqlset_name => 'STS_CEAQUERY_20210420',     basic_filter=>'plan_hash_value = ''1610270671'''    ); 
 END;
 /

4. Change the auto-purge attribute so the plan doesn’t get purged as part of the SPM evolve process.

SET SERVEROUTPUT ON
DECLARE
  l_plans_altered  PLS_INTEGER;
BEGIN
  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(    sql_handle      => 'SQL_04213d38b2251cf8',    plan_name       => 'SQL_PLAN_0889x72t2a77s148d36e4',    attribute_name  => 'autopurge',    
attribute_value => 'NO');
  DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/

This has brought the better runtime back and the TX row lock contention has disappered!