Performance issues on different nodes in RAC cluster - SQL execution plan

So query runs on node 1 - generates a fast query plan which is then cached - large load takes place meaning stats are out of date. 

Query runs on node 2 generates a terrible plan. - meaning node1 still has a fast query plan cached - so node 1 ok node 2 terrible.

Possible option run tuning advisor in OEM on the inconsistent sql id and pick the good execution plan from the correct node.

Found this website - 

Copy SQL Execution Plans Between Databases in Oracle 19c

So using the website above - didn't need to move between databases but used to fix the execution plan.

e.g. (random sql for demo purpose)

select * from C29R2.IMPORT_EXPORT_ERRORS where EXTERNAL_REF like '%Cowie%';

70904 LJS_S85362-1_Cowie 1 PRN

SQL> explain plan for

select * from C29R2.IMPORT_EXPORT_ERRORS where EXTERNAL_REF like '%Cowie%'

Explained.

SQL> SELECT *FROM table(DBMS_XPLAN.DISPLAY (FORMAT=>'ALL +OUTLINE'));

Plan hash value: 3531643812
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|

….etc

Find the sql_id for the inconsistent sql - 

SQL> select sql_text, sql_id, LAST_ACTIVE_TIME from v$sqlarea where sql_text like '%EXTERNAL_REF%' order by 3
2 /

SQL_TEXT SQL_ID LAST_ACTI
------------------------------------------------------------------------------- ------------- ---------
select * from C29R2.IMPORT_EXPORT_ERRORS where EXTERNAL_REF like '%Cowie%' 

gtkddvj5y9skn 20-FEB-25


SQL> SELECT DISTINCT plan_hash_value FROM v$sql WHERE sql_id = 'gtkddvj5y9skn'
SQL> /

PLAN_HASH_VALUE
---------------
3531643812


SQL> DECLARE
set binary_integer;
BEGIN
ret := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => 'gtkddvj5y9skn',
plan_hash_value => 3531643812,
fixed => 'YES',
enabled => 'YES'
);
END; 

PL/SQL procedure successfully completed.

SQL> SELECT sql_handle, plan_name FROM dba_sql_plan_baselines;

SQL_HANDLE PLAN_NAME
------------------------------ ---------------------------------
SQL_0653a496ce0872e7 SQL_PLAN_0cnx4kv70hwr7c58fda78