| |
 |
|
Oracle Tips by Burleson |
The Oracle
v$pgastat View
The v$pgastat
view provides instance-level summary statistics on the PGA usage and
the automatic memory manager. The following script provides excellent
overall usage statistics for all Oracle9i connections:
See
code depot for full scripts
select
name,
value
from
v$pgastat
;
The output of this
query might look like the following:
NAME
VALUE
------------------------------------------------------ ----------
aggregate PGA auto target
736,052,224
global memory bound
21,200
total expected memory
141,144
total PGA inuse
22,234,736
total PGA allocated
55,327,872
maximum PGA allocated
23,970,624
total PGA used for auto workareas
262,144
maximum PGA used for auto workareas
7,333,032
total PGA used for manual workareas
0
maximum PGA used for manual workareas
0
estimated PGA memory for optimal
141,395
maximum PGA memory for optimal
500,123,520
estimated PGA memory for one-pass
534,144
maximum PGA memory for one-pass
52,123,520
In the preceding
display from v$pgastat, we see the following statistics:
-
Aggregate PGA auto target - This column gives
the total amount of available memory for Oracle9i connections. As we
have already noted, this value is derived from the value on the
Oracle parameter pga_aggregate_target.
-
Global memory bound - This statistic measures
the maximum size of a work area, and Oracle recommends that whenever
this statistic drops below 1 megabyte, you should increase the value
of the pga_aggregate_target parameter.
-
Total PGA allocated - This statistic displays
the high-water mark of all PGA memory usage on the database. You
should see this value approach the value of pga_aggregate_target
as usage increases.
-
Total PGA used for auto workareas - This
statistic monitors RAM consumption of all connections running in
automatic memory mode. Remember, Oracle does not allow all internal
processes to use the automatic memory feature. For example, Java and
PL/SQL will allocate RAM memory, and it will not be counted in the
total PGA statistic. Hence, you can subtract this value from the
total PGA allocated to see the amount of memory used by connections
and the RAM memory consumed by Java and PL/SQL.
-
Estimated PGA memory for optimal/one-pass -
This statistic estimates how much memory is required to execute all
task connections demanded by RAM in optimal mode. Remember, when
Oracle9i experiences a memory shortage, the DBA will invoke the
multi-pass operation to attempt to locate recently-freed RAM memory.
This statistic is critical for monitoring RAM consumption in
Oracle9i, and most Oracle DBAs will increase pga_aggregate_target
to this value.
 |
For more details and scripts, see my new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
|
|
For more details and scripts, see my new book "
Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot. |
|
