Oracle 12c/18 : How to find out if a PSU has been applied? DBMS_QOPATCH

PBMLOL>set serveroutput on
PBMLOL>exec dbms_qopatch.get_sqlpatch_status;

Patch Id : 28090523
        Action : APPLY
        Action Time : 12-MAY-2019 00:22:22
        Description : Database Release Update : 18.3.0.0.180717 (28090523)
        Logfile :
/u01/app/oracle/cfgtoollogs/sqlpatch/28090523/22329768/28090523_apply_MYDB_2019May12_00_21_46.log
        Status : SUCCESS

Patch Id : 27923415
        Action : APPLY
        Action Time : 12-MAY-2019 00:29:39
        Description : OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415)
        Logfile :
/u01/app/oracle/cfgtoollogs/sqlpatch/27923415/22239273/27923415_apply_MYDB_2019May12_00_29_39.log
        Status : SUCCESS

PL/SQL procedure successfully completed.

How to enable tracing for ORA-00942: table or view does not exist

Enable the alert : 

SQL> alter system set events '00942 trace name errorstack level 1';

Disable the alert:

SQL> alter system set events '00942 trace name context off';

The rollback is very slow, how do I speed it up?

 This is a very common problem, we kill an update/merge and is taking forever to rollback.

 There are 2 solutions for this:

1) Kill the spid oracle shadow process and set the parameter below:

SQL> alter system set fast_start_parallel_rollback=HIGH;

2) Bounce the DB and set the same parameter as above.

SQL> alter system set fast_start_parallel_rollback=HIGH;

 The only issue now, to monitor the rollback, we'll need a different query:

SQL>   SELECT usn, state, undoblockstotal "Total",undoblocksdone "Done",undoblockstotal-undoblocksdone "ToDo", DECODE(cputime,0,'unknown',SYSDATE+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) "Finish at" FROM v$fast_start_transactions;

Linux: ps command for processes older processes

 To get the exact start time of an old process, use below:

[steaua@TESTDB]/u01/app/oracle >ps -eo pid,lstart,cmd|grep 20734

20734 Wed Nov 17 01:59:02 2021 ora_pmon_TESTDB

Otherwise we'll get the year only:

[steaua@TESTDB]/u01/app/oracle >ps -ef|grep 20734 |grep -v grep

oracle   20734     1  0  2021 ?        00:41:46 ora_pmon_TESTDB

How to force the optimizer to join the 2 tables in a subquery, opening more options for joins? UNNEST hint to the rescue

 The issue:

 Query:

select

* from t

where not exists (select 

1 from c

                     where ban = t.ban

                       and subscriber_no = t.subscriber_no

                       and actv_bill_seq_no = t.bill_seq_no;

The execution plan will always be a FILTER.

By adding the UNNEST hint, we can actually force the optimizer to join the 2 tables, so we can hint for hash or nl joins.

select

* from t

where not exists (select /*+ unnest */

1 from c

                     where ban = t.ban

                       and subscriber_no = t.subscriber_no

                       and actv_bill_seq_no = t.bill_seq_no

Link to a very good article about how to use Log Miner

 Log Miner setup steps:

https://www.thegeekdiary.com/simple-steps-to-use-logminer-for-finding-high-redo-log-generation/

Just in case the link will move, the main steps are below:

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/users/Florin/MYDB_1_714407_821540104.arc',OPTIONS => DBMS_LOGMNR.NEW);

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/users/Florin/MYDB_1_714408_821540104.arc',OPTIONS => DBMS_LOGMNR.ADDFILE);

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( -

     OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);>

PL/SQL procedure successfully completed.

SQL> l

  1  select USERNAME,OS_USERNAME,MACHINE_NAME,SESSION_INFO from V$LOGMNR_CONTENTS

  2  where TABLE_NAME='MY_TABLE'

  3  and OPERATION='DELETE'

  4* and rownum < 11

How to transfer a "good" execution plan from one DB to another?

 The article below is very good and useful, I was following the instructions and I was able to force the Production DB execution plan in ST DB, even that the problematic query never used the good plan in UAT.

https://docs.rackspace.com/blog/transfer-sql-plans-by-using-sql-plan-baseline/

Florin