Over Allocation Of Resources By Setting High Values For PGA And SGA.

From Metalink

Applies to:

Oracle Server – Enterprise Edition – Version: 8.1.7.4 to 11.2.0.2 – Release: 8.1.7 to 11.2
Information in this document applies to any platform.

Goal

What could over allocating resources cause ? i.e. very high settings for SGA and PGA that Oracle doesn’t ACTUALLY need.

Solution

Let’s say, if you have a PROCESSES parameter of 200 and a PGA_AGGREGATE_TARGET of 10 GBs, then this could be over allocation for the system resources that might cause unexplainable high memory demand of Oracle for the server’s physical memory, when less than 200 sessions are actually connected to the database and each one is actually not using more than 2 MBs (in most cases).

It’s usually recommended to set the PGA_AGGREGATE_TARGET parameter to double the number of
PROCESSES. i.e. If PROCESSES is set to 200, then set PGA_AGGREGATE_TARGET to 400 M.

This could be the same case with SGA_TARGET high values for an Oracle database that doesn’t require THAT high memory usage. You have a RAM size of 48 GBs and you give the SGA_TARGET a value of 10 GBs but Oracle actually doesn’t need it and the resources get allocated at some point of time causing high demand to the physical memory.

All the above could lead to high memory usage, and accordingly high system Swapping.

Please make sure to size your PGA and SGA only according to the needs and the load of your database/server.

© 2008 – 2011, www.oracledatabase12g.com. 版权所有.文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.

相关文章 | Related posts:

1. PGA Usage Larger than PGA_AGGREGATE_TARGET setting?
2. Setting an Oracle event:The structure of the trace syntax