Help: Strange PGA Behavior

[Former Member]

hi,

during R/3 4.7 Export, PGA is very stranger. Could somebody give me help?

DB version, oracle 9.2.0.4 Enterprise, OS, SunOS 5.8（machine is old, disk has poor performance）

key parameters:

pgamax_size=1073741824

db_cache_size=1000000000

pga_aggregate_target=2000000000

workarea_size_policy=auto

PGA statistics information:

SQL> select * from v$pgastat;

NAME VALUE UNIT

aggregate PGA target parameter 2000000000 bytes

aggregate PGA auto target 0 bytes

global memory bound 0 bytes

total PGA inuse 23502848 bytes

total PGA allocated 58076160 bytes

maximum PGA allocated 59555840 bytes

total freeable PGA memory 2883584 bytes

PGA memory freed back to OS 327680 bytes

total PGA used for auto workareas 1324032 bytes

maximum PGA used for auto workareas 1726464 bytes

total PGA used for manual workareas 0 bytes

NAME VALUE UNIT

maximum PGA used for manual workareas 0 bytes

over allocation count 0

bytes processed 1.6069E+10 bytes

extra bytes read/written 6.3215E+10 bytes

cache hit percentage 20.26 percent

16 rows selected.

Sort Location:

SQL> select name, value from v$sysstat where name in ('sorts (memory)', 'sorts (disk)');

NAME VALUE

sorts (memory) 13958

sorts (disk) 22

Qestions:

1. why "aggregate PGA auto target" is 0 bytes?

2. why only a small part of PGA is used, but some sorts are performed in disk?

Any tip is appreciated.

Best Regard,

Rongfeng

Edited by: Rongfeng Shi on Aug 22, 2008 10:39 AM

[stefan_koehler · Accepted solution]

Hello Rongfeng,

you have selected a very hard topic but let's start.

At first the sapnote #936441 which is pointing you to the parameter pgamax_size is telling the "half-truth".

If everyone asks something about that topic i always refer to the "ADVANCED MANAGEMENT OF WORKING AREAS IN ORACLE 9I/10G" from Joze Senegacnik. This document is very detailed and has many explanations.

http://tonguc.yilmaz.googlepages.com/JozeSenegacnik-PGAMemoryManagementvO.zip

> 1. why "aggregate PGA auto target" is 0 bytes?

Check the statement from the paper: "aggregate PGA auto target u2013 SQL memory target u2013 P_A_T minus used memory for other categories in PGAs"

> 2. why only a small part of PGA is used, but some sorts are performed in disk?

Because your PGA is "only" 1.8 GB and you have the following limitations:

> SMMMAX_SIZE

> This parameter defines the maximum work area size in auto mode (serial) and defaults to 50% of pgamax_size. In Oracle 9i/10gR1 the maximum value is 0.1GB (100MB), while in 10gR2 it can go up to 0.25GB as we will see later.After one of the instance restarts I found that the pgamax_size and smmmax_size had even bigger values with P_A_T set to 4GB.

Regards

Stefan

[Former Member · Accepted solution]

Hello Stefan,

thank you very much for your explanation and sharing the great documentation.

Regards,

Rongfeng