There are already several articles on the web about how to speed up TEMP I/O in an Oracle database (I really like this one from Alex Fatkulin) , specially since even the cool and fancy Engineered Systems still behave pretty much the same when it comes to spilling to disk, so probably this blog post is not going to show anything new but at least it can work as a reminder for me 🙂
I have an artificial SQL (pretty ugly to be honest) that spills to disk, around 665M
select count(distinct c1) from (select c1 from (select rownum||lpad('x',999,'x') c1 from dual connect by rownum <= 5*1e4), (select rownum n1 from dual connect by rownum <= 1e2))
Considering TEMP data doesn’t require any kind of persistency, redundancy and recoverability we can considering creating the TEMP tablespace on a file under tmpfs/ramfs. I like tmpfs a little better just because it has the ability to stop growing in size (even though it might swap hence reducing the benefit of going to memory).
mount -o size=1G -t tmpfs none /mnt/mytmpfs
and then from SQL*Plus
SQL> create temporary tablespace TEMP2 tempfile '/mnt/mytmpfs/temp2.f' size 1G; SQL> alter user mpagano temporary tablespace temp2;
Notice how the same amount of data has been spilled to disk but the elapsed time is half and the “direct path write temp” is basically gone.
DISCLAIMER: There are a few “oddities” in the both reports, the 0 buffer gets and no “direct path read temp” in the first one and the absence of the “direct path write temp” in the second one, my educated guess is the combination of low number of samples (the SQL is fast in both cases so number of samples is low) combined with the low wait time for the events (the VM writes to a file that could benefit from caching) could justify why not all the info are captured.