If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Any experience with the init parameter _pga_max_size ?

Hi,

I'm working on DWH Project using Oracle 9.2.0.5 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.

I would say that this depends on how your DWH is set up and how good your Data Marts have been designed. The 2 Parameters which will probably influence the most the total size of the PGA are the parameters sort_area_size and hash_area_size.

Tamil,

Thanks for your input. From what I found out so far I totaly agree with you that the "new" PGA management is not suitable for large DWH applications where you have a lot memory but only a few concurent users. That should be much better documented in the Oracle Doc.

I will try to convince my DBA to set the workarea policy to manual and set the PGA parameters back to the value they had when we were using 8i.

I would say that this depends on how your DWH is set up and how good your Data Marts have been designed. The 2 Parameters which will probably influence the most the total size of the PGA are the parameters sort_area_size and hash_area_size.

mike9,
I just thought for a while if there are other init parameters that greatly influence the PGA more than the sort_area_size.

Apart from the "_size" parameters, the most influence parameter is PROCESSES. Depending upon the value of PROCESSES parameter, Oracle determines the max size for each process within the PGA memory. That is why I said, for DW system , it is not advisable to use PGA.

You may set processes to 200 in the init.ora, but in real life only 10 or 12 users use the system.