PGA – How Oracle allocate memory to the process

---

 

In modern Oracle database none is allocating memory to the process manual, almost everybody uses automatic workload allocation, by setting pga_aggregate_target.

But how does Oracle split that memory to processes?

Well, as we all know none of the process can have all of pga_aggregate_target memory allocated to himself.

 

HOW IT`S DONE?

---

 

First take a look at how we can measure it.

All information about work area, max memory allocate to the process and max memory allocate to parallel process can be found in x$ views.

These are hidden parameter which can be modify (but on your own risk).

Following SQL show you these parameter and their values.

 

SELECT ksppinm as param_name, ksppstvl as param_value
 FROM x$ksppi JOIN x$ksppcv USING(indx)
 WHERE ksppinm IN(
  'pga_aggregate_target',
  '_pga_max_size',
  '_smm_max_size',
  '_smm_px_max_size',
  '_pga_aggregate_target'
 )

• pga_aggregate_target is self explanatory
• _pga_max_size – max size of memory allocated to single process
• _smm_max_size – max work area for serial
• _smm_px_max_size – max work area for parallel

 

To automate my tests I prepare a shell script:

#!/bin/bash
 echo > pga_agg_tun.dat

check_and_save(){
 echo $1
 zm=`sqlplus -silent / as sysdba <<EOF
 set feedback off heading off echo off
 alter system set pga_aggregate_target=$1M;
 /
 SELECT LISTAGG(ksppinm||','||
 CASE WHEN ksppinm LIKE '%smm%' THEN ksppstvl/1024 ELSE ksppstvl/1024/1024 END,
 ','
 ) WITHIN GROUP(ORDER BY ksppinm DESC ) AS mb
 FROM x\\$ksppi JOIN x\\$ksppcv USING(indx)
 WHERE ksppinm IN(
 'pga_aggregate_target',
 '_pga_max_size',
 '_smm_max_size',
 '_smm_px_max_size',
 '_pga_aggregate_target'
 )
 /
 exit;
 EOF`

echo $zm
 `echo $zm >> pga_agg_tun.dat`
 }

for i in `seq 10 50 2000`
 do
 check_and_save $i
 done

I take data for pga_aggregate_target between 10M and 2G, increment by 50M.

So finally, below picture is effect of my work.

 

 

 

WHAT DOES IT MEAN?

---

 

As we can see as memory for PGA increase parameter changes, some constantly other not.

For example memory allocated to parallel process is always 50% of total memory for PGA (red line), of course it`s worth mention that memory for single process is calculated by formula:

_smm_px_max_size/DOB

Max memory allocated to single process is constant to 200M until pga_aggregate_target reach 1G, after that point each process get 20% of PGA.

Single work area act kind of weird, it about 20% of total PGA until 500M, then it drops constantly about 2% by 50M to reach 10% of PGA when we bump into 950MB. After that point it`s more or less 10% of total PGA.

 

IN CONCLUSION

---

 

Be carefully when you change your PGA, because some changes aren`t as efficient as others, for example if your PGA is about 300M and you have problems with low memory allocated to single process, then increasing PGA to 900M would not help you, because memory for single process is about 200M constant until 1GB.

Of course you can also modify hidden parameters 🙂