Oracle database consuming more Virtual memory

[Former Member]

Hello All,

I have a issue with my database, it is consuming a lot of virtual memory on HP-UX box

Here is the memory utilization of my DB.

its a output of glance command.

SPM_ORA_SID            117    12   0.0   0.2    5.5    5.5  5.26gb   468gb

what all the things i need to check

[Former Member · Accepted solution]

Hi,

Few more details can give better idea!

Total memory available on host, memory distribution (DB, CI, Apps etc.)

DB, OS versions.

Regards,

Nick Loy

[Former Member · Accepted solution]

Here are the detail of

   Release:   HP-UX B.11.31

  Model:                  "ia64 hp server rx6600"

Memory: 98271 MB (95.97 GB)

SQL> show sga

Total System Global Area 8134017024 bytes

Fixed Size                  2198704 bytes

Variable Size            3825206096 bytes

Database Buffers         4294967296 bytes

Redo Buffers               11644928 bytes

on DB+CI on host

[Former Member · Accepted solution]

Here are the detail of

   Release:   HP-UX B.11.31

  Model:                  "ia64 hp server rx6600"

Memory: 98271 MB (95.97 GB)

SQL> show sga

Total System Global Area 8134017024 bytes

Fixed Size                  2198704 bytes

Variable Size            3825206096 bytes

Database Buffers         4294967296 bytes

Redo Buffers               11644928 bytes

on DB+CI on host

swap info :

[Former Member · Accepted solution]

Hello Yves KERVADEC,

I have checked the Notes link provided by you. However, it the configurations looks okay

any further help.. please.

[Former Member · Accepted solution]

Hello All,

Upon further investigation the shadow process created while fetching/updating data from database is occuping 8GB for each process, and even after the session end/close the process is not releasing the memory...the server was rebooted on 16th Feb and i found lot of process in hung state from that day..

Is there any solution to force the process to release the memory once after session end..?

[Former Member · Accepted solution]

Hi,

Did you try with latest SBP?

Regards,

Nick Loy

[Former Member · Accepted solution]

HI Nick,

Yes, I have planned for the same in the next week, hoping for the best

[former_member182657]

Hi Rajesh,

Share results from vmstat & free -g commands.

Thanks

[Former Member]

Hello All,

please find some statistics

SQL> show SGA

Total System Global Area 8134017024 bytes

Fixed Size                  2198704 bytes

Variable Size            3825206096 bytes

Database Buffers         4294967296 bytes

Redo Buffers               11644928 bytes

total PGA allocated	467.584.000	bytes
total PGA inuse	369.367.040	bytes
total PGA used for auto workareas	0	bytes
total PGA used for manual workareas	0	bytes
total freeable PGA memory	53.673.984	bytes

(/root/home/root)(root)#vmstat

         procs           memory                   page                              faults       cpu

    r     b     w      avm    free   re   at    pi   po    fr   de    sr     in     sy    cs  us sy id

    1     1     0  10521943  5648590    0    0     6   15     0    0     0   6371 266995  5788  16  6 78

and i can see a lot of inactive sessions(almost 100+) when i check in V$session, does it means the dead process are not releasing the memory..?

USERNAME                       OSUSER                         STATUS

------------------------------ ------------------------------ --------

MACHINE

----------------------------------------------------------------

SAPDAT                         sidadm                         INACTIVE

host

SAPDAT                         sidadm                         INACTIVE

host

SAPDAT                         sidadm                          INACTIVE

host

[ACE-SAP]

Hi

Having inactive sessions is normal, all SAP workprocess keeps a connection open with it's Oracle shadow process.

Oracle memory config seems Ok... but you are not providing you total amount of memory (excluding swap).

Have VMstat running many times to have representative values.

Check your minfree maxfree parameters.

Regards

973227 - AIX Virtual Memory Management: Tuning Recommendations

[Former Member]

Hello Kervadec,

the link which you have provided is for AIX and my OS os HP-UX,, any further help

[ACE-SAP]

Hi

Sorry, working too much on Aix and for me now Unix=Aix

Check the here under notes that can help

Regards

172747 - SAP on HP-UX: OS kernel parameter recommendations

1112627 - SAP on HP-UX: Recommendations for swap and pseudo swap

[ACE-SAP]

Hi

You can also check Oracle memory usage from SAP transaction DB02

Check memory allocated to the two main memory areas: PGA & SGA.

You could check the advisors (size for estimation) that can help you to predict the impact of size decrease.

Regards

789011 - FAQ: Oracle memory areas

[former_member188883]

Hi Rajesh,

Please check db_cache size and other Oracle memory parameter values configured.

Check in top command what is the process ID associate with process consuming more memory

Regards,

Deepak Kori