pga_aggregate_target

[Nikee]

Hello,

column name format a60
column value format 999,999,999

select name, value from v$pgastat;

When I ran the above sql statment, I do not see the following lines in the output:

estimated PGA memory for optimal
maximum PGA memory for optimal
estimated PGA memory for one-pass
maximum PGA memory for one-pass

Should I enable any other parameters other than the following one's:
pga_aggregate_target big integer 209715200
workarea_size_policy string AUTO

Thanks,
Nikee

[tamilselvan]

They are in v$pga_target_advice not in v$pgastat.

14:19:23 SQL> desc v$pga_target_Advice
Name Null? Type
-------------------------- -------- ------------------------------------
PGA_TARGET_FOR_ESTIMATE NUMBER
PGA_TARGET_FACTOR NUMBER
ADVICE_STATUS VARCHAR2(3)
BYTES_PROCESSED NUMBER
ESTD_EXTRA_BYTES_RW NUMBER
ESTD_PGA_CACHE_HIT_PERCENTAGE NUMBER
ESTD_OVERALLOC_COUNT NUMBER

[Nikee]

Hello TamilSelvan,

Thanks for the problem resolution.

Here is the better way to find the esimated & maximum PGA memory required for Optimal, 1-pass & > 1-pass:

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;

Thanks,
Nikee