Tuning PGA memory

Tuning PGA Memory – Oracle database 10g
--------------------------------------------------


Correct size of PGA
====================

You can get the correct size of PGA using V$PGA_TARGET_ADVICE, dynamic performance view.


 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;



Checking PGA for each sessions:
================================

You can check session level PGA using V$SESSTAT and V$SESSION view and also you can check the username, who is using

that memory.

 SELECT
s.value,s.sid,a.username
FROM
V$SESSTAT S, V$STATNAME N, V$SESSION A
WHERE
n.STATISTIC# = s.STATISTIC# and
name = 'session pga memory'
AND s.sid=a.sid
ORDER BY s.value;



To check the total PGA in use and hit ratio for PGA:
==================================================

SQL> SELECT * FROM V$PGASTAT;


The ideal way to perform sorts is by doing the entire job in memory. A sort job that Oracle performs entirely in memory

is said to be an optimal sort. If you set the PGA_AGGREGATE_TARGET too low, some of the sort data is written out

directly to disk (temporary tablespace) because the sorts are too large to fit in memory. If only part of a sort job

spills over to disk, it’s called a 1-pass sort. If the instance performs most of the sort on disk instead of in memory,

the response time will be high. This is called multi pass sort.

Another method of checking the efficiency of PGA memory is to check V$SQL_WORKAREA_HISTOGRAM.

V$SQL_WORKAREA_HISTOGRAM displays the cumulative work area execution statistics (cumulated since instance startup) for

different work area groups. The work areas are split into 33 groups based on their optimal memory requirements with the

requirements increasing in powers of two. That is, work areas whose optimal requirement varies from 0 KB to 1 KB, 1 KB

to 2 KB, 2 KB to 4 KB, … and 2 TB to 4 TB.

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 to run in one-pass mode, and finally how many ran in multi-pass mode. The DBA can take

a snapshot at the beginning and the end of a desired time interval to derive the same statistics for that interval.


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;




You can check the proportion of work areas since you started the Oracle instance, using optimal, 1-pass, and multipass

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%');


PROFILE COUNT PERCENTAGE
————————————————– ———- ———-
workarea executions – optimal 6650608 100
workarea executions – onepass 2 0
workarea executions – multipass 0 0

Since almost all the sorting and temporary operation are carried out inder optimal catagory we can conclude that out

PGA is sized correctly.