Poor multi-version SQL performance

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:

Code:
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; 

This statement will return all users who are currently executing SQL that is being sorted in the temporary tablespaces.

The segtype is the type of sort operation. For example, sort or hash, the extent value identifies how many extents are being used and the sqladdr is the address in memory of the cursor executing the statement.

The sqladdr can also be used to detect the actual query being executed by executing the following statement:

Code:
SELECT sql_text
FROM v$sqltext 
WHERE address = '<sqladdr from above>'
ORDER BY piece; 

It is important to note, if the SQL statement is going to be sorted on disk, ideally the sort should occur in one extent. Each extent that is required impacts performance; therefore, increase your temporary tablespace's initial extent value.

As you slowly begin to notice the trends of what statements are being sorted on disk, you can start to identify the optimal size of the sort_area_size and hash_area_size parameters. For example, if sort statements are requiring 3 to 4 extents each time, and your temporary tablespaces initial and next extent are 128K, your SQL will typically require 512K of space for the sort operation. Therefore, to keep the query from exceeding the process' available sort_area memory, increase the sort_area_size parameter to 512K or slightly greater.

Remember, the larger the size of these Oracle init parameters, the more memory each connected user requires. Make sure you are not exceeding the available hardware RAM.