Oracle 23c New Features for DBA's
Why you need to increase the cache size of sys.audsess$ sequence in RAC?
Answer:-
Background:- In oracle database every session must have an AUDSID (v$session.audsid) when the session spawns/created in database, this number is derived from a sequence called sys.audsess$ where the cache option set to 20 and noorder.
When you have an database that can provide logon storms , for example many number of users logged in first time during business start may create lot of logon storms to the database directly and these sequence value in the dictionary cache will be exhausted and it has to parse again and again another set 20 sequences cache values, which need an enqueue (SQ Lock) Enq: SQ for a long time or most of the time depend on the logon storms received.
In RAC this will become worse since the library cache/dictionary cache are global, both will have wait for this enqueue and cause dead lock or hang situation.
In order to mitigate the issue, we have to give cache clause for sequence audsess$ the bigger number especially for RAC instances (with noorder clause), say about 1000.
-Hope this helps
Follow Me!!!