TEMP usage in Parallel Query | Why can't it use the spare RAM?
802327Oct 1 2010 — edited Oct 1 2010Hi All
I have a query running with parallel degree 8 on Red Hat 64 bit, 10.2.0.4.0, with 16 CPUs and 32GB of RAM.
My PGA target is 28 GB, SGA target at 2 GB.
I am using this Oracle instance purely for some datamart ETL, in order to deliver data to a separate reporting database. No other clients hit the instance.
I watch the instance during the query. Depending on volumes, I will sometimes get TEMP memory spill. This is not huge - maybe 2 to 4 GB of use, but even so, I don't want it to spill ,as the duration of the batch job is bigger than it needs to be.
I've tried increasing pgamax_size to use more RAM, and this helps to a point. However, there are some Oracle limits, after which TEMP space comes back into play. I've also tried tinkering with the smmmax_size and smmpx_max_size, but cannot seem to get any improvement. Changing the degree of parallelism makes little difference.
Looking at the Linux config, I have reduced swappiness to prevent the Oracle processes from being swapped out. And I can observe that only 4 to 6 GB of actual RAM is being used. I still have 20 - 24 GB completely free.
So - how do I get Oracle to actually consume this RAM rather than using TEMP space?
One option is to configure a RAM DISK of 8GB, and build the TEMP tablespace in that location. But is this really the answer? It seems like a 'fudge' rather than a solution.
The query plan is tuned well, and seems optimal.
Any help would be appreciated - thanks, Ankle.