Question: What do
I do to see if my Oracle server is RAM constrained? I
fear that I am RAM bound on my Oracle server but I am not
sure how to detect it.
Answer: It is impossible for an
Oracle database to be RAM bound unless the instance is
misconfigured by the DBA. The DBA controls the amount
of RAM on an instance by using the following SGA pool
parameters.
You can tell when an Oracle server is RAM bound only with
these conditions:
- Server RAM bound: The RAM
page-in (pi) will increase along with the scan-rate (sr)
See
Server_RAM for details.
- PGA advisory RAM bound: When
you see multi-pass executions in the
v$_pga_advice.htm utility. This appears in the
AWR report.
In a properly configured database server all of the T2
RAM will be allocated to the database instances and the free
RAM will show optimal as 5% of less of free RAM. All
RAM is fixed in size and allocated at instance startup time.
The only variable RAM in Oracle is for the PGA, and it is
constrained by the _pga_max_size undocumented parameter.
Oracle 11g and beyond:
- memory_target
- memory_max_target
Oracle 10g (not using AMM)
- sga_target
- sga_memory_max
Oracle 9i and prior: (unless not using AMM)
- pga_aggregate_target
- sort_area_size
- pga_aggregate_target
See
details here on using the PGA and SGA parameters.
you can adjust your PGA regions to allow
for system-specific sorting and hash joins. Please read
these
http://www.dba-oracle.com/art_so_undocumented_pga_parameters.htm
important notes on PGA behavior.
The moral of the story is that the DBA
controls the amount of RAM used by Oracle, and this amount
of RAM (SGA and PGA) can be reduced as much as desired,
knowing that as the available RAM decreases, the amount of
physical I/O increases.
You can also increase your pga_aggregate_target above
the default 200 megabyte setting by setting the
hidden _pga_max_size parameter.
- _pga_max_size = 1000m
- _smm_px_max_size = 333m
With pga_aggregate_target and _pga_max_size
hidden parameter set to 1 meg we see a 5x improvement
over the default for parallel queries and sorts:
- A RAM sort or hash join may now
have up to 50 megabytes (5% of pga_aggegate_target).
- Parallel queries may now have up
to 330 megabytes of RAM (30% of pga_aggegate_target),
such that a DEGREE=4 parallel query would have 83
megabytes (333 meg/4).
