When using automatic PGA management mode,as you may know there is limitation on the size of the assigned memory indicated by :
- _pga_max_size Maximum size of the PGA memory for one process
- _smm_max_size maximum work area size per porcess
- _smm_px_max_size maximum work area size per parallel query
The calculation of this parameters depend on the value set for “pga_aggregate_target”. Also the memory bound is adjusted based on the workload.For more info on the algorithm please check this white paper .
Here is a quick view :
- Oracle 184.108.40.206
- Oracle Linux Server release 6.5
a.ksppinm Param ,
x$ksppi a ,
x$ksppcv b ,
a.indx = b.indx AND
a.indx = c.indx AND
a.ksppinm in (‘_pga_max_size’,’_smm_max_size’,’_smm_px_max_size’,’pga_aggregate_target’);
So here we have set target pga_aggregate_target=2G but the max workarea we can use for a single process is “_smm_max_size=200M” = 10%*pga_aggregate_target
You can check how the memory bound is adjusted using the following query when there is many active workarea:
select name,value from v$pgastat where name in (‘global memory bound’,’recompute count (total)’);
There is different way to super size a WorkArea discussed in many other blogs like changing hidden parameters( check : Doc ID 453540.1 also there is a known bug 17951233 when setting _pga_max_size > 2Gb) or switch to manual memory management so i will not talk about that here.What we will see here is some workaround to the internal limitation of a single workarea size limited to 2GB.
alter session set sort_area_size=2147483648;
Error at line 1
ORA-02017: valeur entière obligatoire
alter session set sort_area_size=2147483647;
Like you observed we can not use a workarea bigger than 2G per process .
- Using a high DOP will maximize the memory use up to the parameter “_smm_px_max_size”.
- Improving temporary tablespace performance by using an in-memory filesystem like tmpfs.(But be careful you must recreate the tablespace after instance reboot)
- Improving temporary tablespace performance by using SAN LUN with write-back cache.
For more info and example please take a look at Alex Fatkulin presentation .