You can get the correct size of PGA using V$PGA_TARGET_ADVICE, dynamic performance view.
SQL> SELECT ROUND(pga_target_for_estimate/1024/1024) target_mb,
2 estd_pga_cache_hit_percentage cache_hit_perc,estd_overalloc_count
3 FROM V$PGA_TARGET_ADVICE;
TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
---------- -------------- --------------------
250 66 13703
500 87 86
1000 95 4
1500 95 0
2000 98 0
2400 99 0
2800 99 0
3200 99 0
3600 99 0
4000 99 0
6000 99 0
8000 99 0
12000 99 0
16000 99 0
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.
sys@OEM12C>SELECT round(s.value/1024/1024 ,2) MB ,s.sid,a.username
2 FROM V$SESSTAT S, V$STATNAME N, V$SESSION A
3 WHERE n.STATISTIC# = s.STATISTIC# and
4 name = 'session pga memory'
5 AND s.sid=a.sid
6 ORDER BY s.value;
MB SID USERNAME
-------------------- -------------------- ------------------------------
.41 1104
.41 1103
.41 1105
.47 1089
.47 1095
.53 1074 ICME
.53 1078 ICME
.53 1063 ICME
.53 1087
.66 1098
.78 1064 ICME
.78 1084 ICME
.8 1100
.89 1099
.97 1069 SYS
1.03 1097
1.1 1088
1.47 1061 ZABBIX
1.78 1096
2.39 1067 GGSMGR
4.65 1102
11.21 1101
To check the total PGA in use and hit ratio for PGA
sys@GGS>SELECT * FROM V$PGASTAT; NAME VALUE UNIT ---------------------------------------------------------------- -------------------- ------------ aggregate PGA target parameter 3879731200 bytes aggregate PGA auto target 3467400192 bytes global memory bound 387973120 bytes total PGA inuse 27149312 bytes total PGA allocated 57661440 bytes maximum PGA allocated 4563434496 bytes total freeable PGA memory 25755648 bytes process count 25 max processes count 63 PGA memory freed back to OS 15763969212416 bytes total PGA used for auto workareas 0 bytes maximum PGA used for auto workareas 2923335680 bytes total PGA used for manual workareas 0 bytes maximum PGA used for manual workareas 1899520 bytes over allocation count 0 bytes processed 36117468017664 bytes extra bytes read/written 3105058237440 bytes cache hit percentage 92.08 percent recompute count (total) 4175071
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.
sys@GGS>SELECT
2 low_optimal_size/1024 "Low (K)",
3 (high_optimal_size + 1)/1024 "High (K)",
4 optimal_executions "Optimal",
5 onepass_executions "1-Pass",
6 multipasses_executions ">1 Pass"
7 FROM v$sql_workarea_histogram
8 WHERE total_executions <> 0;
Low (K) High (K) Optimal 1-Pass >1 Pass
-------------------- -------------------- -------------------- -------------------- --------------------
2 4 22167255 0 0
64 128 31068 0 0
128 256 13624 0 0
256 512 52058 0 0
512 1024 1821196 0 0
1024 2048 528162 0 0
2048 4096 285108 6 0
4096 8192 366635 300 0
8192 16384 555839 14 0
16384 32768 13526 205 0
32768 65536 236743 276 0
65536 131072 5570 52 0
131072 262144 24924 12789 0
262144 524288 105 608 0
524288 1048576 0 290 0
1048576 2097152 0 59 0
2097152 4194304 0 8 0
You can check the proportion of work areas since you started the Oracle instance, using optimal, 1-pass, and multipass PGA memory sizes.
sys@GGS>SELECT name PROFILE, cnt COUNT, 2 DECODE(total, 0, 0, ROUND(cnt*100/total)) PERCENTAGE 3 FROM (SELECT name, value cnt, (sum(value) over ()) total 4 FROM V$SYSSTAT 5 WHERE name like 'workarea exec%'); PROFILE COUNT PERCENTAGE ---------------------------------------------------------------- -------------------- -------------------- workarea executions - optimal 26154868 100 workarea executions - onepass 14607 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.
references http://avdeo.com/2007/06/17/tuning-pga-memory-oracle-database-10g/