Today I want to talk about Oracle Database session usage; last week there was a problem with a database reporting ORA-00018: maximum number of sessions exceeded, but the number of sessions from v$session was much less than the one specified in SESSIONS parameter.
So, what was going on?
Turns out this is explained by internal recursive sessions that also account but they are not seen in the dictionary views. Note ID 419130.1 explains this in detail but I wanted to see this behavior in my 12c test database.
SQL> select count(*) from v$session;
COUNT(*)
———-
104
SQL> select count(*) from x$ksuse where bitand(ksspaflg,1) !=0 ;
COUNT(*)
———-
132
Also wanted to know what sessions were recursive and seems like all of them are performing DDL operations.
SQL> select INDX,decode(bitand(ksuseflg,19),17,’BACKGROUND’,1,’USER’,2,’RECURSIVE’,’?’),ksuudsna from x$ksuse s WHERE decode(bitand(ksuseflg,19),17,’BACKGROUND’,1,’USER’,2,’RECURSIVE’,’?’)=’RECURSIVE’;
INDX DECODE(BIT KSUUDSNA
———- ———- ——————————
8 RECURSIVE SYS
11 RECURSIVE SYS
14 RECURSIVE SYS
15 RECURSIVE SYS
17 RECURSIVE SYS
19 RECURSIVE SYS
20 RECURSIVE SYS
21 RECURSIVE SYS
29 RECURSIVE SYS
32 RECURSIVE SYS
38 RECURSIVE SYS
39 RECURSIVE SYS
40 RECURSIVE SYS
42 RECURSIVE SYS
123 RECURSIVE SYS
126 RECURSIVE SYS
129 RECURSIVE SYS
134 RECURSIVE SYS
136 RECURSIVE SYS
137 RECURSIVE SYS
138 RECURSIVE SYS
139 RECURSIVE SYS
142 RECURSIVE SYS
144 RECURSIVE SYS
145 RECURSIVE SYS
148 RECURSIVE SYS
154 RECURSIVE SYS
155 RECURSIVE SYS
156 RECURSIVE SYS
157 RECURSIVE SYS
242 RECURSIVE SYS
247 RECURSIVE SYS
249 RECURSIVE SYS
250 RECURSIVE SYS
252 RECURSIVE SYS
253 RECURSIVE SYS
255 RECURSIVE SYS
257 RECURSIVE SYS
259 RECURSIVE SYS
264 RECURSIVE SYS
265 RECURSIVE SYS
269 RECURSIVE SYS
272 RECURSIVE SYS
273 RECURSIVE SYS
275 RECURSIVE SYS
276 RECURSIVE SYS
278 RECURSIVE SYS
367 RECURSIVE SYS
369 RECURSIVE SYS
371 RECURSIVE SYS
375 RECURSIVE SYS
376 RECURSIVE SYS
379 RECURSIVE SYS
381 RECURSIVE SYS
383 RECURSIVE SYS
384 RECURSIVE SYS
388 RECURSIVE SYS
389 RECURSIVE SYS
391 RECURSIVE SYS
392 RECURSIVE SYS
60 rows selected.
This is something to keep in mind when setting SESSIONS parameter in future.
Thanks,
Alfredo