19c

Things to Consider to Avoid Database Performance Problems on 19c (Doc ID 2773012.1)



Popular Known Issues

**********************************************************************************
Problem 1: Tablespace level IO statistics are missing in 19c AWR reports

Solution: The Bug Document 25416731.8 is fixed from 19.8 DBRU onwards. Apply 19.8 DBRU or above and do the below steps.

To get Tablespace IO stats data back in AWR reports in 19.X versions, please run following commands as SYS:

$ sqlplus / as sysdba
exec dbms_workload_repository.modify_table_settings(table_name => 'WRH$_FILESTATXS', flush_level => 'TYPICAL');
exec dbms_workload_repository.modify_table_settings(table_name => 'WRH$_DATAFILE', flush_level => 'TYPICAL');
exec dbms_workload_repository.modify_table_settings(table_name => 'Tempfile Group', flush_level => 'TYPICAL');
exec dbms_workload_repository.modify_table_settings(table_name => 'WRH$_TEMPSTATXS', flush_level => 'TYPICAL');

When new AWR snapshots are generated, you will start getting Tablespace IO stats data for checking IO performance.

FYI: You may have to run these commands in the PDBs also if you are generating AWR snapshots at PDB level and they are missing Tablespace IO stats data in AWR reports.

**********************************************************************************
Problem 2: High Version counts for the SQLs that are consistently above 1024

Solution: Refer below document for controlling Version counts for the SQLs that are consistently above 1024.

Document 2431353.1 High Version Counts For SQL Statements (>1024) Post Upgrade To 12.2 and Above Causing Database Slow Performance



For non-CDB environment in 12.2 and above, set the parameter to the below value to avoid the mutex concurrency issues due to high version counts during OLTP:
INIT.ORA: _cursor_obsolete_threshold=1024
or
SPFILE: SQL> alter system set "_cursor_obsolete_threshold"=1024 scope=spfile;



**********************************************************************************
Problem 3: High CPU for SQL statements with too many columns. The short stack shows functions spinning on qosdGetOptDir, qosdInitDirCtx, qosdUpdExprExecStatsRws. There could be high latch waits including GES latches in RAC.

Solution: Set, _column_tracking_level=1 at system level. Its a dynamic parameter. This is to avoid the extensive column usage tracking which could incur more CPU. The default value was 1 in 11.2 & 12.1 and changed to 21 and higher from 12.2 onwards.


**********************************************************************************
Problem 4: If Database is upgraded from 11.2 to 19c, then there is a change in LGWR architecture from 12c onwards by using parallel adaptive LGWR. This might cause slow LGWR throughput sometimes causing 'log file sync' waits in 19c for foreground sessions. This is due to some bug with adaptive behavior of parallel LGWR to serial LGWR and vice versa.

Solution: Evaluate the new LGWR architecture (enabled by default) in UAT before PROD. To use the old LGWR architecture, set the below parameter:

_use_single_log_writer=TRUE /* default value: ADAPTIVE */

Note: This is NOT a dynamic parameter. It requires database bounce.



**********************************************************************************
Problem 5: High library cache lock waits in 19c database during Partition maintenance

Solution: Document 2619066.1 High Library Cache Lock Waits After Upgrading To 19C During Partition Index Maintenance
SQL> alter system set "_optimizer_gather_stats_on_load_index"=FALSE;



Refer below documents for troubleshooting library cache related waits:-

Document 444560.1 Troubleshooting Library Cache: Lock, Pin and Load Lock
Document 1353015.1 How to Identify Hard Parse Failures Causing Library Cache contention
Document 2746493.1 How To Trace Overall Library Cache Objects Invalidation Happening At Particular Period