Oracle Living Books ‎ > ‎ Oracle Internals and Architecture ‎ > ‎ Recursive Sessions and ORA-00018: maximum number of sessions exceeded



SQL> select count(*) from t;



COUNT(*)

----------

50079



SQL> select count(*) from t;



COUNT(*)

----------

50079



SQL> delete t where rownum = 1;



1 row deleted.



SQL> commit;



Commit complete.





Everything works ok, right?



Now, a little later, lets try some DDL:



SQL> alter table t move;

alter table t move

*

ERROR at line 1:

ORA-00018: maximum number of sessions exceeded





SQL> drop table t;

drop table t

*

ERROR at line 1:

ORA-00018: maximum number of sessions exceeded



What the heck? I am logged on already! How come I get the "maximum number of sessions" exceeded error message?



This error message says we have tried to create a new session but have ran out of session state objects in Oracle... hmm... why does Oracle create a NEW session as I am already logged on !!!



This is because a DDL statement requires use of recursive DML and some recursive (data dictionary) operations in Oracle are done using a recursive session context! This is, that Oracle will silently allocate a new session state object from session state object array (V$SESSION / X$KSUSE) and all the recursive operations state objects (locks, transaction state objects etc) will belong to that recursive session. This separation simplifies privilege management when accessing data dictionary base tables and also helps with cleanout should the data dictionary operation fail.



In fact, also data dictionary QUERIES (populating dictionary cache) are done using a separate recursive session, check this:



SQL> select count(*) from t;



COUNT(*)

----------

50078



SQL> alter system flush shared_pool;



System altered.



SQL> select count(*) from t;

select count(*) from t

*

ERROR at line 1:

ORA-00018: maximum number of sessions exceeded





The last query failed now as the flush shared_pool operation cleared also dictionary cache and during next parse a data dictionary operation was required.



So, it's pretty evident that we have ran out of sessions (in other words, session state objects or slots in V$SESSION session state object array).



Let's check what's the sessions parameter value and how many sessions V$SESSION reports as used:



SQL> select value from v$parameter where name = 'sessions';



VALUE

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

170



SQL>

SQL> select count(*) from v$session;



COUNT(*)

----------

163





The sessions parameter is 170 and only 163 sessions are reported to be used by V$SESSION! We should have 7 session still left?!



Doesn't make sense... let's double check from V$RESOURCE_LIMIT which shows us the usage of various fixed and segmented arrays in Oracle instance, including the session state object array, V$SESSION:





SQL> select * from v$resource_limit;



RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU

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

processes 129 136 150 150

sessions 170 170 170 170

enqueue_locks 13 23 2300 2300

enqueue_resources 13 40 968 UNLIMITED

ges_procs 0 0 0 0

ges_ress 0 0 0 UNLIMITED

ges_locks 0 0 0 UNLIMITED

ges_cache_ress 0 0 0 UNLIMITED

ges_reg_msgs 0 0 0 UNLIMITED

ges_big_msgs 0 0 0 UNLIMITED

ges_rsv_msgs 0 0 0 0

gcs_resources 0 0 0 0

gcs_shadows 0 0 0 0

dml_locks 0 47 748 UNLIMITED

temporary_table_locks 0 3 UNLIMITED UNLIMITED

transactions 0 12 187 UNLIMITED

branches 0 0 187 UNLIMITED

cmtcallbk 0 2 187 UNLIMITED

sort_segment_locks 0 3 UNLIMITED UNLIMITED

max_rollback_segments 11 11 187 65535

max_shared_servers 1 1 UNLIMITED UNLIMITED

parallel_max_servers 0 2 40 3600



The above output shows that the "sessions" array is completely full, all 170 slots of max 170 have been used!



So, V$SESSION is wrong! It does not report all sessions really in use. If you look into V$SESSION view text (with help of V$FIXED_VIEW_DEFINITION), you'll see that V$SESSION reports only USER and BACKGROUND sessions.



But there's a 3rd type of a session - a RECURSIVE session, which is used for recursive data dictionary calls as explained above. V$SESSION doesn't show these.



Here's a little query snippet which queries X$KSUSE and also reports recursive sessions. To test this identified the PADDR of one of my sessions (in a different database, where I wasn't out of session state objects), caused some serious performance problems and then tried to drop a table (which should allocated a recursive session for the duration of data dictionary DML):



SQL> select paddr from v$session where sid = userenv('sid');



PADDR

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

0000000398E73B78



SQL>

SQL> create table t3(a int);

...session got hung here (or rather became very slow...)



In another session I ran this query, to see which sessions belong under that process state object identified above:



SQL> select decode(bitand(ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','?'),ksuudsna

2 from x$ksuse s where ksusepro = '0000000398E73B78';



DECODE(BITAND(KSUSEFLG,19),17, KSUUDSNA

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

USER SYSTEM

RECURSIVE SYS





You see, X$KSUSE says there are two sessions under the process, one regular user session (SYSTEM user) and another recursive session which run under SYS security context.



By the way, the recursive session state objects are not linked directly under the process state object (like normal sessions are, but instead they are under a call state object under your regular session, as seen from the following process state dump:



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

SO: 38f4ac000, type: 3, owner: 398e73b78, flag: INIT/-/-/0x00

(call) sess: cur 398f149b8, rec 398f6b3c8, usr 398f149b8; depth: 0

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

SO: 398f6b3c8, type: 4, owner: 38f4ac000, flag: INIT/-/-/0x00

(session) sid: 150 trans: 0, creator: 0, flag: (2) -/REC -/-/-/-/-/-

DID: 0000-0000-00000000, short-term DID: 0000-0000-00000000

txn branch: 0

oct: 0, prv: 0, sql: 0, psql: 0, user: 0/SYS

temporary object counter: 0

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

SO: 38f461ae0, type: 16, owner: 398e73b78, flag: INIT/-/-/0x00

(osp req holder)



Note the REC bit stating that this is a recursive session (as opposed to USR/SYS for user and background sessions).



So, what's the moral of this story?



Oracle uses recursive sessions for recursive data dictionary operations These sessions are also taken from session state object array controlled by sessions parameter V$SESSION does not show recursive sessions, but V$RESOURCE_LIMIT tells you the truth about session state object array utilization If you hit the ORA-00018 error, then make your sessions parameter array larger or configure your application to use less connections or sessions



Note that in Oracle 11.2 the automatic calculation of sessions parameter has changed and many more session state objects are allocated for a given number of processes by default.



Check this out, I can log on, run queries, update rows:Everything works ok, right?Now, a little later, lets try some DDL:What the heck? I am logged on already! How come I get the "maximum number of sessions" exceeded error message?This error message says we have tried to create a new session but have ran out of session state objects in Oracle... hmm... why does Oracle create a NEW session as I am already logged on !!!This is because a DDL statement requires use of recursive DML and some recursive (data dictionary) operations in Oracle are done using acontext! This is, that Oracle will silently allocate a new session state object from session state object array (V$SESSION / X$KSUSE) and all the recursive operations state objects (locks, transaction state objects etc) will belong to that recursive session. This separation simplifies privilege management when accessing data dictionary base tables and also helps with cleanout should the data dictionary operation fail.In fact, also data dictionary QUERIES (populating dictionary cache) are done using a separate recursive session, check this:The last query failed now as the flush shared_pool operation cleared also dictionary cache and during next parse a data dictionary operation was required.So, it's pretty evident that we have ran out of sessions (in other words, session state objects or slots in V$SESSION session state object array).Let's check what's the sessions parameter value and how many sessions V$SESSION reports as used:The sessions parameter is 170 and only 163 sessions are reported to be used by V$SESSION! We should have 7 session still left?!Doesn't make sense... let's double check from V$RESOURCE_LIMIT which shows us the usage of various fixed and segmented arrays in Oracle instance, including the session state object array, V$SESSION:The above output shows that the "sessions" array is completely full, all 170 slots of max 170 have been used!So, V$SESSION is wrong! It does not report all sessions really in use. If you look into V$SESSION view text (with help of V$FIXED_VIEW_DEFINITION), you'll see that V$SESSION reports only USER and BACKGROUND sessions.But there's a 3rd type of a session - a RECURSIVE session, which is used for recursive data dictionary calls as explained above. V$SESSION doesn't show these.Here's a little query snippet which queries X$KSUSE and also reports recursive sessions. To test this identified the PADDR of one of my sessions (in a different database, where I wasn't out of session state objects), caused some serious performance problems and then tried to drop a table (which should allocated a recursive session for the duration of data dictionary DML):In another session I ran this query, to see which sessions belong under that process state object identified above:You see, X$KSUSE says there are two sessions under the process, one regular user session (SYSTEM user) and another recursive session which run under SYS security context.By the way, the recursive session state objects are not linked directly under the process state object (like normal sessions are, but instead they are under a call state object under your regular session, as seen from the following process state dump:Note the REC bit stating that this is a recursive session (as opposed to USR/SYS for user and background sessions).So, what's the moral of this story?Note that in Oracle 11.2 the automatic calculation ofparameter has changed and many more session state objects are allocated for a given number of processes by default.