Query to help determine what PGA sizing might be?

[TwoProc]

I'm on Oracle 9.2 and I'm not using the Auto Aggregate PGA. If I were to do convert to this, can anyone provide me with a query to determine what I might be using. I do have statspack runnng as per the manual. And, according to the manual, there is no way to simply see that I'm using xxx for unaggregated PGA currently, so that I could set it for xxx for aggregated PGA.
Does anyone know of a query that would help me determine what my unaggregated PGA for each individual connection in dedicated server mode might be?

[Patti Johnson]

I've used this script - found on Metalink note 1070975.6.

column name format a25
column total format 999 heading 'Cnt'
column bytes format 9999,999,999 heading 'Total Bytes'
column avg format 99,999,999 heading 'Avg Bytes'
column min format 99,999,999 heading 'Min Bytes'
column max format 9999,999,999 heading 'Max Bytes'
ttitle 'PGA = dedicated server processes - UGA = Client machine process'


compute sum of minmem on report
compute sum of maxmem on report
break on report

select se.sid,n.name,
max(se.value) maxmem
from v$sesstat se,
v$statname n
where n.statistic# = se.statistic#
and n.name in ('session pga memory','session pga memory max',
'session uga memory','session uga memory max')
group by n.name,se.sid
order by 3
/

One problem I have had with using the AUTO workarea_size_policy is that it does not allocate sort space as generously as I would like. There apprears be a limit of about 5% of total space per session - regardless of the number of sessions. So I tend to set the pga_aggregate_target higher than I originally calculated was needed.

Patti

[Eric Antunes · Accepted solution]

Hi TwoProc,

Since you are just talking about PGA, you should the following value, where 1.3 is for overhead:

select n.name
, round( sum(se.value) * 1.5 / 1024 / 1204) pga_agg_target_mb
from v$sesstat se,
v$statname n
where n.statistic# = se.statistic#
and n.name = 'session pga memory'
group by n.name;

PS: I have pga_aggregate_target = 640 and the above query is returning 671

Best Regards,

Eric Antunes

[Eric Antunes]

Hi,

Where I wrote:

"Since you are just talking about PGA, you should the following value, where 1.3 is for overhead:...", I wanted to say:

Since you are just talking about PGA, you should the following value, where 1.5 is for overhead...:

[TwoProc]

Thanks Patti and Eric.

This helps quite a bit, and is more like what I expected. I ran something I had from a script I found on Oracle, and it way underreported the numbers. From your two scripts, although the numbers are very different for just the PGA ports (I edited Patti's script down to just that factor) - and they vary by 33% - it gives me a better guideline, and more in line of what I was thinking the requirement was than what was being provided to me before.

Thanks much!