Oracle Internals

Problem Statement  Recently we have noticed global temporary tables (GTT) with the option "Preserve rows after commit" not working as expected post-migration to oracle 19c with the distributed transaction which uses database links as compared to 12c where it was working fine. Observation It has been identified with the 19c parameter temp_undo_enabled parameter is by default set to true which was false in 12c has an impact on the above behavior. To understand the reason we would need to understand about parameter temp_undo_enabled. temp_undo_enabled parameter means undo generated for the changes to temporary tablespace will be written to permanent undo tablespace when it is set to false, when this parameter value is set to true it means undo generated for temporary tables will be written to temporary tablespaces. This will mean lesser redo log generation and better database performance. It also reduces the storage requirement due to lesser redo log generation. In cases where t...

Problem Statement  Recently came across one interesting issue where we found _cursor_obselete_threshold parameter having the value of 8192 causing database performance issues. On investigation, it was found this parameter value is the maximum limit for obsoleting the parent cursors in a multitenant environment and cannot be increased beyond 8192. This default value of the parameter is increased heavily from 1024 to 8192.  Parameter _cursor_obselete_threshold was first introduced in version 11.2.0.3 with default to 100 and then increased to 1024 in 11.2.0.4 and remains the same until 12.1. From 12.2 onwards to support 4096 PDB which was only 252 PDB till 12.1 this parameter default value is changed to 8192. In some cases, it is observed high version counts for SQL statement post-upgrade to 12.2 or 19c causing database performance slowness. Those using a lesser number of PDB in their environment do not need to for this much high value and cause set this to 1024. How to identify ...