Tune an instance

Go back to OCM 12c Preparation Project 

Hands On Lab – Index

[1] Wait Events Statistics
[2] Tuning Instance Recovery Performance: Fast-Start Fault Recovery
[3] Tuning Database Memory

[1] Wait Events Statistics

Table of Wait Events and Potential Causes

1. buffer busy waits

General Area : Buffer cache, DBWR

This wait indicates that there are some buffers in the buffer cache that 
multiple processes are attempting to access concurrently. 
Query V$WAITSTAT for the wait statistics for each class of buffer. 
Common buffer classes that have buffer busy waits include data block, 
segment header, undo header, and undo block.

Check the following V$SESSION_WAIT parameter columns:

P1: File ID
P2: Block ID
P3: Class ID

Causes
To determine the possible causes, first query V$SESSION to identify the 
value of ROW_WAIT_OBJ# when the session waits for buffer busy waits. 

For example:

SELECT row_wait_obj# 
FROM V$SESSION 
WHERE EVENT = 'buffer busy waits';

To identify the object and object type contended for, query DBA_OBJECTS 
using the value for ROW_WAIT_OBJ# that is returned from V$SESSION. 

For example:

SELECT owner, object_name, subobject_name, object_type
FROM DBA_OBJECTS
WHERE data_object_id = &row_wait_obj;

Actions
The action required depends on the class of block contended for and 
the actual segment.

Segment Header
If the contention is on the segment header, then this is most likely 
free list contention.

Automatic segment-space management in locally managed tablespaces eliminates 
the need to specify the PCTUSED, FREELISTS, and FREELIST GROUPS parameters. 
If possible, switch from manual space management to automatic 
segment-space management (ASSM).

Data Block

If the contention is on tables or indexes (not the segment header):
Check for right-hand indexes. These are indexes that are inserted into 
at the same point by many processes. 
For example, those that use sequence number generators for the key values.

Consider using ASSM, global hash partitioned indexes, or 
increasing free lists to avoid multiple processes attempting to insert 
into the same block.

Undo Header

For contention on rollback segment header:
If you are not using automatic undo management, 
then add more rollback segments.


Undo Block

For contention on rollback segment block:
If you are not using automatic undo management, 
then consider making rollback segment sizes larger.

2. db file scattered read

Check the following V$SESSION_WAIT parameter columns:

P1: The absolute file number
P2: The block being read
P3: The number of blocks (should be greater than 1)

3. db file sequential read

Check the following V$SESSION_WAIT parameter columns:

P1: The absolute file number
P2: The block being read
P3: The number of blocks (should be 1)

4. direct path read and direct path read temp

Check the following V$SESSION_WAIT parameter columns:

P1: File_id for the read call
P2: Start block_id for the read call
P3: Number of blocks in the read call

5. direct path write and direct path write temp

Check the following V$SESSION_WAIT parameter columns:

P1: File_id for the write call
P2: Start block_id for the write call
P3: Number of blocks in the write call

6. enqueue (enq:) waits
-> Enqueues are locks that coordinate access to database resources. 
-> This event indicates that the session is waiting for a lock that 
   is held by another session.

enq: TX - allocate ITL entry
enq: TX - contention
enq: TX - index contention
enq: TX - row lock contention

The V$EVENT_NAME view provides a complete list of all the enq: wait events.

You can check the following V$SESSION_WAIT parameter columns for 
additional information:

P1: Lock TYPE (or name) and MODE
P2: Resource identifier ID1 for the lock
P3: Resource identifier ID2 for the lock

#Finding Locks and Lock Holders

If there are enqueue waits, you can see these using the following statement:

SELECT * FROM V$LOCK WHERE request > 0;

To show only holders and waiters for locks being waited on, use the following:

SELECT DECODE(request,0,'Holder: ','Waiter: ') || 
sid sess, id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK 
WHERE request > 0)
ORDER BY id1, request;

-> The HW enqueue is used to serialize the allocation of space beyond 
the high water mark of a segment. If this is a point of contention for 
an object, then manual allocation of extents solves the problem.

-> The most common reason for waits on TM locks tend to involve foreign key 
constraints where the constrained columns are not indexed. 
Index the foreign key columns to avoid this problem.

These are acquired exclusive when a transaction initiates its first change 
and held until the transaction does a COMMIT or ROLLBACK.

1. enq: TX - row lock contention.

1.1 Waits for TX in mode 6: 
-> occurs when a session is waiting for a row level lock that is held by 
another session. This occurs when one user is updating or deleting a row, 
which another session wants to update or delete. This type of TX enqueue 
wait corresponds to the wait event enq: TX - row lock contention.
-> The solution is to have the first session holding the lock perform a 
COMMIT or ROLLBACK.

1.2 Waits for TX in mode 4

-> can also occur if a session is waiting due to potential duplicates in 
UNIQUE index. If two sessions try to insert the same key value the 
second session has to wait to see if an ORA-0001 should be raised or not. 
This type of TX enqueue wait corresponds to the wait event 
enq: TX - row lock contention.

-> The solution is to have the first session holding the lock perform 
a COMMIT or ROLLBACK.

-> can also occur if the session is waiting due to shared bitmap index 
fragment. Bitmap indexes index key values and a range of rowids. 
Each entry in a bitmap index can cover many rows in the actual table. 
If two sessions want to update rows covered by the 
same bitmap index fragment, then the second session waits for the first 
transaction to either COMMIT or ROLLBACK by waiting 
for the TX lock in mode 4. This type of TX enqueue wait corresponds to 
the wait event enq: TX - row lock contention.

-> can also occur waiting for a PREPARED transaction.

2. enq: TX - index contention.
-> can occur when a transaction inserting a row in an index has to wait 
for the end of an index block split being done by another transaction. 
This type of TX enqueue wait corresponds to the wait event 
enq: TX - index contention.

3. enq: TX - allocate ITL entry.

-> Waits for TX in mode 4 can occur if the session is waiting for an 
ITL (interested transaction list) slot in a block. 
This happens when the session wants to lock a row in the block but one 
or more other sessions have rows locked in the same block, and 
there is no free ITL slot in the block. Usually, Oracle Database 
dynamically adds another ITL slot. This may not be possible if there 
is insufficient free space in the block to add an ITL. If so, the session 
waits for a slot with a TX enqueue in mode 4. 
This type of TX enqueue wait corresponds to the wait event 
enq: TX - allocate ITL entry.

7. free buffer waits
8. latch events

[2]. Tuning Instance Recovery Performance: Fast-Start Fault Recovery

This section describes instance recovery, and how Oracle's Fast-Start 
Fault Recovery improves availability in the event of a crash or instance 
failure. It also offers guidelines for tuning the time required to perform 
crash and instance recovery.

1. About Instance Recovery

If a single instance database crashes or if all instances of an Oracle RAC 
configuration crash, then Oracle Database performs crash recovery at the 
next startup. If one or more instances of an Oracle RAC configuration crash, 
then a surviving instance performs instance recovery automatically. 
Instance and crash recovery occur in two steps: cache recovery followed 
by transaction recovery.

-> Cache Recovery (Rolling Forward)
-> Transaction Recovery (Rolling Back)

Fast Cache Recovery Tradeoffs

To minimize the duration of cache recovery, you must force Oracle Database 
to checkpoint often, thus keeping the number of redo log records to be 
applied during recovery to a minimum. However, in a high-update system, 
frequent checkpointing increases the overhead for normal database operations.

2. Configuring the Duration of Cache Recovery: FAST_START_MTTR_TARGET

With the Fast-Start Fault Recovery feature, the FAST_START_MTTR_TARGET 
initialization parameter simplifies the configuration of recovery time 
from instance or system failure.

-> FAST_START_MTTR_TARGET specifies a target for the expected mean time 
to recover (MTTR), that is, the time (in seconds) that it should take to 
start up the instance and perform cache recovery.

-> After FAST_START_MTTR_TARGET is set, the database manages incremental 
checkpoint writes in an attempt to meet that target.

Practical Values for FAST_START_MTTR_TARGET

-> The maximum value for FAST_START_MTTR_TARGET is 3600 seconds (one hour). 
If you set the value to more than 3600, then Oracle Database rounds it to 3600.

SQL> ALTER SYSTEM SET FAST_START_MTTR_TARGET=30;

The MTTR target that your database can achieve given the current value 
of FAST_START_MTTR_TARGET is called the effective MTTR target. You can 
view your current effective MTTR by viewing the TARGET_MTTR column of the 
V$INSTANCE_RECOVERY view.

Determine the Practical Range for FAST_START_MTTR_TARGET
-> After calibration, you can perform tests to determine the practical 
range for FAST_START_MTTR_TARGET for your database.

-> Monitoring Cache Recovery with V$INSTANCE_RECOVERY
TARGET_MTTR
Effective MTTR target in seconds. This field is 0 if 
FAST_START_MTTR_TARGET is not specified.

ESTIMATED_MTTR
The current estimated MTTR in seconds, based on the current number of 
dirty buffers and log blocks. This field is always calculated, whether 
FAST_START_MTTR_TARGET is specified.

As part of the ongoing monitoring of your database, you can periodically 
compare V$INSTANCE_RECOVERY.TARGET_MTTR to your FAST_START_MTTR_TARGET. 
The two values should generally be the same if the FAST_START_MTTR_TARGET 
value is in the practical range. If TARGET_MTTR is consistently longer than
FAST_START_MTTR_TARGET, then set FAST_START_MTTR_TARGET to a value no less 
than TARGET_MTTR. If TARGET_MTTR is consistently shorter, then set 
FAST_START_MTTR_TARGET to a value no greater than TARGET_MTTR.


3. Tuning FAST_START_MTTR_TARGET and Using MTTR Advisor
To determine the appropriate value for FAST_START_MTTR_TARGET for your 
database, use the following four step process:

-> Calibrate the FAST_START_MTTR_TARGET

To effectively calibrate FAST_START_MTTR_TARGET, ensure that you run the 
typical workload of the system for long enough,and perform several instance 
recoveries to ensure that the time to read a redo block and the time to 
read or write a data block during recovery are recorded accurately.

-> Determine the Practical Range for FAST_START_MTTR_TARGET

After calibration, you can perform tests to determine the practical range 
for FAST_START_MTTR_TARGET for your database.

1. Determining Lower Bound for FAST_START_MTTR_TARGET: Scenario

To determine the lower bound of the practical range, 
set FAST_START_MTTR_TARGET to 1, and start up your database. 
Then check the value of V$INSTANCE_RECOVERY.TARGET_MTTR, and use this 
value as a good lower bound for FAST_START_MTTR_TARGET. Database startup 
time, rather than cache recovery time, is usually the dominant 
factor in determining this limit.

For example, set the FAST_START_MTTR_TARGET to 1:

SQL> ALTER SYSTEM SET FAST_START_MTTR_TARGET=1;

Then, execute the following query immediately after opening the database:

SQL> SELECT TARGET_MTTR, ESTIMATED_MTTR 
FROM V$INSTANCE_RECOVERY;
Oracle Database responds with the following:

TARGET_MTTR ESTIMATED_MTTR 
18 15


Assume that you query V$INSTANCE_RECOVERY immediately after a period of 
heavy update activity in the database. 
Oracle Database responds with the following:

TARGET_MTTR ESTIMATED_MTTR 
18 30

Now the effective MTTR target is still 18 seconds, and the estimated MTTR 
(if a crash happened at that moment) is 30 seconds. This is an acceptable 
result. This means that some checkpoints writes might not have finished 
yet, so the buffer cache contains more dirty buffers than targeted.

Now wait for sixty seconds and reissue the query to V$INSTANCE_RECOVERY. 
Oracle Database responds with the following:

TARGET_MTTR ESTIMATED_MTTR 
18 25

The estimated MTTR at this time has dropped to 25 seconds, because some 
of the dirty buffers have been written out during this period

2. Determining Upper Bound for FAST_START_MTTR_TARGET

-> To determine the upper bound of the practical range, 
set FAST_START_MTTR_TARGET to 3600, and operate your database 
under a typical workload for a while. Then check the value of 
V$INSTANCE_RECOVERY.TARGET_MTTR. This value is a good 
upper bound for FAST_START_MTTR_TARGET.

3. Selecting Preliminary Value for FAST_START_MTTR_TARGET

-> After you have determined the practical bounds for the 
FAST_START_MTTR_TARGET parameter, select a preliminary value for 
the parameter. Choose a higher value within the practical range 
if your concern is with database performance, and a 
lower value within the practical range if your priority is 
shorter recovery times.

SQL> ALTER SYSTEM SET FAST_START_MTTR_TARGET=30;
You might then go on to use the MTTR Advisor to determine an optimal value.

-> Evaluate Different Target Values with MTTR Advisor

After you have selected a preliminary value for FAST_START_MTTR_TARGET, 
you can use MTTR Advisor to evaluate the effect of
different FAST_START_MTTR_TARGET settings on system performance, 
compared to your chosen setting.

Enabling MTTR Advisor

To enable MTTR Advisor, set the two initialization parameters 
STATISTICS_LEVEL and FAST_START_MTTR_TARGET.
STATISTICS_LEVEL governs whether all advisors are enabled and is not 
specific to MTTR Advisor. Ensure that it is set to TYPICAL or ALL. 
Then, when FAST_START_MTTR_TARGET is set to a nonzero value, the MTTR Advisor 
is enabled.

Using MTTR Advisor

-> After enabling MTTR Advisor, run a typical database workload for a while. 
When MTTR Advisor is ON, the database simulates checkpoint queue behavior 
under the current value of FAST_START_MTTR_TARGET, and up to four other 
different MTTR settings within the range of valid FAST_START_MTTR_TARGET 
values. 
(The database will in this case determine the valid range for 
FAST_START_MTTR_TARGET itself before testing different values in the range.)

Viewing MTTR Advisor Results: V$MTTR_TARGET_ADVICE

The dynamic performance view V$MTTR_TARGET_ADVICE lets you view statistics 
or advisories collected by MTTR Advisor.

The database populates V$MTTR_TARGET_ADVICE with advice about the effects 
of each of the FAST_START_MTTR_TARGET settings for your database. 
For each possible value of FAST_START_MTTR_TARGET, the row contains details 
about how many cache writes would be performed under the workload tested 
for that value of FAST_START_MTTR_TARGET.

Specifically, each row contains information about cache writes, total 
physical writes (including direct writes), and total I/O (including reads) 
for that value of FAST_START_MTTR_TARGET, expressed both as a total number 
of operations and a ratio compared to the operations under your chosen 
FAST_START_MTTR_TARGET value. For instance,a ratio of 1.2 indicates 20%
more cache writes.

Knowing the effect of different FAST_START_MTTR_TARGET settings on cache 
write activity and other I/O enables you to decide better which 
FAST_START_MTTR_TARGET value best fits your recovery and performance needs.

If MTTR Advisor is currently on, then V$MTTR_TARGET_ADVICE shows the 
Advisor information collected. If MTTR Advisor is currently OFF, then the 
view shows information collected the last time MTTR Advisor was ON since 
database startup, if any. If the database has been restarted since the 
last time the MTTR Advisor was used, or if it has never been used, the 
view will not show any rows.

-> Determine the Optimal Size for Redo Logs

1. You can use the V$INSTANCE_RECOVERY view column OPTIMAL_LOGFILE_SIZE 
to determine the size of your online redo logs.
2. This field shows the redo log file size in megabytes that is considered 
optimal based on the current setting of FAST_START_MTTR_TARGET.
3. If this field consistently shows a value greater than the size of your 
smallest online log, then you should configure all your online logs to be 
at least this size.

Note, however, that the redo log file size affects the MTTR. In some cases, 
you may be able to refine your choice of the optimal FAST_START_MTTR_TARGET 
value by re-running the MTTR Advisor with your suggested optimal log file size.

[3] Tuning Database Memory

Oracle Database provides the following methods to manage database memory:

3.1 Automatic Memory Management

Management of the shared global area (SGA) and program global area 
(instance PGA) memory is handled completely by Oracle Database.

To use automatic memory management, set the following initialization 
parameters:

MEMORY_TARGET
The MEMORY_TARGET initialization parameter specifies the target memory size. 
The database tunes to the value specified for this parameter, redistributing 
memory as needed between the SGA and the instance PGA. This parameter is dynamic, 
so its value can be changed at any time without restarting the database.

MEMORY_MAX_TARGET
The MEMORY_MAX_TARGET initialization parameter specifies the maximum 
memory size. The value specified for this parameter serves as the limit 
to which the MEMORY_TARGET initialization parameter can be set. This 
parameter is static, so its value cannot be 
changed after instance startup.

-> If you need tuning advice for the MEMORY_TARGET parameter, 
then use the V$MEMORY_TARGET_ADVICE view.
-> When the AMM is enable the only available advisor is Memory Advisor.

V$MEMORY_CURRENT_RESIZE_OPS
-> Displays information about memory resize operations (both automatic 
and manual) that are currently in progress.

V$MEMORY_DYNAMIC_COMPONENTS
-> Displays information about the current sizes of all dynamically-tuned
 memory components, including the total sizes of the SGA and instance PGA.

V$MEMORY_RESIZE_OPS
-> Displays information about the last 800 completed memory resize operations 
(both automatic and manual).This does not include operations that are 
currently in progress.

V$MEMORY_TARGET_ADVICE
-> Displays tuning advice for the MEMORY_TARGET initialization parameter.

3.2 Automatic Shared Memory Management

Automatic shared memory management simplifies the configuration of the 
SGA by automatically distributing the memory in the SGA for the following 
memory pools:


Database buffer cache (default pool)
Shared pool
Large pool
Java pool
Streams pool

Note:
Fixed SGA and other internal allocations needed by the Oracle Database instance 

-> To enable automatic shared memory management, 
set the following initialization parameters:

STATISTICS_LEVEL to TYPICAL or ALL
SGA_TARGET to a nonzero value
The SGA_TARGET parameter can be set to a value that is less than or 
equal to the value of the SGA_MAX_SIZE initialization parameter.

Set the value of the SGA_TARGET parameter to the amount of memory 
that you intend to dedicate to the SGA.

-> To disable automatic shared memory management, 
set the value of the SGA_TARGET parameter 
dynamically to 0 at instance startup.

-> To monitor and tune the SGA target size:
Query the V$SGAINFO and V$SGA_TARGET_ADVICE views.

-> You can change the value of the SGA_TARGET parameter in the command-line 
interface by querying the V$SGA_TARGET_ADVICE view and using the 
ALTER SYSTEM command.

-> When the ASMM is enable the only available advisor are SGA Advisor and 
   PGA Advisor.

-> Monitoring Shared Memory Management
V$SGA_CURRENT_RESIZE_OPS
Displays information about SGA resize operations that are currently in progress.

V$SGA_RESIZE_OPS
Displays information about the last 800 completed SGA resize operations. 
This does not include operations that are currently in progress.

V$SGA_DYNAMIC_COMPONENTS
Displays information about the dynamic components in the SGA. 
This view summarizes information of all completed SGA resize operations that occurred 
after instance startup.

V$SGA_DYNAMIC_FREE_MEMORY
Displays information about the amount of SGA memory available for future 
dynamic SGA resize operations.


3.3 Manual Shared Memory Management

-> When the AMM & ASMM are disable then only available advisor are 
Shared pool Advisor and Buffer Cache Advisor.

Query V$SHARED_POOL_ADVICE view to know how to size the shared pool.
Query V$DB_CACHE_ADVICE view to know how to size the database buffer cache.

Database buffer cache
The database buffer cache is sized using the DB_CACHE_SIZE initialization parameter.

Shared pool
The shared pool is sized using the SHARED_POOL_SIZE initialization parameter.

Large pool
The large pool is sized using the LARGE_POOL_SIZE initialization parameter.

Java pool
The Java pool is sized using the JAVA_POOL_SIZE initialization parameter.

Streams pool
The Streams pool is sized using the STREAMS_POOL_SIZE initialization parameter.

In-memory column store
The In-memory column store is an optional static SGA pool. 
It is sized using the INMEMORY_SIZE initialization parameter.


3.4 Automatic PGA Memory Management

Tuning PGA_AGGREGATE_TARGET
To help you tune the value of the PGA_AGGREGATE_TARGET initialization 
parameter, Oracle Database provides two PGA performance advisory views: 
V$PGA_TARGET_ADVICE and V$PGA_TARGET_ADVICE_HISTOGRAM. By using these views, 
you do not need to use an empirical approach to tune the value of the 
PGA_AGGREGATE_TARGET parameter. Instead, you can use these views to predict 
how changing the value of the PGA_AGGREGATE_TARGET parameter will 
affect key PGA statistics

SELECT * FROM V$PGASTAT;
SELECT program, pga_used_mem, pga_alloc_mem, pga_freeable_mem, pga_max_mem 
FROM V$PROCESS;
SELECT low_optimal_size/1024 low_kb,
(high_optimal_size+1)/1024 high_kb,
optimal_executions, onepass_executions, multipasses_executions
FROM V$SQL_WORKAREA_HISTOGRAM
WHERE total_executions != 0;

SELECT optimal_count, ROUND(optimal_count*100/total, 2) optimal_perc, 
onepass_count, ROUND(onepass_count*100/total, 2) onepass_perc,
multipass_count, ROUND(multipass_count*100/total, 2) multipass_perc
FROM
(SELECT DECODE(SUM(total_executions), 0, 1, SUM(total_executions)) total,
SUM(optimal_executions) optimal_count,
SUM(onepass_executions) onepass_count,
SUM(multipass_executions) multipass_count
FROM V$SQL_WORKAREA_HISTOGRAM
WHERE low_optimal_size >= 64*1024);

SELECT TO_NUMBER(DECODE(sid, 65535, null, sid)) sid,
operation_type operation,
TRUNC(expected_size/1024) esize,
TRUNC(actual_mem_used/1024) mem,
TRUNC(max_mem_used/1024) "max mem",
number_passes pass,
TRUNC(TEMPSEG_SIZE/1024) tsize
FROM V$SQL_WORKAREA_ACTIVE
ORDER BY 1,2;


SELECT name profile, cnt, DECODE(total, 0, 0, ROUND(cnt*100/total)) percentage
FROM (SELECT name, value cnt, (SUM(value) over ()) total
FROM V$SYSSTAT
WHERE name
LIKE 'workarea exec%');

#Using the V$PGA_TARGET_ADVICE View
The V$PGA_TARGET_ADVICE view predicts how changing the value of the 
PGA_AGGREGATE_TARGET initialization parameter will affect the following 
statistics in the V$PGASTAT view:

cache hit percentage
over allocation count

SELECT ROUND(pga_target_for_estimate/1024/1024) target_mb,
estd_pga_cache_hit_percentage cache_hit_perc,
estd_overalloc_count
FROM V$PGA_TARGET_ADVICE;


#Using the V$PGA_TARGET_ADVICE_HISTOGRAM View
The V$PGA_TARGET_ADVICE_HISTOGRAM view predicts how changing the value of 
the PGA_AGGREGATE_TARGET initialization parameter will affect the statistics 
in the V$SQL_WORKAREA_HISTOGRAM view. 
Use this view to display detailed information about the predicted number 
of optimal, one-pass, and multi-pass work area executions for the 
PGA_AGGREGATE_TARGET values used for the prediction.

SELECT low_optimal_size/1024 low_kb, (high_optimal_size+1)/1024 high_kb,
estd_optimal_executions estd_opt_cnt,
estd_onepass_executions estd_onepass_cnt,
estd_multipasses_executions estd_mpass_cnt
FROM V$PGA_TARGET_ADVICE_HISTOGRAM
WHERE pga_target_factor = 2
AND estd_total_executions != 0
ORDER BY 1;

#Limiting the Size of the Program Global Area

In automatic PGA memory management mode, Oracle Database attempts to 
adhere to the PGA_AGGREGATE_TARGET value by dynamically controlling the 
amount of PGA memory allotted to work areas. However, PGA memory usage 
may exceed the PGA_AGGREGATE_TARGET setting at times due to the following 
reasons:

The PGA_AGGREGATE_TARGET setting acts as a target, and not a limit.
PGA_AGGREGATE_TARGET only controls allocations of tunable memory.

Excessive PGA usage can lead to high rates of swapping. When this occurs, 
the system may become unresponsive and unstable. In this case, consider 
using the PGA_AGGREGATE_LIMIT initialization parameter to limit overall 
PGA usage.

-------------------------
About PGA_AGGREGATE_LIMIT
-------------------------
The PGA_AGGREGATE_LIMIT initialization parameter enables you to specify 
a hard limit on PGA memory usage. If the PGA_AGGREGATE_LIMIT value is 
exceeded, Oracle Database aborts or terminates the sessions or processes 
that are consuming the most untunable PGA memory in the following order:

Calls for sessions that are consuming the most untunable PGA memory are 
aborted. If PGA memory usage is still over the PGA_AGGREGATE_LIMIT, then 
the sessions and processes that are consuming the most untunable PGA memory 
are terminated.

In determining the sessions and processes to abort or terminate, Oracle 
Database treats parallel queries as a single unit

By default, the PGA_AGGREGATE_LIMIT parameter is set to the greater of 2 GB, 
200% of the PGA_AGGREGATE_TARGET value, or 3 MB times the value of the 
PROCESSES parameter. However, it will not exceed 120% of the physical 
memory size minus the total SGA size. 
The default value is printed into the alert log. A warning message is 
printed in the alert log if the amount of physical memory on the system 
cannot be determined.

-> The PGA_AGGREGATE_LIMIT initialization parameter can be set dynamically; 
a database restart is not necessary. You can set the value of PGA_AGGREGATE_LIMIT 
regardless of whether automatic memory management is being used.


SQL> alter system set PGA_AGGREGATE_LIMIT=1G;
alter system set PGA_AGGREGATE_LIMIT=1G
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00093: pga_aggregate_limit must be between 2048M and 100000G

3.5 Manual PGA Memory Management

If both automatic memory management and automatic PGA memory management 
are disabled, then you must manage PGA memory manually by adjusting the 
portion of PGA memory dedicated to each work area. This method can be very 
difficult because the workload is always changing and is not recommended 
by Oracle. Although manual PGA memory management is supported by 
Oracle Database, Oracle strongly recommends using automatic memory management 
or automatic PGA memory management instead.

In releases earlier than Oracle Database 10g, the database administrator 
controlled the maximum size of SQL work areas by setting the following
 parameters: SORT_AREA_SIZEHASH_AREA_SIZEBITMAP_MERGE_AREA_SIZE and  CREATE_BITMAP_AREA_SIZE. Setting these parameters is difficult, because the maximum work area size is ideally selected from the data input size and the total number of work areas active in the system. These two factors vary greatly from one work area to another and from one time to another. Thus, the various *_AREA_SIZE parameters are difficult to tune under the best of circumstances. The initialization parameter WORKAREA_SIZE_POLICY is a session- and system-level parameter that can take only two values: MANUAL or AUTO. The default is AUTO. You can set PGA_AGGREGATE_TARGET, and then switch back and forth from auto to manual memory management mode. When  WORKAREA_SIZE_POLICY is set to AUTO, your settings for *_AREA_SIZE  parameters are ignored. ====================== Path to Documentation: ====================== Database Performance Tuning Guide 10 Instance Tuning Using Performance Views -> Instance Tuning Steps -> Interpreting Oracle Database Statistics -> Wait Events Statistics -> Tuning Instance Recovery Performance: Fast-Start Fault Recovery Database Performance Tuning Guide Part III Tuning Database Memory 11 Database Memory Allocation 12 Tuning the System Global Area 13 Tuning the Database Buffer Cache 14 Tuning the Shared Pool and the Large Pool 15 Tuning the Result Cache 16 Tuning the Program Global Area Database Administrator’s Guide 6 Managing Memory

Thank you for visiting this blog 🙂