Oracle parametrization

[Former Member]

Hello,

We have separated SAP PI application and our Oracle database 11g on two hosts. With this SAP oracle database on same host are running a lot of another non sap DBs. So its hard to calculate how much of resources we can use. We need to adjust these parameters because recently was another DB installed on that host and hardware is now not enough, so we would like to check if some of our parameters can be decreased because they are just dev and qua systems.

So my idea is to fix obviously wrong parametrization and try it with less memory and see if I have any issue then.

Here are our current parameters:

pga_aggregate_target                 big integer 3092376453

sga_target                           big integer 4528M

shared_pool_size                     big integer 2224M

db_cache_size                        big integer 2224M

memory_max_target                    big integer 0

memory_target                        big integer 0

Oracle DBA guys want to turn it to AMM by re-set these values

alter system set pga_aggregate_target=0 scope=spfile;

alter system set sga_target=0 scope=spfile;

alter system set shared_pool_size=0 scope=spfile;

alter system set db_cache_size=0 scope=spfile;

alter system set memory_max_target=4G scope=spfile;

alter system set memory_target=2G scope=spfile;

alter system set cursor_sharing=SIMILAR ;

What solution would you prefer? Thanks a lot for your inputs.

[thiago_cavalheiro · Accepted solution]

Hello Matej,

We do not recommend the usage of AMM or ASMM, as there are some known issues that are very common like the ORA-04030 and ORA-04031. Also you most likely won't never reach the same performance of a fine tuned database with fixed memory areas. So though the use is allowed it is not advisable.

Hope this helps.

Best regards,

Thiago

[Former Member]

If you can omit performance issues, you can use AMM for dev and qua systems.

Regards,

Roman

[Former Member]

Just a genereal remark: running SAP oracle databases along with other oracle installations is often quite difficult. SAP has it's own set of parameters, patches, brtools and a completely different directory structure than standard oracle installations.

In your case you might also consider to extend the servers memory, instead of micro managing every memory parameter. Memory is cheap and gives you a huge performance gain.

Cheers Michael

[volker_borowski2]

Hi,

crosscheck notes 105047 and 789011.

789011 is saying:

As of Oracle 11g, you can use the parameters MEMORY_TARGET and MEMORY_MAX_TARGET to define the total size of PGA and SGA (Automatic Memory Management, AMM). We do not recommend that you use these parameters in the SAP environment.

"Not recommended" is not "not allowed" so in case your Oracle guys are doing a good

job on the other systems, why not give them some trust?

Crosscheck the usage of the memory areas in ST04 (Memory Advisors) first.

With just knowing the size and not the utilisation you can not expect a qualified answer.

Just from comparing other systems, i think you can give up one GB in shared_pool

and one or one and a half GB in PGA. May be more. May be some additional stuff from db_cache.

But again : this depends on utilisation.

Volker