RE: Temp Space performance

My test case involved a query with an outer join from a to b and a to b again. Temp space would get exhausted on the second outer join. The extra 5gb of disk space was used on the first outer join from a to b.  

How are you measuring this difference ? Instead of querying, I was lazy and used Oracle SQL*Monitor in Grid Control 12c.  

5GB out of how much ? 5gb of physical temp space used from temp disk space.  

What's the current uniform extent size on your temporary tablespace ? Testing was with 1mb and 5mb. The plan was to test with an even a larger uniform size. I also read recommendations to use 64mb.  

I will continue my testing in the near future and will share my results.  

The Oracle 12 database I used is 12.1.0.2 RAC not 12.2.0.2....., sorry typo.  

Thanks, Matt

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Wednesday, May 20, 2015 3:57 AM
To: mvshelton_at_chartermi.net; 'Oracle-L Freelists' Subject: RE: Temp Space performance  

How are you measuring this difference ?
Total size of temporary space allocated, number of waits for reads/writes, v$temp_space_usage, reports from v$sql_plan_statistics_all / dbms_xplan.display_cursor/monitoring ?

5GB out of how much ?
What's the current uniform extent size on your temporary tablespace ?    

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Matt [mvshelton_at_chartermi.net]

Sent: 20 May 2015 01:49
To: 'Oracle-L Freelists'
Subject: Temp Space performance

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      

No virus found in this message.
Checked by AVG - www.avg.com
Version: 2014.0.4800 / Virus Database: 4311/9829 - Release Date: 05/20/15

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