Hi All,
I have PGA_AGGREGATE_TARGET size. As shown in the below query AUTO is used for PGA management. However, I don't understand why 0 is set to value of PGA_AGGREGATE_TARGET? Shouln't it have been more than 0?
It is written the following in the Oracle documents.
https://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams157.htm#REFRN10165
Setting PGA_AGGREGATE_TARGET to 0 automatically sets the WORKAREA_SIZE_POLICY parameter to MANUAL.
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
>>show parameters workarea_size_policy
| workarea_size_policy | string AUTO |
>>show parameters pga
| pga_aggregate_target | integer 0 |
My second question is, in 11g auto pga management, when you consider to change the size of PGA_AGGREGATE_TARGET what will you check? What are your criterias in order to change the value of PGA_AGGREGATE_TARGET size?
Is it enough to check cache hit percantage column in v$pgastat and the sum of pass counts in v$sql_workarea_histogram?
>> SELECT * FROM v$pgastat;
aggregate PGA target parameter 7516192768 bytes
aggregate PGA auto target 5128141824 bytes
global memory bound 751616000 bytes
total PGA inuse 1826552832 bytes
total PGA allocated 2566455296 bytes
maximum PGA allocated 5099891712 bytes
total freeable PGA memory 440074240 bytes
process count 899
max processes count 1491
PGA memory freed back to OS 12140080070656 bytes
total PGA used for auto workareas 11377664 bytes
maximum PGA used for auto workareas 1932968960 bytes
total PGA used for manual workareas 0 bytes
maximum PGA used for manual workareas 1071104 bytes
over allocation count 0
bytes processed 124239258235904 bytes
extra bytes read/written 88505795584 bytes
cache hit percentage 99,92 percent
recompute count (total) 434062
>>SELECT sum(onepass_executions), sum(multipasses_executions) FROM v$sql_workarea_histogram;
185 0
Thanks in advance