Memory increased after upgrade to ORACLE 11.2.0.2

[martn_sello]

After updating our database to Oracle 11.2.0.2 version, memory usage

has increased significantly in the node SOLMAN, DEV, QAS consuming an

average of 90% of available memory.

SBP were installed: SAP_112024_201111_AIX.zip latest version

containing the fix described in SAP note 1552849 but also these values;

are maintained with the same average consumption.

We are to update PRD and do not want to have this picture.

We have changed the parameters of demand for ORACLE not consume all the resources but low memory consumption and is now in a 94% non-connected users, and no job working.

SQL> show parameter target

NAME TYPE VALUE

-

---

-

---

-

---

archive_lag_target integer 0

db_flashback_retention_target integer 1440

fast_start_io_target integer 0

fast_start_mttr_target integer 0

memory_max_target big integer 6124M

memory_target big integer 6124M

parallel_servers_target integer 16

pga_aggregate_target big integer 0

sga_target big integer 0

NAME TYPE VALUE

-

---

-

---

-

---

lock_sga boolean FALSE

pre_page_sga boolean FALSE

sga_max_size big integer 4608M

sga_target big integer 0

Our OS AIX 6.1 TL6 SP4 with 12gb RAM

Thanks..

[Former Member · Accepted solution]

Can do you say what means

We have changed the parameters of demand for ORACLE not consume all the resources but low memory consumption and is now in a 94% non-connected users, and no job working.

what do you change ?

Parameters:

*.SHARED_POOL_SIZE

*.DB_CACHE_SIZE

*.PGA_AGGREGATE_TARGET

Are set correctly ? Can you check how the memory usage are distributed for users on OS level ?

Why do you think what oracle uses this memory , not SAP for example ?

Check in st06n for example.

Regards.

[martn_sello · Accepted solution]

Hello,

No, the only parameters that were changed are:

memory_target=6124M

memory_max_target=6124M

pga_agreggate_target=0

Regards

[former_member188883 · Accepted solution]

Hi,

Why the value of this paramter

pga_agreggate_target = 0

Regards,

Deepak Kori

[Former Member · Accepted solution]

Read Note 1431798 - Oracle 11.2.0: Database Parameter Settings

Why you set pga_agreggate_target=0 ?

After this setting SQL workareas are sized using the *_AREA_SIZE parameters , are you know how it works ??

Where do you read about this parameters

memory_target

memory_max_target and why you change it ?

Post current initSID.ora generated from spfile.

Also i not understand you have DEV on one server where 12 GB ,

are on same server SAP application server ? What more ?

Regards.

[martn_sello · Accepted solution]

Hi.

This parameter was purposely put together with a consultant ORACLE but it is not proof that we do.

If we look at the note of recommended parameters apply ORA 11.2 and 20% of the setting of the parameter pga_aggregate_target memory but neither worked.

Both the server DEV, QAS and SM1 instance running with your database and SAP application server.

thanks

[Former Member · Accepted solution]

his parameter was purposely put together with a consultant ORACLE

Give your Basis job to ORACLE consultant (which do not know how SAP uses oracle)

and i think your SAP project will stops in the near future ...

Can you explain

and 20% of the setting of the parameter pga_aggregate_target memory but neither worked.

??

Post your initSID.ora generated from current spfile .

How memory are distributed between SAP and Oracle ?

Check in SAP : st02 --> detail analysis meny --> storage

and post info here.

P.S. --> Note 789011 - FAQ: Oracle memory areas :

"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 . "

Regards.

[martn_sello · Accepted solution]

Hi again,

The company's have other systems on ORACLE so ask the consultant if he had any interesting idea to apply.

The pga_aggregate_target parameter is set to 0 for that being 20% as stated in the note reference parameters SAP memory is still firing, and this change was reversed and the behavior of the nodes is maintained.

st02:

Subpool 10 273.555 K 78.008 K 351.563

Subpool 40 124.631 K 112.674 K 237.305

Not in Pool 1.625.637 K 1.625.637

Total 2.023.823 K 190.682 K 2.214.505

Roll area 6.348 K Cursor Cache 2.000

Paging buffer 1.200 K d+w user bss 0

Short storage 3.906 K d+w user data 0

Total 13.454 K

User storage for all work processes 309.4

Size of SAP executables (Disp+Work) Shared text

Size of Extended Memory 4.190.2

Virtual memory allocated TOTAL 6.714.1

Maximum heap area for all work processes 7.812.500 K

thanks...

[martn_sello · Accepted solution]

We made changes at the nodes of memory parameters suggested in the note 789011, improves memory performance.

But after reaching a peak of 90% consumption of memory, the server releases the same but there is no paging.

REgards.

[former_member189725 · Accepted solution]

Please do not set these parameters

memory_target=6124M

memory_max_target=6124M

Please refer to SAP note , 789011 .

Set the following parameters for each of the systems .

DB_CACHE_SIZE

SHARED_POOL_SIZE

PGA_AGGREGATE_TARGET

The note 789011 , clearly states the SQLs you can use to check the actual memory usage in a system. The buffer cache and Shared pool have static sizes defined by the above parameters . Check the amount of PGA allocation which is added dynamically as required from the note 789011. The PGA allocation is dependent on the number of workprocess you configure in the SAP system.

[Former Member · Accepted solution]

Martín , since you do not post your initSID.ora (or only memory related parameters from it)

i'm only can guess what you want (and set) for oracle PGA+SGA ~ 6124M

You say the OS haves 12 GB memory ....

Virtual memory allocated TOTAL 6.714.1

Maximum heap area for all work processes 7.812.500 K

Try to summarize all this areas , in peak load your configuration needs 61246.7147.630 = 20468 MB

You not have problems with paging just because this is are DEV and TST systems , and load are not so high.

Regards.

[martn_sello · Accepted solution]

Do not send file information because we validate InitOra.sid according to SBP latest Oracle for AIX installed for the current month.

We eliminated the settings of parameters and recalculate max_target allocate memory for the parameters according to the recommendations of SAP:

DB_CACHE_SIZE

SHARED_POOL_SIZE

PGA_AGGREGATE_TARGET

Although this memory is in 98% of peak use but the server is not paging, I think the problem is the run time loader AIX does not reflect the reality of memory consumption

Physical PageSpace | pages/sec In Out | FileSystemCache

│% Used 99.8% 0.2% | to Paging Space 0.0 0.0 | (numperm) 25.8% Free 0.2% 99.8% | to File System 4.0 4.0 | Process 55.2%

│MB Used 12260.5MB 38.4MB | Page Scans 0.0 | System 18.9%

│MB Free 27.5MB 20441.6MB | Page Cycles 0.0 | Free 0.2%

│Total(MB) 12288.0MB 20480.0MB | Page Steals 0.0 | -

---

│ | Page Faults 1332.6 3 | Total 100.0%

Regards,

[former_member188883]

Hi,

From the information above, It seems like you have 3 SAP instances on a single server having 12 GB RAM ?

Is this true ?

Regards,

Deepak Kori

[martn_sello]

Hi, deepakori

The same is running only a single instance, but the behavior is similar in other instances such as DEV and QAS.

Regards,