Monday, June 2, 2014

Oracle Recursive Sessions ORA-00018

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

No comments:

Post a Comment