Can you give specific sizes for the PGA in Oracle? Please explain.
Oracle uses many parameters to control the sizing of specific PGA structures. These parameters include SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, and CREATE_BITMAP_AREA_SIZE. Collectively, these are known as the *_AREA_SIZE parameters. Before Oracle 9i, the DBA would individually size these parameters.
Oracle 9i introduced a more automated way of dealing with sizing the PGA. The new PGA_AGGREGATE_TARGET parameter defines the total amount of PGA for all of your connected sessions. In addition, the WORKAREA_SIZE_POLICY parameter must be set to AUTO for this to work.
So the big question then is what value should PGA_AGGREGATE_TARGET be set at? Oracle does give a rule of thumb for this. For an OLTP system, Oracle recommends setting PGA_AGGREGATE_TARGET to 16% of your server's physical memory. For a DSS system, Oracle recommends 40% of your physical memory. This is just a starting place. You'll want to fine tune this parameter for you database's specific workload.
The first place to look is V$SYSTAT for the "work area" statistics. You'll want to size PGA_AGGREGATE_TARGET so that the 'workarea executions - multipass' is non-zero. Ideally, the 'workarea executions - onepass' should be non-zero as well. Also, you'll want to look at V$PGASTAT and pay close attention to the over allocation count. Increase PGA_AGGREGATE_TARGET if this value is non-zero. Finally, the V$PGA_TARGET_ADVICE view will give you advice on an optimal setting for this parameter.
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our applications, PL/SQL, database administration and data warehousing gurus are waiting to answer your toughest questions.