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

 

Upgrading a single PDB to use a new DST file

(Credit to Mike C. for these notes)

When trying to refresh a db which was at a db level 2 below Prod I received the error below.

ORA-39002: invalid operation

ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 41 into a target database with TSTZ version 39.

This is the first time I’ve tried to refresh Dev from Prod. Test is more in step with Prod so wasn’t a problem.

Prod is at 19.19 but that’s not available from the console anymore.

I created a new Dev at 19.23 and that did allow me to do an import however I didn’t really want to replace the original db system, although would have done if no other choice.

I upgraded Dev to 19.23 but suspected there’d be more work to do before an import could be done and so it proved.

Although general db patching does make the new DST files available it doesn’t set them in the CDB or PDB so the actions below were still required.

If I hadn’t patched to 19.23 I could have applied a DST patch (p35099667_190000_Linux-x86-64.zip ) using opatch to apply the files but this still would have needed the below. (think of the below as datapatch after patching)

The below can be done on individual PDB’s or the container. I’ve just done it on LIMSD pdb as that’s all we’ll use this for. Also I did it on a new temp empty PDB which would be cloned over LIMSD so as not to worry about user table upgrading. I was doing an import anyway.

Check which DST files are now available after upgrade or DST patch.

ls -l /u01/app/oracle/product/19.0.0.0/dbhome_1/oracore/zoneinfo

Check which file is set in the db.

SQL> SELECT version FROM v$timezone_file;

VERSION
----------
39

Put pdb into upgrade mode

alter pluggable database PDB1 close immediate;

alter pluggable database PDB1 open upgrade;

alter session set container=PDB1;


Start an upgrade window

begin

dbms_dst.begin_upgrade

( new_version => 41,
error_on_overlap_time => false,
error_on_nonexisting_time => false
);

end;


Restart the db normally

alter pluggable database PDB2 close immediate;

alter pluggable database PDB2 open;

Start the upgrade


SQL> declare

failures binary_integer := -1;

begin

dbms_dst.upgrade_database

( num_of_failures => failures,

upgrade_data => false,

error_on_overlap_time => false,

error_on_nonexisting_time => false

);

end; 

End the upgrade

SQL> declare

failures binary_integer := -1;

begin

dbms_dst.end_upgrade(num_of_failures => failures);

end; 

PL/SQL procedure successfully completed.


Check again which file is set in the db.

SQL> SELECT version FROM v$timezone_file;

VERSION
----------
41

DP import should now work in this PDB.