Peter Ramm's Oracle performance analysis stuff

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...

Recently a problem occured using function based indexes: The optimizer switched to full table scan on a table with 3.2 billion rows although the used function expression is covered by a function based index. The expression often evaluates to NULL ond so this index has only 4.9 million rows ( < 1% of the table rows ) and a low selectivity of approx. 4 rows per key. Let's consider the simple case. The table INVOICE has two interesting columns: - CUSTOMER_ID 38 mio distinct values, not null, avg. 84 rows/key - OPEN_AMOUNT not null, 4,8 mio. records > 0 A function based index was created to quickly identify the small portion of rows of a certain customer where the OPEN_AMOUNT is > 0 CREATE INDEX IX_OPENAMOUNT ON INVOICE(CASE WHEN Open_Amount > 0 THEN Customer_ID END); This SQL is used to select the rows of a certain customer where the OPEN_AMOUNT is > 0 SELECT ID FROM Invoice WHERE CASE WHEN Open_Amount > 0 THEN Customer_ID END = :B1 ; The executi...