RE: experience with undocumented pga parameters

From: Josh Collier <Josh.Collier_at_banfield.net>
Date: Wed, 13 Mar 2013 21:27:07 +0000
Message-ID: <D0534F8D31056242BE8E38FA9413FDA817E19A4D_at_M1EXCHMB12.mmi.local>



I have not been able to get my sample query to act any differently with the setting of _pga_max_size

My pga is set to 200G

Query uses 17GB of temp with DoP 64, with 24G cumulative memory allocated to PGA

When I set _pga_max_size to 200G

Query uses 17G of temp with DoP 64, with 24G cumulative memory allocated to PGA

I was under the assumption that setting this undocumented parameter would enable the database to use more of the PGA per process?

I must have something wrong.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Josh Collier Sent: Monday, March 11, 2013 9:02 AM
To: Randolf Geist; oracle-l_at_freelists.org Subject: RE: experience with undocumented pga parameters

Thanks! This is very helpful advice.

-----Original Message-----
From: Randolf Geist [mailto:info_at_www.sqltools-plusplus.org] Sent: Saturday, March 09, 2013 1:30 AM
To: oracle-l_at_freelists.org; Josh Collier Subject: Re: experience with undocumented pga parameters

Josh,

when you say "sorts", do you specifically refer to SORT ORDER BYs, or does this include other workarea based operations like WINDOW SORTS, or may be even HASH GROUP BYs or HASH UNIQUEs?

In particular with HASH GROUP BY and HASH UNIQUE there are some issues with how Oracle manages automatic PGA - if that applies to your situation you might read the summary at the bottom of that post: http://oracle-randolf.blogspot.com/2011/01/hash-aggregation.html

Can you check V$PGASTAT, just to make sure you don't have anything weird going on with how Oracle thinks it needs to manage PGA?

Note that Oracle only uses a certain amount of memory per process / workarea, so if you want to make use of such large PGA memory you would need to use an appropriate Parallel Degree of your SQL executions (DOP) - theoretically in recent releases a single PX execution should be allowed to consume up to 50% PGA_AGGREGATE_TARGET across all workareas of that execution provided the DOP is sufficiently high.

Even then, if your distribution of work is skewed, and only a few of the Parallel Worker processes need to do all the work, they still might need to spill to disk, even if you allow up to the maximum of 2GB per process / workarea.

Instead of fiddling with undocumented parameters you could try to use manual workarea_size_policy for your larger queries and see if that changes the memory /temp usage significantly. This can be done on session level using ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL SORT_AREA_SIZE = 2000000000 HASH_AREA_SIZE = 2000000000 just as an example.

Hope this helps,
Randolf

> Does anyone on this list have experience with setting the following parameters on 11gR2 linux?
> I have a server with 250G available for the PGA and am having a hard time getting the database to stop spilling sorts to temp. I have a ticket open with support so that I can get into a support configuration with these settings, however, I am having a hard time finding a combination of these 4 that will prevent even small queries from sending sorts to temp.
>
> "_smm_max_size"
> "_pga_max_size"
> "_smm_px_max_size"
> "_smm_isort_cap"

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


--
http://www.freelists.org/webpage/oracle-l
Received on Wed Mar 13 2013 - 22:27:07 CET

Original text of this message