| |
 |
|
Oracle Tips by Burleson |
Oracle
Sorting Monitoring
As we
have noted, a serious problem in Oracle8i was the requirement that all
dedicated connections use a one-size-fits-all sort_area_size.
Oracle9i now has the option of running automatic PGA memory
management, and Oracle has introduced a new Oracle parameter called
pga_aggregate_target.
When
the pga_aggregate_target parameter is set and you are using
dedicated Oracle connections, Oracle9i will ignore all of the PGA
parameters in the Oracle file, including sort_area_size,
hash_area_size, and sort_area_retained_size. On a dedicated
Oracle server, Oracle recommends that the value of
pga_aggregate_target be set to the amount of remaining memory
(less a 10 percent overhead for other UNIX tasks) on the UNIX server
after the instance has been started.
Once
the pga_aggregate_target has been set, Oracle will
automatically manage PGA memory allocation based upon the individual
needs of each Oracle connection. Oracle9i allows the
pga_aggregate_target parameter to be modified at the instance
level with the alter system command, thereby allowing the DBA to
dynamically adjust the total RAM region available to Oracle9i.
Oracle9i also introduces a new parameter called
workarea_size_policy. When this parameter is set to automatic, all
Oracle connections will benefit from the shared PGA memory. When
workarea_size_policy is set to manual, connections will allocate
memory according to the values for the sort_area_size
parameter. Under the automatic mode, Oracle tries to maximize the
number of work areas that are using optimal memory and uses one-pass
memory for the others.
 |
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. |
|
