How to workaround the internal limitation of a single work area

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 12.1.0.2
  • Oracle Linux Server release 6.5

SQL> SELECT
a.ksppinm Param ,
b.ksppstvl SessionVal
FROM
x$ksppi a ,
x$ksppcv b ,
x$ksppsv c
WHERE
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’);

PARAM                     SESSIONVAL
————————- ————————-
pga_aggregate_target      2147483648
_pga_max_size             429486080
_smm_max_size             209710
_smm_px_max_size          1048576

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;
Session altered.

Like you observed we can not use a workarea bigger than 2G per process .

Workaround :

  • 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 .

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s