Part 2 – How to decide on the value of PGA_AGGREGATE_TARGET for new instance
When configuring a new database, we need to set the PGA_AGGREGATE_TARGET value and we can estimate the size in 3 stages.
1. Initially, we will set up as Oracle recommends 20% of SGA size for DSS systems. But this seems to be very less for large DSS systems.
2. Run the workload on the instance and monitor the performance using PGA statistics by oracle to see whether the PGA is under-configured or over-configured.
3. Tune PGA_AGGREGATE_TARGET using PGA advice statistics.
How to set the PGA value initially
The initial setting value of PGA_AGGREGATE_TARGET is based on the total memory available for the oracle database instance. This value then can be modified dynamically at the instance level.
Let us see in an example.
Physical memory of the system = 4GB
Memory available for OS and other non-oracle applications available on the system = 20% of RAM, i.e (0.8GB.)
Memory available for oracle instance=80% of RAM (3.2GB)
Now, we must divide the SGA and PGA memory from the above memory of 3.2 GB.
OLTP systems, 20% of 3.2 = 0.2*3.2=0.64GB and 80% for SGA
DSS systems with high memory intensive queries, we can use up to 70% of 3.2 =0.7*3.2=2.2 GB.
Good Initial values for PGA_AGGREGATE_TARGET are
For OLTP: PGA_AGGREGATE_TARGET=(Total memory *80)*20%
For DSS: PGA_AGGREGATE_TARGET=(Total_memory *80)*70%
In this example, total memory is 4G, so, for OLTP, the value of PGA_AGGREGATE_TARGET=655M and for DSS, it is 1600M.
How to monitor the statistics
Before starting, we will see how to monitor and interpret the statistics of PGA collected by oracle database to find the performance of automatic PGA memory management.
V$PGASTAT - this view will gives the instance-level statistics on the PGA memory usage and the automatic PGA memory manager.
Columns which are useful for the statistics.
Over Allocation count :
Ø This statistic is cumulative from instance startup.
Ø Over allocation of PGA memory can happen when the size of PGA is too small to accommodate the untunable component of PGA plus the minimum memory required to execute the workarea workload. When this happens, oracle database cannot honor the initialization parameter PGA_AGGREGATE_TARGET and extra PGA memory has to be allocated. If we see occurrence of over allocation, then we need to increase the value of PGA_AGGREGATE_TARGET using the information provided in V$PGA_TARGET_ADVICE.
Extra bytes read/written:
Ø When a work area cannot run optimally, one or more extra passes is performed over the input data.
Ø Extra bytes read/written represent the number of bytes processed during these extra passes since instance startup.
Ø This number is also used to compute the cache hit percentage; ideally, this should be small compared to total bytes processed.
Cache hit percentage
Ø This column reflects the metric on performance of PGA memory component. It is a cumulative from instance startup.
Ø Value of 100% means that all work areas executed by the system since instance startup have used an optimal amount of PGA memory.
Ø When a work area cannot run optimally, one or more extra passes is performed over the input data. This reduces the cache hit percentage in proportion to the size of the input data and number of extra passes performed.
V$PROCESS
· This view has on row for each oracle process connected to an instance.
· The columns PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM and PGA_MAX_MEM can be used to monitor memory usage of these processes.
V$SQL_WORKAREA_HISTOGRAM
· This view shows the number of work areas executed with optimal memory size; one-pass memory size and multi pass memory size since instance startup.
· Statistics in this view are divided into buckets that are defined by optimal memory requirement of the work area. Each bucket is identified by a range of optimal memory requirements specified by the values of the column LOW_OPTIMAL_SIZE and HIGH_OPTIMAL_SIZE.
· The work areas are split into 33 groups based on their optimal memory requirements with the requirements increasing in powers of 2. i.e., work areas optimal requirement varies from 0 KB to 1KB, 1KB to 2KB, 2KB to 4KB and 2TB to 4TB.
· For each work area group, the v$sql_workarea_histogram view shows how many work areas in that group were able to run in optimal mode, how many were able run in one-pass mode and finally how many ran in multi-pass mode.
SELECT
low_optimal_size/1024 "Low (K)",
(high_optimal_size + 1)/1024 "High (K)",
optimal_executions "Optimal",
onepass_executions "1-Pass",
multipasses_executions ">1 Pass"
FROM v$sql_workarea_histogram
WHERE total_executions <> 0;
We can check the proportion of work areas since you started the Oracle instance, using optimal, 1-pass, and multi-pass PGA memory sizes.
SELECT name PROFILE, cnt COUNT,
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%');
V$SQL_WORKAREA_ACTIVE
· This view is used for finding the work areas that are active (or executing) in the instance.
· Small active sorts of 64KB are excluded from this view.
· Use this view to precisely monitor the size of all workarea and to determine if these active areas are spilled to temporary segment.
V$SQL_WORKAREA
· Oracle database maintains cumulative work area statistics for each loaded cursor whose execution plan uses one or more work area.
· Every time a work area is de-allocated, the v$sql_workarea table is updated with execution statistics for the work area.
Tuning the PGA
To help in tune the initialization parameter PGA_AGGREGATE_TARGET , we can use V$PGA_TARGET_ADVICE view.
V$PGA_TARGET_ADVICE
· This view predicts how the statistics cache hit percentage and over allocation count in v$pgastat
will be impacted if we change the value of initialization parameter pga_aggregate_target.
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;
How to tune PGA_AGGREGATE_TARGET
1. If over allocation count > 0, it indicates that PGA_AGGREGATE_TARGET is too small to even meet the minimum PGA memory needs. If PGA_AGGREGATE_TARGET is set within the over allocation zone, the memory manager will over-allocate memory and actual PGA memory consumed will be more than the limit we set. Set PGA_AGGREGATE_TARGET value so that there is no memory over allocation; avoid setting it in the over-allocation zone.
2. After eliminating the over-allocations, aim at maximizing the PGA cache hit percentage, based on the response-time requirements and memory constraints.
a. If available memory >= optimal memory for 100% PGA cache hit %, then set PGA_AGGREGATE_TARGET= optimal memory as in PGA_TARGET_ADVICE .
If AMM is enabled,
Remaining memory will be allocated to SGA.
Else,
Rest of the available memory will be returned to operating system.
b. If available memory < optimal memory for 100% PGA cache hit %, then set PGA_AGGREGATE_TARET=available memory and settle lower PGA cache hit %.
V$PGA_TARGET_ADVICE_HISTOGRAM
· This view predicts how the statistics displayed by the performance view will be impacted if you change the value of the initialization parameter PGA_AGGREGATE_TARGET.
· We can use the dynamic view V$PGA_TARGET_ADVICE_HISTOGRAM to view the detailed information on the predicted number of optimal, one-pass and multi-pass work area executions for the set of PGA_AGGREGATE_TARGET values you use for the predication.
V$SYSSTAT and V$SESSTAT
· Statistics in v$sysstat and v$sesstat views shows the total number of work areas executed with optimal memory size, one-pass memory size and multi-pass memory size.
· These statistics are cumulative since the instance or session was started.
· The following query gives the total number and the percentage of times work areas were executed in these three modes since the instance was started
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%’);
References :
No comments:
Post a Comment