Temp Space performance

From: Matt <mvshelton_at_chartermi.net>
Date: Tue, 19 May 2015 20:49:32 -0400
Message-ID: <01f501d09296$d5e7eac0$81b7c040$_at_net>

Temp Tablespace tuning on Exadata. I am currently running a 11.2.0.3 RAC and I have also done testing with 12.2.0.2 RAC.  

On test/uat environment we have older x2-2 so using flash cache for temp is limited for this environment.  

We are running queries with parallel 8 and outer joining 10 tables. Quite a few of the table are a billion rows and are using HCC query high compression. I have tuned the queries and we good there. The slowness we are seeing is in the reading and writing to temp.  

I have done multiple testing with temporary tablespace groups vs. a single temporary tablespace with multiple small tempfiles. Hands down the temporary tablespace groups are better than a single temporary tablespace in all of my testing.  

Now for the question, I am seeing less temporary tablespace usage for hash outer join by approximately 5g when using temporary tablespace groups with bigfiles vs. temporary tablespace groups with small files. What has everyone else seen when testing this on Exadata?  

Also what about testing with different uniform sizes? I read Oracle white papers recommending for 64mb uniform size on temporary tablespaces.  

Thanks, Matt      

--
http://www.freelists.org/webpage/oracle-l

Received on Wed May 20 2015 - 02:49:32 CEST