RE: experience with undocumented pga parameters

From: Josh Collier <Josh.Collier_at_banfield.net>
Date: Fri, 8 Mar 2013 23:25:36 +0000
Message-ID: <D0534F8D31056242BE8E38FA9413FDA817E1461C_at_M1EXCHMB12.mmi.local>

I have queries that under these settings use temp like this Memory used : 18MB
Temp used : 12 MB

Also have larger queries that report thusly: Memory 18G
Temp: 49G

SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 245G

workarea_size_policy                 string      AUTO

I suspect the internal memory limits are cumulative and a process will begin to use temp when its memory allocation has hit a limit over the span of a query that has multiple sorts and joins?

I am using the real time sql monitoring in OEM to determine size of memory and temp for a given query. Could this be misleading me?

sort_area_retained_size, Sort_area_size, Hash_area_size show as also being set when I create a pfile from spfile, but my understanding is that they are ignored when workarea policy is set to auto.

Josh C.

From: Niall Litchfield [mailto:niall.litchfield_at_gmail.com] Sent: Friday, March 08, 2013 10:39 AM
To: Josh Collier
Cc: ORACLE-L
Subject: Re: experience with undocumented pga parameters

What are the documented parameters set to :) and what is a 'small sort' On Mar 8, 2013 5:27 PM, "Josh Collier" <Josh.Collier_at_banfield.net<mailto:Josh.Collier_at_banfield.net>> wrote: 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 Sat Mar 09 2013 - 00:25:36 CET