RE: experience with undocumented pga parameters
- From: Josh Collier <Josh.Collier@xxxxxxxxxxxx>
- To: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- Date: Fri, 8 Mar 2013 23:25:36 +0000
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@xxxxxxxxx]
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@xxxxxxxxxxxx<mailto:Josh.Collier@xxxxxxxxxxxx>> 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"
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
