Peter Ramm's Oracle performance analysis stuff

After migrating DB instances to Exadata cloud service in Oracle's OCI cloud we noticed that selects from the view UNIFIED_AUDIT_TRAIL did not succeed in accepted time, even if they were quite simple. Looking at the execution plan shows that nearly the whole excution time is spent on full table scan on table X$UNIFIED_AUDIT_TRAIL. A click in the column "Waits" in that line shows the wait events for that execution plan line that have been recorded by ASH. Mostly X$-tables will present an SQL interface for Oracle's internal memory structures. In this case the wait event 'Disk file operations I/O' let the X$UNIFIED_AUDIT_TRAIL table look like a wrapper for file structures on disk. Trying to explain what happened Some blog posts and a note in the documentation gave the needed background info. Note:1. When the database is writeable, audit records are written to the unified audit trail. If the database is not writable (typically occurs when the datab...

Since release 18c there's an undocumented feature Parallel Shared Hash Join which introduces sharing memory between parallel query slaves. The required memory for these shared hash tables is allocated in a new memory region known as the Managed Global Area (MGA). See also doc. ID 2638904.1. This feature is particularly beneficial for costly HASH JOIN BUFFERED operations that spill large amounts of data into the temporary tablespace. These operations may benefit from transformation into HASH JOIN SHARED operations. By sharing hash tables between parallel query (PQ) processes, instead of each PQ server maintaining its own, runtime can be significantly reduced. This reduces overall memory requirements, enabling more data to be processed before spilling to disk in the temporary tablespace. There are several ways to activate the Parallel Shared Hash Join: set '_px_shared_hash_join'=true; at system or session level define the PQ distribution strategy for a particular...