Windows Server 2012

Oracle DB 11g-r2

The problem is this: When you restart or turn on the server, everything works perfectly, the memory is normal, the number of sessions, too. But after 2-3 days of continuous work, the consumed memory increases, and the number of sessions increases significantly to 450+. As far as I know, the database session should be thrown out by itself and there should be no problems with it. But what happens to memory? At the moment, it is 5.4GB passed 3 days. Tomorrow will be even more. In the end, it will rest on the fact that the base will stop accepting new horse traffic even if everything is unloaded. It helps only reboot. Help me to understand. enter image description here

Updated

If you sin on the wrong distribution of PGA \ SGA memory, let's see what is in fact, not strong in administration:

SELECT name, sum(value/1024) "Value - KB" FROM v$statname n, v$session s, v$sesstat t WHERE s.sid=t.sid AND n.statistic# = t.statistic# AND s.type = 'USER' AND s.username is not NULL AND n.name in ('session pga memory', 'session pga memory max', 'session uga memory', 'session uga memory max') GROUP BY name

At the output we get:

session uga memory 24672,609375

session uga memory max 66495,8515625

session pga memory 46984

session pga memory max 237577,875

Look at the SGA:

 SELECT ( (SELECT SUM(value) FROM V$SGA) - (SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY) ) "SGA_TARGET" FROM DUAL;

Conclusion:

SGA_TARGET: 5465165824

  • The database itself will not throw out the session until the tcp connection that provides this session is broken. So I would start by studying an application that uses a database to forget about open sessions - Mike
  • @Mike Users are not so much. There is an Siemens IDB application here it bears sessions outside itself. She has 1 data pool, it is one session, and there are 15 pools in total. It works on the principle of opening, recording, closing, and so on in a circle. Abandon this software is not possible due to production needs. What measures can be taken to combat? - Ethernets
  • There is a resource parameter IDLE_TIME, then everything is not very simple with it. Here for example that they discuss sql.ru/forum/391693/ob-ispolzovanii-idle-time - Mike
  • Open-burned-closed should not lead to this effect. that the session clearly does not close. I need to see what this session is all about through v $ session - Mike
  • @Mike At the moment, there is nothing to show through the sessions of just 57. There are some questions for self-written software that weighs as INACTIVE everything else is in order for today. - Ethernets

1 answer 1

The causes of your trouble can be many. Starting from the application itself, oracle caches requests to the wrong distribution of SGA, PGA, and log sizes. When you run, how much RAM does it take? What memory management do you use? Maybe your application is not designed for this amount of resources. Start from here: https://oracle-patches.com/oracle/tuning/3118-setup- memory-database- data-oracle-buffers, caches , -pga

  • when you start the memory consumption starts from 100mb over the weekend increased to 5+ gb. Previously, this particular attention was not paid until 1 strap of memory crashed, while waiting for new shipments, you need to keep the server up - Ethernets