Imagine a query that faces PGA shortage and heavily spills to temporary tablespace to complete (sorting, hashing and etc.). A typical solution is to tune the query, whereas the following can also act as a way out.
The environment is Oracle 19.3 on Linux 6.4. A test table with ~4 million rows is indexed on one of the columns with temporary tablespace datafiles residing on disk. Index creation requires sorting and the volume of data (4 million rows) forces disk spills. In such a case, 11904 temporary datafile blocks have been allocated and the query completes in 16 seconds.
Then, we load the temporary datafiles in /tmpfs on Linux which caches it in the RAM. Oracle is actually agnostic where the files reside: disk or memory. This time the index creation (started from scratch) spills to 11904 blocks just like before but the query runs two times faster. OS analysis also confirms that the blocks reside in memory this time.
In Favor of Temporary Tablespaces
Posted in Oracle.