Oracle tuning PGA_AGGREGATE_TARGET

chuntse
chuntse used Ask the Experts™
on
Hello,

in reponse to another question user posted:

Ensure big separate temporary tablespace and increase Sort RAM
setting    (example only!)
PGA_AGGREGATE_TARGET= 400M
according the free RAM

PGA_AGGREGATE_TARGET specifies the target aggregate PGA memory available to all server processes attached to the instance.

Setting PGA_AGGREGATE_TARGET to a nonzero value has the effect of automatically setting the WORKAREA_SIZE_POLICY parameter to AUTO. This means that SQL working areas used by memory-intensive SQL operators (such as sort, group-by, hash-join, bitmap merge, and bitmap create) will be automatically sized. A nonzero value for this parameter is the default since, unless you specify otherwise, Oracle sets it to 20% of the SGA or 10 MB, whichever is greater.

Setting PGA_AGGREGATE_TARGET to 0 automatically sets the WORKAREA_SIZE_POLICY parameter to MANUAL. This means that SQL workareas are sized using the *_AREA_SIZE parameters.

Oracle attempts to keep the amount of private memory below the target specified by this parameter by adapting the size of the work areas to private memory. When increasing the value of this parameter, you indirectly increase the memory allotted to work areas. Consequently, more memory-intensive operations are able to run fully in memory and less will work their way over to disk.

When setting this parameter, you should examine the total memory on your system that is available to the Oracle instance and subtract the SGA. You can assign the remaining memory to PGA_AGGREGATE_TARGET.

I have no idea what this really means. Here is output from v$pgastat

v$pgastat
order by
value desc;  2    3    4    5

NAME                                            VALUE
---------------------------------------- ------------
bytes processed                          ############
PGA memory freed back to OS              ############
extra bytes read/written                 ############
aggregate PGA target parameter            848,297,984
aggregate PGA auto target                 663,395,328
maximum PGA allocated                     622,045,184
total PGA allocated                       225,905,664
maximum PGA used for auto workareas       176,314,368
total PGA inuse                           111,349,760
global memory bound                       104,857,600
total freeable PGA memory                  66,715,648

NAME                                            VALUE
---------------------------------------- ------------
maximum PGA used for manual workareas       3,225,600
recompute count (total)                     1,100,523
max processes count                               328
process count                                     130
cache hit percentage                               98
total PGA used for manual workareas                 0
over allocation count                               0
total PGA used for auto workareas                   0

19 rows selected.

In simple terms what does this tell me and do I need to tune the PGA_AGGREGATE_TARGET in order for Oracle to run better, especially when enabling foreign keys?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008
Commented:
Oracle uses in the past sort_area_size for sort operation in the RAM.
Now Oracle claims that this paremeter is obsolete and recommends to use PGA_AGGREGATE_TARGET

When you enable foreign keys you will have no troubles.
The problem arise when you create index on foreign keys (in order do not to lock the primary table).
But index creations possibly will use some sort operation to create the B*Tree.
You need for this RAM. RAM will be freed after the instance creation.
Top Expert 2009
Commented:
When Oracle builds an index, it first sorts all of the keys in the temp tablespace (or if PGA area is big enough it it will sort in memory). It has to have enough space in the PGA sort area or the temp tablespace to completely store all the keys for the single index during the creation phase. It then uses those keys to build the target index in the index tablespace.

Author

Commented:
OK, thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial