Oracle memory leaks issue on 19c and 11g versions-CodePudding

We have oracle DB (19c) installed on Oracle Linux 8 machine. When we connect our servers with 19c db, the memory of DB machine starts growing up. As long as the server handles load and performs db operations, memory keeps growing and after few hours all the memory gets used up and there is no more free memory available. We have short PS/SQL statements and stored procedures which get executed as different CRUD, commit, rollback operations are performed. We did some research and found this command to free up memory but this doesn't work.

sync; echo 3 > /proc/sys/vm/drop_caches

Note: We found the same behaviour on 11g database, so it's not a database versions specific problem, also we changed DB machine but the issue is persistent.

A screenshot of our machine with fully used memory is attached. Any help will be highly appreciated.

CodePudding user response：

Its hard to say whether you really have memory leak or not. Most likely no. Oracle spawns dedicated process for each connection. This process "mounts" shared memory regions. That's why you see such a huge memory allocation. Try to use tool like ps_mem.py, which can better deal with shared memory segments.

Oracle DB has two memory related parameters SGA PGA, normally DB should not use more RAM than defined sum of these two parameters.

Just execute vmstat and check whether your server actually is swapping memory or not.

CodePudding user response：

Make sure you are closing all of your database sessions, as well as cursors. All of those processes you showed appear to be dedicated server processes, opened for each login to the database. That's why your memory goes up as soon as you start using it: every login requires its own dedicated memory, in addition to the memory used by the background server processes.

See here for information on tuning Oracle memory usage.

Also note that your memory is not exactly 100% used: 29.9% is recently used or reserved in buffers and ready to be made available if there is a need. If it were actually used, then the used value would be 100% and your swap usage would likely be much higher.