Oracle Database 19c performance Issue Post Migration -- What to Consider

 


Common Misunderstanding : 
1)  Compatible Parameter  Control  Optimizer Behavior
2) 



A) Thing to Compare in source  and Target :  

1)   Compare Database Parameter with source and Target 
Specially Non default parameters and Optimizer Parameters 
Under Score Parameters / Events


2)  Compare  Database  Hidden parameters  In source and  Target 

3) Compare Database Optimizer parameters in source and target 

https://abdul-hafeez-kalsekar.blogspot.com/2022/06/oracle-database-parameters-influencing.html


4)  Compare Database Sql profiles  and Baselines  in source and Target 

5) Compare Database Sql patches in source and  Target 

6)   Compare database maintenance window in source and  Target  and verify  automatic  statistics collection

7)    Compare dba_profiles   and password verification function for  comparing  password expiry and other restrictions 


8) Take Awr dump  from source and import in Target 

https://abdul-hafeez-kalsekar.blogspot.com/2022/04/oracle-awr-data-dump-important-while.html


9)  Verify  V$option in source and  target to compare  options  enabled 

10)  Verify  Dba_registry in source and  target for  Compare  Components   

11)   Verify  Hugepages and  OS  Limits  on source and Target 

12)   Compare oneoff patches  applied on source database and  Grid home and if that is  covered on  new target   binary homes 

13) Check  sqlnet.ora settings in  source  server 

14)  Verify Acl permission if any  in source oracle home , granted to application team .

15)  check  nls date format i , character set and national characterset in source 

16)  Verify Dba_directories and verify all directories are pysically present on  target . 

17)  Check cron jobs   in source database server 

18) check awr retention in source database 

19)  Check  stats retention . in both source and  target .

select dbms_stats.get_stats_history_retention  from dual ; 



19) Verify  Stats   Preference   in source and Target 

select dbms_stats.get_prefs('method_opt') a from dual;

20)   Verify   public synonyms  in source    created on  non system tables 

21)   Compare V$SYSTEM_PARAMETER2   in source and  Target .

22)  Check Parallel degrees in  dba_tables and  Dba_indexes  to ensure Degree  has not changed  during  export and  import 

23) Compare resource_manager_plan  . 


24)_lm_drm_disable


Other Best Practices : 

1) Decide Asm Parameters Depending  Upon  Asm Client 

2) Gather sys and  system   schema  statistics immediately after import 


3) If Its upgrade - gather Fixed Object Statistics 

Select  con_id ,  operation ,  target , end_time   from cdb_opstat_operations where 
(
(operation='gather_fixed_object_stats')  
or 
(operation='gthaer_dictionary_stats'  and  ( target is null  or target is ('SYS','SYSTEM')) ) 
or 
( operation ='gather_schema_stats' and target in ('SYS','SYSTEM')) 
) 
and   end_time > sysdate-1  
order  by  con_id , end_time ; 



4) alter system set "_cursor_obsolete_threshold"=1024 scope=spfile sid='*' ;   -- (Doc ID 2658406.1)




5) 
Oracle 19c | _exclude_seed_cdb_view
• Default is TRUE
• This hides all objects in PDB$SEED
• Can be very problematic after patching - you need to see everything!
• Recommendation: MOS Note 1940806.1
• _exclude_seed_cdb_view=FALSE




Things to Consider to Avoid SQL Performance Problems on 19c (Doc ID 2773715.1)

Performance Issues when using PDBs with Oracle RAC 19c and 18c (Doc ID 2644243.1)



6) 
Container_data and target_pdbs


7) _shared_pool_reserved_pct 15


B)  Walk through  New Features and Depreciated features .




C)  Known Bugs  and Issues : 



1) Oracle 19c: Bug 27175987 — Partition Pruning Not Work For SQL With Predicate Of User Function After Upgrade To 19c

https://abdul-hafeez-kalsekar.blogspot.com/2022/05/oracle-19c-bug-27175987-partition.html


2) Mga Now  Part of Pga   causing ORA-04030 / ORA-04036

https://abdul-hafeez-kalsekar.blogspot.com/2022/04/pga-nightmare-in-oracle-19c-database.html



3) MMON slave using More PGA  :  272197.1 

4) Multiple SYSRAC  Process  2832806.1 



1) Query Performance Issues after upgrade to 19C (Doc ID 2739411.1)
2) Partition Pruning Not Work For SQL With Predicate Of User Function After Upgrade To 19c (Doc ID 2717940.1)
3) Things to Consider to Avoid SQL Performance Problems on 19c (Doc ID 2773715.1)


5) 
31915444
31915444.8


window function
Bug 34381258 - Merge Statement Performance When Fix For Bug 30822446 Is Enabled (Doc ID 34381258.8)


Why the PARALLEL_MAX_SERVERS Value Reduced After Database Upgrade to 19c (Doc ID 2796822.1)
he default value of PARALLEL_THREADS_PER_CPU has been reduced from 2 to 1 starting from 18c ,



PARALLEL_MAX_SERVERS  got changed to higher value after migration 

https://community.ifs.com/framework-experience-infrastructure-cloud-integration-dev-tools-50/database-performance-issues-by-using-oracle-parallel-query-concept-19810




Job Using Parallelism Hangs After Upgrade To 19c (Doc ID 2861895.1)

_px_cdb_view_enabled = FALSE
_partition_cdb_view_enabled =FALSE




6) 19c  shared pool has more usage from PRTMV 


7) 
Bug 31142749 - Querying ALL_ARGUMENTS Is Slow In 19.6 PDB (Doc ID 31142749.8)

alter session set container_data=CURRENT_DICTIONARY;

show parameter container_data
alter system set container_data=CURRENT_DICTIONARY scope=spfile sid='*';



6)   databaase coming from non exadata to  exadata .  cell pfflaoding causing issues 



pga  sr   3-38379948411 
_optimizer_squ_bottomup=false 
_optimizer_unnest_scaler_sq=false 
_simple_vew_merging=false 




D)  Check  Top Sql   post migration  .

https://abdul-hafeez-kalsekar.blogspot.com/2022/05/oracle-sql-tuning-health-check-script.html







E)  Solutions  Customers have tried and 

1) Cardinality feedback of version 12c may lead to instability of the execution plan. To avoid the occurrence of 19c, it is recommended to close:

alter system set "_optimizer_use_feedback"=false sid ='*' scope=spfile;




2)  Setting OPTIMIZER_FEATURES_ENABLE   to lower version  of Source 

Try hints 

select /*+ optimizer_features_enable('10.1.0') */


select
   optimizer_feature_enable,
   count(*)
from
   v$system_fix_control
group by
   optimizer_feature_enable
order by 1 asc;




3) Disabling Adaptive Plans  

OPTIMIZER_ADAPTIVE_PLANS to FALSE,
OPTIMIZER_ADAPTIVE_STATISTICS to FALSE

http://abdul-hafeez-kalsekar.blogspot.com/2021/06/oracle-adaptive-lgwr-and-adaptive-plans.html




4)  "_complex_view_merging"=FALSE



5) Turn off the cross-instance parallel parameter

alter system set parallel_force_local=true scope=spfile sid='*';

 "_px_cdb_view_enabled" = false




6) Turn off the automatic adjustment of UNDO_RETENTION

Avoid a series of bugs that happen with undo auto-adjustment:

alter system set "_undo_autotune"=false scope=spfile sid='*';
alter system set undo_retention=10800 scope=spfile sid='*';


https://smarttechways.com/2021/05/07/_optimizer_use_feedback-_optimizer_gather_feedback-in-oracle/


Below  is changes  for sample of  one of application 

SQL> alter system set "_allow_insert_with_update_check"=true scope=both;
SQL> ALTER system SET session_cached_cursors = 400 scope = spfile;
SQL> ALTER system SET shared_pool_size = 1G scope = BOTH ;
SQL> ALTER system reset streams_pool_size scope = BOTH;
alter system set "_optimizer_nlj_hj_adaptive_join"= FALSE scope=both sid='';
alter system set "_optimizer_strans_adaptive_pruning" = FALSE scope=both sid='';
alter system set "_px_adaptive_dist_method" = OFF scope=both sid='';
alter system set "_sql_plan_directive_mgmt_control" = 0 scope=both sid='';
alter system set "_optimizer_dsdir_usage_control" = 0 scope=both sid='';
alter system set "_optimizer_use_feedback" = FALSE scope=both sid='';
alter system set "_optimizer_gather_feedback" = FALSE scope=both sid='';
alter system set "_optimizer_performance_feedback" = OFF scope=both sid='';



F) Other Parameter .

Parameters 

New initialization parameters in Oracle Database 19c


DATA_GUARD_MAX_IO_TIME
DATA_GUARD_MAX_LONGIO_TIME
ADG_REDIRECT_DML (TRUE or FALSE)
MAX_DATAPUMP_JOBS_PER_PDB
aws_pdb_autoflush_enabled (TRUE or FALSE)





G) Real Time Statistics 

EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

/*+ monitor no_gather_optimizer_statistics */


“_optimizer_gather_stats_on_conventional_dml” and “_optimizer_use_stats_on_conventional_dml” which are true by default
_optimizer_stats_on_conventional_dml_sample_rate (at 100%)


This is, as far as I know, result of Oracle applying real time statistics - new feature introduced in 19C. Try setting _optimizer_gather_stats_on_conventional_dml and/or _optimizer_use_stats_on_conventional_dml.


INsert /*+NO_GATHER_OPTIMIZER_STATISTICS*/ into t
as select * from table_name;

This is, as far as I know, result of Oracle applying real time statistics - new feature introduced in 19C. Try setting _optimizer_gather_stats_on_conventional_dml and/or _optimizer_use_stats_on_conventional_dml.

Check the real time statistics is gathered

SQL> alter session set "_optimizer_gather_stats_on_conventional_dml" = false
  2  /

Session altered.

SQL> alter session set "_optimizer_use_stats_on_conventional_dml" = false
  2  /

SQL> EXPLAIN PLAN FOR
  2  SELECT  --+ OPT_PARAM('_optimizer_use_stats_on_conventional_dml' 'true')
  3          COUNT(*)
  4    FROM  activity qRoot
  5    WHERE qRoot.Retired = 0
  6  /

 




Note: The view DBA_TAB_COL_STATISTICS and DBA_TAB_STATISTICS will contain STATS_ON_CONVENTIONAL_DML in the NOTES column and SHARED in the SCOPE column.

SET PAGESIZE 5000
SET LINESIZE 200
COL COLUMN_NAME FORMAT a13
COL LOW_VALUE FORMAT a10
COL HIGH_VALUE FORMAT a10
COL NOTES FORMAT a25
COL PARTITION_NAME FORMAT a13

SELECT COLUMN_NAME, LOW_VALUE, HIGH_VALUE, SAMPLE_SIZE, NOTES
FROM USER_TAB_COL_STATISTICS WHERE TABLE_NAME = 'TRAN';

COLUMN_NAME   LOW_VALUE  HIGH_VALUE SAMPLE_SIZE NOTES
------------- ---------- ---------- ----------- -------------------------
ITEMS_QANTITY 10111      224422     7854        STATS_ON_CONVENTIONAL_DML
ITEMS_QANTITY 10111      2125349    4568
SELECT NVL(PARTITION_NAME, 'GLOBAL') PARTITION_NAME, NUM_ROWS, BLOCKS, NOTES
FROM USER_TAB_STATISTICS WHERE TABLE_NAME = 'TRAN';



https://www.youtube.com/watch?v=NpnET64plOM


SELECT a.ksppinm AS parameter,
       a.ksppdesc AS description,
       b.ksppstvl AS session_value,
       c.ksppstvl AS instance_value
FROM   x$ksppi a,
       x$ksppcv b,
       x$ksppsv c
WHERE  a.indx = b.indx
AND    a.indx = c.indx
AND    a.ksppinm in ('_optimizer_gather_stats_on_load','_optimizer_gather_stats_on_conventional_dml','_optimizer_use_stats_on_conventional_dml')
ORDER BY a.ksppinm;











H) Encrypt  BACKUPS 








References:

https://docs.oracle.com/cd/F19136_01/tgsql/sql-tuning-guide.pdf




Comments

Post a Comment

Popular posts from this blog

Oracle Materialized View In-Depth and Materialized View refresh issues in 19c

   We    had  situation demanding  tuning of  MV  refresh and  options  like  TUNE_MVIEW  and Atomic_Refresh was explored . Hence  Documenting materialized view sample commands  and views in this  blog .     After migration if we are  facing slowness  in  Materialized View refresh , we can explore to re-create  Materialized View   Since  main topic for this blog   was MV issues  in 19c ,  posting that topic   first in line below  We also   have Real-Time Materialized Views  to explore .  To Note :  1) We can  create Mv  on ON PREBUILT TABLE  2) We can create Index on Mv  3) We can  gather stats on mv  just like other table .  4) Mv can be REFRESH_MODE ON COMMIT / REFRESH ON DEMAND. 5) Mv  REFRESH_METHOD can be  Complete / FAST or FORCE . A FORCE attempts a FAST...
Read more

Troubleshooting Oracle Rac Crs/Cluster Startup Issues

  We need to check what  component  are started and what are pending based on that we need to  take approach .  Known Issues  / Issues faced :  1)  ora.crsd remains in INTERMEDIATE and commands like crsctl hang and Logical corruption check failed (Doc ID 2075966.1) 2) CRSD Failing to Start or in INTERMEDIATE State Due to OLR corruption. (Doc ID 2809968.1)  3) CRS is not starting up on the cluster node (Doc ID 2445492.1)  --  Missing execute permission  on <GRID_HOME>/srvm/mesg directory 4)  Crs activeversion is different on both nodes after patching/upgrade  5)  Permission of Voting/Ocr disk was changed  6)  ohasd process was no started due to missing /etc/systemd/system/ohasd.service  7)  Asm startup issues faced  due to  below  -->  asm disk were not shared between nodes  --> asm_diskstring parameters were somehow removed  7) asm not s...
Read more

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

  In this Document Goal Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Information in this document applies to any platform. GOAL Goal of this document is to provide the method to purge the old records of Optimizer Statistics Advisor Task namely AUTO_STATS_ADVISOR_TASK that consumes huge SYSAUX space. Huge no.of old records retained in WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK or INDIVIDUAL_STATS_ADVISOR_TASK incurs heavy SYSAUX space. AUTO_STATS_ADVISOR_TASK is meant for Automatic Statistics Advisor task while INDIVIDUAL_STATS_ADVISOR_TASK is for Manual Statistics Advisor task.  DBA_ADVISOR_PARAMETERS displays all advisor task parameters and their current values in the database. There is a parameter name called EXECUTION_DAYS_TO_EXPIRE. This parameter is set in no.of days. Executions older than the value(no.of days) set would be purged automaticall...
Read more