Tempfile Read /Writes and ASM

I truly believe that database myths are the scourge of the database world.  With that said, I have to blame myself for not being specific enough in one of my own blog posts and propagating one myself!  As it was brought to my attention by Tanel Poder and Greg Rahn, I hadn’t put a post out here until now, so apologies!

I have flipped back to an almost exclusively 10g environment the last three months, but even though I’m back to my old stomping grounds, now fully staffed with almost double the DBA’s that were present when I left, have been able to implement ASM.  With this change, I also need to update my data on how tempfiles are written to.  When you read my blog on temp usage in parallel, in must be reminded that this post is for a Non-ASM environment.  I also think for those that have high waits in temp read and writes, this should be an extra selling feature of ASM.

ASM, when implemented in any Oracle environment, 10g, included, will change the way that tempfiles are written to.  Instead of single file writes, even when multiple tempfiles per a temp tablespace are present, with ASM, it WILL stripe across the files efficiently, (unless statistics are in issue) with parallel.  There would then only be the benefit of having multiple temp tablespaces if you would like to allocate a different temp tablespace to different processes, but even then, the performance gain was marginal in my tests.

I did receive similar performance gains of temp tablespace groups without ASM as a single temp tablespace with multiple files with ASM.  I was only able to gain extra performance when spreading heavy hitting temp usage across temp tablespace groups during high concurrency situations.   the overall performance gain was only 12%, which was no where near what I experienced in the Non-ASM environment choice to go to temp tablespace groups with parallel processing.  These were processes that utilized over 50GB of temp per parallel process.

I still have processes that limit my tuning options that have me daydreaming about multiple temp tablespace groups on multiple ASM groups, residing on SSD, but again, I’m daydreaming … 🙂