Database Query Performance Issue After Database Upgraded to 12.2.0.1 from 11.2.0.4

Issue: 
Database Query Performance Issue After database Upgradated to 12.2.0.1 from 11.2.0.4

Solution: After troubleshooting we came up with the below 5 sections or scenarios to address the above query performance issue.


Section 1:
12c oracle home is at base level and not applied any patches.

Cause: No Patch applied at 12c oracle home, as per the oracle recommendation any oracle home should not be at base level since base release contain lots of bugs.

Steps:
1. Below are the patches recommended by oracle 

Database Oct 2019 Update 12.2.0.1.191015 Patch 30116802 for UNIX
OJVM Update 12.2.0.1.191015 Patch 30133625 for UNIX

2. Download the patches from MOS.

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=33968877088394&id=2568292.1&displayIndex=1&_afrWindowMode=0&_adf.ctrl-state=95g599v2j_496

3. Apply as per the readme 

30138470 - Apply this DB patch it will sub-patch of patch 30116802
30122814 - Apply this OCW patch it will sub-patch of patch 30116802
30133625 - Apply this OJVM patch

Section 2: 
Dictionary stats and fixed objects stats.

Cause: It seems Dictionary stats and fixed objects stats are not performed after database upgradation, Oracle strongly recommends to do dictionary stats and fixed objects stats after any database upgradation or migration.

Steps:
1. Please prepare the below sql script as Fixed_Dictionary_stats.sql

spool Fixed_Dictionary_stats.log
set time on
set timing on

exec DBMS_STATS.GATHER_DICTIONARY_STATS;
exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
execute dbms_stats.gather_schema_stats('SYS', method_opt=>'for all columns size 1', degree=>30,estimate_percent=>100,cascade=>true);
exec dbms_stats.gather_system_stats ('NOWORKLOAD');

2. Stop if any application running or connected 

3. The above script in nohup 

nohup sh sqlplus / as sysdba @Fixed_Dictionary_stats.sql &

Section 3:
Table move and Table shrink.

Cause: After any DML or after any database upgradation data inside the table is not aligned sequentially, it is recommended to do Table move and Table shrink on certain major Tables for better query performance.

Steps:
1. Please prepare the below sql script as Table_Move.sql

spool Table_move.log
set time on
set timing on

alter table XXLG.XLG_FAH_BULK_UPLOAD_TRANS move parallel nologging;
alter table XXLG.XLG_FAH_BULK_UPLOAD_TRANS noparallel logging;

alter table PROD_DW.WC_GL_BALANCE_F_S1 move parallel nologging;
alter table PROD_DW.WC_GL_BALANCE_F_S1 noparallel logging;

alter table PROD_DW.WC_ACCT_BUDGET_F_S1 move parallel nologging;
alter table PROD_DW.WC_ACCT_BUDGET_F_S1 noparallel logging;

alter table PROD_DW.WC_GL_GROUP_AACCOUNT_DH move parallel nologging;
alter table PROD_DW.WC_GL_GROUP_AACCOUNT_DH noparallel logging;

alter table PROD_DW.WC_HIERARCHY_DH move parallel nologging;
alter table PROD_DW.WC_HIERARCHY_DH noparallel logging;

alter table PROD_DW.WC_MFR_DATASECURITY_G move parallel nologging;
alter table PROD_DW.WC_MFR_DATASECURITY_G noparallel logging;

alter table PROD_DW.W_GL_ACCOUNT_D move parallel nologging;
alter table PROD_DW.W_GL_ACCOUNT_D noparallel logging;

alter table PROD_DW.W_MCAL_DAY_D move parallel nologging;
alter table PROD_DW.W_MCAL_DAY_D noparallel logging;

alter table PROD_DW.W_BUDGET_D move parallel nologging;
alter table PROD_DW.W_BUDGET_D noparallel logging;

2. Stop if any application running or connected 

3. The above script in nohup 

nohup sh sqlplus / as sysdba @Table_move.sql &

4. Please prepare the below sql script as Table_Shrink.sql

spool Table_shrink.log
set time on
set timing on

alter table XXLG.XLG_FAH_BULK_UPLOAD_TRANS ENABLE ROW MOVEMENT;
alter table XXLG.XLG_FAH_BULK_UPLOAD_TRANS SHRINK SPACE cascade;
alter table XXLG.XLG_FAH_BULK_UPLOAD_TRANS DISABLE ROW MOVEMENT;

alter table PROD_DW.WC_GL_BALANCE_F_S1 ENABLE ROW MOVEMENT;
alter table PROD_DW.WC_GL_BALANCE_F_S1 SHRINK SPACE cascade;
alter table PROD_DW.WC_GL_BALANCE_F_S1 DISABLE ROW MOVEMENT;

alter table PROD_DW.WC_ACCT_BUDGET_F_S1 ENABLE ROW MOVEMENT;
alter table PROD_DW.WC_ACCT_BUDGET_F_S1 SHRINK SPACE cascade;
alter table PROD_DW.WC_ACCT_BUDGET_F_S1 DISABLE ROW MOVEMENT;

alter table PROD_DW.WC_GL_GROUP_AACCOUNT_DH ENABLE ROW MOVEMENT;
alter table PROD_DW.WC_GL_GROUP_AACCOUNT_DH SHRINK SPACE cascade;
alter table PROD_DW.WC_GL_GROUP_AACCOUNT_DH DISABLE ROW MOVEMENT;

alter table PROD_DW.WC_HIERARCHY_DH ENABLE ROW MOVEMENT;
alter table PROD_DW.WC_HIERARCHY_DH SHRINK SPACE cascade;
alter table PROD_DW.WC_HIERARCHY_DH DISABLE ROW MOVEMENT;

alter table PROD_DW.WC_MFR_DATASECURITY_G ENABLE ROW MOVEMENT;
alter table PROD_DW.WC_MFR_DATASECURITY_G SHRINK SPACE cascade;
alter table PROD_DW.WC_MFR_DATASECURITY_G DISABLE ROW MOVEMENT;

alter table PROD_DW.W_GL_ACCOUNT_D ENABLE ROW MOVEMENT;
alter table PROD_DW.W_GL_ACCOUNT_D SHRINK SPACE cascade;
alter table PROD_DW.W_GL_ACCOUNT_D DISABLE ROW MOVEMENT;

alter table PROD_DW.W_MCAL_DAY_D ENABLE ROW MOVEMENT;
alter table PROD_DW.W_MCAL_DAY_D SHRINK SPACE cascade;
alter table PROD_DW.W_MCAL_DAY_D DISABLE ROW MOVEMENT;

alter table PROD_DW.W_BUDGET_D ENABLE ROW MOVEMENT;
alter table PROD_DW.W_BUDGET_D SHRINK SPACE cascade;
alter table PROD_DW.W_BUDGET_D DISABLE ROW MOVEMENT;

5. Stop if any application running or connected 

6. The above script in nohup 

nohup sh sqlplus / as sysdba @Table_shrink.sql &

Section 4: 
Rebuild index.

Cause: Recommendation to rebuild the index after any database upgradation or migration as per the SQL tunneling adviser.

Steps:
1. Please prepare the below sql script as Index_rebuild.sql

Assuming WC_GL_GROUP_AACCOUNT_DH_N1 as one index, we need to list all index and keep in the below script

spool Index_rebuild.log
set time on
set timing on

alter index PROD_DW.WC_GL_GROUP_AACCOUNT_DH_N1 coalesce;
alter index PROD_DW.WC_GL_GROUP_AACCOUNT_DH_N1 rebuild parallel nologging;
alter index PROD_DW.WC_GL_GROUP_AACCOUNT_DH_N1 noparallel logging;

2. Stop if any application running or connected 

3. The above script in nohup 

nohup sh sqlplus / as sysdba @Index_rebuil.sql &

Section 5:
Collect stats on the table with 100%.

Cause: One time we need to collect the stats with 100% on all major tables.

Steps:
1. Please prepare the below sql script as Table_stats.sql

spool Table_stats.log
set time on
set timing on

execute dbms_stats.gather_table_stats(ownname =>'PROD_DW',tabname =>'WC_GL_BALANCE_F_S1',estimate_percent =>100, method_opt =>'FOR ALL COLUMNS SIZE AUTO',CASCADE=>TRUE);
execute dbms_stats.gather_table_stats(ownname =>'PROD_DW',tabname =>'WC_ACCT_BUDGET_F_S1',estimate_percent =>100, method_opt =>'FOR ALL COLUMNS SIZE AUTO',CASCADE=>TRUE);
execute dbms_stats.gather_table_stats(ownname =>'PROD_DW',tabname =>'WC_GL_GROUP_AACCOUNT_DH',estimate_percent =>100, method_opt =>'FOR ALL COLUMNS SIZE AUTO',CASCADE=>TRUE);
execute dbms_stats.gather_table_stats(ownname =>'PROD_DW',tabname =>'WC_HIERARCHY_DH',estimate_percent =>100, method_opt =>'FOR ALL COLUMNS SIZE AUTO',CASCADE=>TRUE);
execute dbms_stats.gather_table_stats(ownname =>'PROD_DW',tabname =>'WC_MFR_DATASECURITY_G',estimate_percent =>100, method_opt =>'FOR ALL COLUMNS SIZE AUTO',CASCADE=>TRUE);
execute dbms_stats.gather_table_stats(ownname =>'PROD_DW',tabname =>'W_GL_ACCOUNT_D',estimate_percent =>100, method_opt =>'FOR ALL COLUMNS SIZE AUTO',CASCADE=>TRUE);
execute dbms_stats.gather_table_stats(ownname =>'PROD_DW',tabname =>'W_MCAL_DAY_D',estimate_percent =>100, method_opt =>'FOR ALL COLUMNS SIZE AUTO',CASCADE=>TRUE);
execute dbms_stats.gather_table_stats(ownname =>'PROD_DW',tabname =>'W_BUDGET_D',estimate_percent =>100, method_opt =>'FOR ALL COLUMNS SIZE AUTO',CASCADE=>TRUE);

2. Stop if any application running or connected 

3. The above script in nohup 

nohup sh sqlplus / as sysdba @Table_stats.sql &

Regards,

Mallik