You can get the correct size of PGA using V$PGA_TARGET_ADVICE, dynamic performance view.
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.
To check the total PGA in use and hit ratio for PGA
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.
You can check the proportion of work areas since you started the Oracle instance, using optimal, 1-pass, and multipass PGA memory sizes.
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/
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/
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
