Re: Temp Space performance

From: <l.flatz_at_bluewin.ch>
Date: Wed, 20 May 2015 07:37:24 +0000
Message-ID: <1349886.11877.1432107444344.JavaMail.webmail_at_bluewin.ch>



Hi Matt,
I know the issue. memory is a weak point of the X2-2. You try to configure PGA as big as possible. Increase memory if possible.  On 11G be carefull because the db can overrun your limit. For PGA You have to keep a security margin of 100%, better 200% unassigned. You can increase the available PGA a bit by runing on two nodes and increasing DOP. You have to test. Your biggest challange is likely to be hash join buffered. Randolf Geist and Jonathan Lewis wrote about it. Your best bet is to avoid it. The parallel distribution broadcast could help if at all sensible. Partition wise join is always an option. You can sacrifice part of the flash cache as grid disk, but you should mirror, which is tough. Testing temp space configuration never produced something usefull in my own work. The difference it makes seems allways to little. My tests were not extensive, though.
I always wonder that Oracle can write "with /*+ materialize */ " so quick. Thanks
Lothar
----Urspr√ľngliche Nachricht----

Von : mvshelton_at_chartermi.net
Datum : 20/05/2015 - 02:49 (UTC)
An : oracle-l_at_freelists.org
Betreff : 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      

--

http://www.freelists.org/webpage/oracle-l Received on Wed May 20 2015 - 09:37:24 CEST

Original text of this message