_pga_max_size parameter

[Former Member]

Is the pgamax_size parameter relevant for 10.2? Oss note 830576 lists this as an additional alternative parameter for 10.1 but states nothing about subsequent releases. Also, is there a general way to determine whether or not a underscore parameter is pertinent for a specific release & patch level?

Regards,

Thad Gustafson

[Former Member]

Hi

you don't have to set the pgamax_size parameter for 10.2, because Oracle have change the limits and defaults for pgamax_size and smmmax_size in 10.2. The values of these hidden parameters now dynamicly changed depending on the value for pga_aggregat_target.

Regards,

Ronny Zimmermann

[Former Member]

Hi thad..

you can very well refer this followin document to know all the details of pgamax_size parameter.....

hope this following link will help you out...

<b>http://www.dba-oracle.com/art_so_undocumented_pga_parameters.htm</b>

or u can go thru this blog

<b>https://beta.blogger.com/comment.g?blogID=12193242&postID=112619488530646768&isPopup=true</b>

let me know in case of issues..

regards

jag

[Former Member]

Hi

No It is not relevant, pga_max_size – this hidden parameter defaults to 200 megabytes, regardless of the setting for pga_aggregate_target.When we set the PGA_AGGREGATE_TARGET and WORKAREA_SIZE_POLICY to auto then the *_area_size parameter are automatically ignored and oracle will automatically use the computed value for these parameters.

Ref http://www.dba-oracle.com/Evidence%20of%20Sort%20Sizes%20in%20an%20Oracle10g%20Database.doc.This will guide you unstd abt this parameter.

Regards

Vinod

[Former Member]

Hello Vinod,

Thank you for your response. I could not access the link you provided.

I understand "_" parameters are typically not recommended. What I would like to know is if there is a specific place that one can look to see if an "_" is relevant to a particular Oracle release?

The background for the question is this. Because of the 200MB limit you sited above, which is per an Oracle process (OSS note 619876 pg. 5), BW queries can quite easily require more space and swap out (due to their high utilization of hash joins, merge joins, sorts, and bitmap operations). As of the previous release of Oracle, Oracle recommended we set this parameter to improve query performance.

SAP recommends changing the value according to the size of the PGA_AGGREGATE_TARGET as discussed on OSS note 830576. As far as I know, setting up automatic PGA management does not disable the parameter I am looking to change unless this is a difference between 10.1 and 10.2 (and that is what I am trying to figure out).

Thanks,

Thad

[Former Member]

Thad you answered your own question...Oracle does NOT document these underscore parameters...that's the general reason they are not recommended to be used...except for very specific situations -- i.e. usually to disable certain behaviors due to bugs in their code...

There exist no place to see if an underscore parameter is relevant...the only place that comes close is metalink.oracle.com...

But what I will say about pga aggregrate in general is that this functionality tries to make the best use of the given pga target it is given among all of its Oracle processes. This works well for OLTP (R/3) systems but for BW systems where 1 or 2 process may require huge amounts of pga memory...pga target may not work quite as well...I think ST04N -> Resource Consuption -> PGA monitor give your historical PGA usage...

Sorry, I wasn't trying to make is sound like you weren't aware that underscore parameters weren't documented earlier...

[Former Member]

Dear Thad,

Yes, There is a place where u can ref _parameters for Oracle release.but It is not public accessable.As of my knowledge ref by some of oracle internal teams which unfortunately I was not part of.

U R right, There are cases where oracle suggested to set this parameter like large DWH DBs, where average execution time of our queries was reduces by 50%.

If you have set, for example the PGA_AGGREGATE_TARGET to 1 GB then Oracle cannot give more than 50 MB to a single work area (for serial operations).

If you want to use more than 100 MB per single work area then may be the PGAMAX_SIZE can help you to achieve better results for your sort tasks and to let you to perform the biggest ones only inside the memory. One more hidden parameter associated with this is SMMPX_MAX_SIZE ,is used only when you are running query with a degree with a parallelism more than 6.

Ans to ur question this parameter does not make any big diff between 10.1 and 10.2 acc to my info.

Let me know still we can share something.....

Regards

Vinod

[Former Member]

Thad,

From one of my friend who works at oracle I Got reply like

Oracle is never allocating more then 200'000'000 bytes of pga memory per process in automatic workarea size policy. Setting the pgamax_size parameter to a higher value was not supported and was leading to ORA-4030.

Therefore, I increased the DOP since I couldn't increase the memory per process.

Regards

Vinod

[Former Member]

Hi

No It is not relevant, pga_max_size – this hidden parameter defaults to 200 megabytes, regardless of the setting for pga_aggregate_target.When we set the PGA_AGGREGATE_TARGET and WORKAREA_SIZE_POLICY to auto then the *_area_size parameter are automatically ignored and oracle will automatically use the computed value for these parameters.

Ref http://www.dba-oracle.com/Evidence%20of%20Sort%20Sizes%20in%20an%20Oracle10g%20Database.doc.This will guide you unstd abt this parameter.

Regards

Vinod

[Former Member]

Oracle underscore parameters are usually not recommended to be set UNLESS explicitly requested by SAP or Oracle.

If it's not in an SAP note then I wouldn't set it for 10.2.

These parameters usually disable default behaviors/functionality in Oracle.