RE: experience with undocumented pga parameters
- From: Josh Collier <Josh.Collier@xxxxxxxxxxxx>
- To: Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 11 Mar 2013 16:01:36 +0000
Thanks! This is very helpful advice.
-----Original Message-----
From: Randolf Geist [mailto:info@xxxxxxxxxxxxxxxxxxxxxxxxx]
Sent: Saturday, March 09, 2013 1:30 AM
To: oracle-l@xxxxxxxxxxxxx; 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"
--
//www.freelists.org/webpage/oracle-l
Other related posts:
