Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Query to help determine what PGA sizing might be?

SOLVED
Go to solution
TwoProc
Honored Contributor

Query to help determine what PGA sizing might be?

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?
We are the people our parents warned us about --Jimmy Buffett
4 REPLIES
Patti Johnson
Respected Contributor

Re: Query to help determine what PGA sizing might be?

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
Honored Contributor
Solution

Re: Query to help determine what PGA sizing might be?

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
Each and every day is a good day to learn.
Eric Antunes
Honored Contributor

Re: Query to help determine what PGA sizing might be?

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...:
Each and every day is a good day to learn.
TwoProc
Honored Contributor

Re: Query to help determine what PGA sizing might be?

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!
We are the people our parents warned us about --Jimmy Buffett