Issues affecting multi-version SQL performance.
Oracle SQL performance can be affected by the setting of two Oracle init parameters, sort_area_size and hash_area_size. These parameters define the available process memory space for performing SQL sorts in memory.
Multi-version SQL perform sort operations between the business, feature, spatial, and then add and delete tables when fetching the rows that meet the criteria of the query. When the sort exceeds the available space in memory, the process moves the sort from memory to disk. At this point performance suffers.
It is important to detect if your applications SQL and multi-version SQL sort operations are being performed on disk, specifically in the temporary tablespace.
Run the following statement as the Oracle SYSTEM user, or a user who has select permissions on the V$ views:
SELECT DISTINCT a.username, b.tablespace, b.segtype, b.extents, b.sqladdr
FROM v$session a, v$sort_usage b
WHERE a.saddr = b.session_addr;
WHERE address = '<sqladdr from above>'
ORDER BY piece;