Any experience with the init parameter _pga_max_size ?
I'm working on DWH Project using Oracle 220.127.116.11 Our production servers has 20cpus and around 40Gb of ram. The pga_aggregate_target is set to 16GB and workarea_size_policy is set to AUTO.
The big problem we have is that from these 16GB of PGA only 5GB are used (based on V$PGASTAT). The reason for that is that Oracle never allocates more then 100MB of PGA to a serial or a parallel process.
So to improve the performance of these queries which need more then 100MB of PGA I was thinking about to use the parameter "_pga_max_size" and to set it to 256MB.
Has anybody so experience with this parameter?
Thanks for any feedback