Initialization parameters SORT_AREA_SIZE and HASH_AREA_SIZE are used to specify the maximum amount of memory for each session in Oracle8i or earlier.
In Oracle9i, the following parameters specify the maximum amount of the PGA memory available to all server processes for every instance.
(PGA: Program Global Area)
PGA_AGGREGATE_TARGET
Specify the total amount of PGA memory available to instance. You can dynamically change the parameter at instance level. Set the parameter to any value between 10M and 4096G-1 bytes.
WORKAREA_SIZE_POLICY
Specify if the size of the SQL workarea should be tuned automatically or manually.
AUTO: tune automatically
MANUAL: tune manually
When WORKAREA_SIZE_POLICY is set to MANUAL, the maximum amount of memory is defined by the parameter such as HASH_AREA_SIZE. When WORKAREA_SIZE_POLICY is set to AUTO, the maximum amount of memory is defined automatically and the parameters such as HASH_AREA_SIZE are ignored.
If PGA_AGGREGATE_TARGET is set, WORKAREA_SIZE_POLICY will be set to AUTO by default.
How to determine PGA_AGGREGATE_TARGET.
The following formula is used as a guideline.
OLTP systems:
PGA_AGGREGATE_TARGET=(total actual memory*80%)*20%
DSS systems:
PGA_AGGREGATE_TARGET=(total actual memory*80%)*50%
PGA_AGGREGATE_TARGET of DSS systems is determined larger than that of OLTP systems because DSS systems normally access large amount of data by sorting with ORDER BY or GROUP BY.
There three types of SQL workarea executions as follows:
optimal: workarea execution of which all processes such as sort are executed on memory
onepass: workarea execution that requires the minimum writes to a disk
multipass: workarea execution that requires heavy writes to a disk because SQL workarea is too low
The following are standard tuning goal:
workarea execution - optimal >= 90%
workarea execution - multiplass = 0%
Most of the workarea executions are executed in optimal mode to reduce workarea execution - onepass.
The following query enables you to monitor the status of PGA memory in V$STSSTAT.
select name
,value
,100*(value/
decode((select sum(value) from v$sysstat where name like 'workarea
exec%'),0,null,(select sum(value) from v$sysstat where name like 'workarea
exec%'))) pct
from v$sysstat
where name like 'workarea exec%'
/
NAME VALUE PCT
----------------------------------------------------------------------
workarea executions - optimal 1529 100
workarea executions - onepass 0 0
workarea executions - multipass 0 0
|
If VALUE of workarea execution - multiplass is not 0% or VALUE of workarea executions - onepass exceeds 10%, you need to increase PGA_AGGREGATE_TARGET. If VALUE of workarea executions - optimal is 100%, you may reduce PGA_AGGREGATE_TARGET.
댓글 없음:
댓글 쓰기