Child Cursors Related with LANGUAGE_MISMATCH

The reason behind is NLS_SETTINGS

I’ve moved my blog from https://insanedba.blogspot.com to https://dincosman.com Please update your bookmarks and follow/subscribe at the new address for all the latest updates and content. More up-to-date content of this post may be available there.

I generally use Stew Ashton's below query to summarize reasons why cursors are not shared. 

In one of our production database, the amount of child cursor count related to "LANGUAGE_MISMATCH" attracted my attention and I tried to make clear the reason behind.

According to the Database Hang With 'cursor: mutex X' Contention Due To High Version Count Under LANGUAGE_MISMATCH (Doc ID 2542447.1), whenever there are different NLS settings that get changed in different combinations in every execution, it creates new child cursors and the version count increases with LANGUAGE_MISMATCH.

Exactly the same queries and PL/SQL packages are called from different applications. Different applications connect to the database with different database users, but execute some common SQL statements. Also, these applications are deployed to different servers with different operating systems and client environments.

Some example child cursor reasons are as below.

<ChildNode><ChildNumber>14</ChildNumber><ID>44</ID><reason>NLS Settings(2)</reason><size>2x568</size><NLS_DATE_FORMAT>'DD/MM/RRRR'->'DD.MM.YYYY'</NLS_DATE_FORMAT></ChildNode> 

<ChildNode><ChildNumber>15</ChildNumber><ID>44</ID><reason>NLS Settings(2)</reason><size>2x568</size><NLS_DATE_FORMAT>'DD.MM.YYYY'->'DD/MM/RRRR'</NLS_DATE_FORMAT></ChildNode> 

<ChildNode><ChildNumber>16</ChildNumber><ID>44</ID><reason>NLS Settings(2)</reason><size>2x568</size><NLS_LANGUAGE>'TURKISH'->'AMERICAN'</NLS_LANGUAGE><NLS_TERRITORY>'TURKEY'->'AMERICA'</NLS_TERRITORY><NLS_CURRENCY>'TL'->'$'</NLS_CURRENCY><NLS_ISO_CURRENCY>'TURKEY'->'AMERICA'</NLS_ISO_CURRENCY><NLS_NUMERIC_CHARACTERS>',.'->'.,'</NLS_NUMERIC_CHARACTERS><NLS_DATE_FORMAT>'DD/MM/RRRR'->'DD-MON-RR'</NLS_DATE_FORMAT><NLS_DATE_LANGUAGE>'TURKISH'->'AMERICAN'</NLS_DATE_LANGUAGE><NLS_SORT>'TURKISH'->'BINARY'</NLS_SORT><NLS_TIME_FORMAT>'HH24:MI:SSXFF'->'HH.MI.SSXFF AM'</NLS_TIME_FORMAT><NLS_TIMESTAMP_FORMAT>'DD/MM/RRRR HH24:MI:SSXFF'->'DD-MON-RR HH.MI.SSXFF AM'</NLS_TIMESTAMP_FORMAT><NLS_TIME_TZ_FORMAT>'HH24:MI:SSXFF TZR'->'HH.MI.SSXFF AM TZR'

></NLS_TIME_TZ_FORMAT><NLS_TIMESTAMP_TZ_FORMAT>'DD/MM/RRRR HH24:MI:SSXFF TZR'->'DD-MON-RR HH.MI.SSXFF AM TZR'</NLS_TIMESTAMP_TZ_FORMAT><NLS_DUAL_CURRENCY>'YTL'->'$'</NLS_DUAL_CURRENCY></ChildNode> 

First, I checked all login triggers,  for some users there were "ALTER SESSION SET NLS_DATE_FORMAT" commands. They were added deliberately to prevent  some queries (without appropriate date format casting) from failing. I created login triggers like below to detect all NLS_SETTINGS for applications.

The most common format I detected was as below. 

PARAMETER VALUE

NLS_LANGUAGE TURKISH

NLS_TERRITORY TURKEY

NLS_CURRENCY ?

NLS_ISO_CURRENCY TURKEY

NLS_NUMERIC_CHARACTERS ,.

NLS_CALENDAR         GREGORIAN

NLS_DATE_FORMAT DD/MM/RRRR

NLS_DATE_LANGUAGE TURKISH

NLS_SORT         TURKISH

NLS_TIME_FORMAT HH24:MI:SSXFF

NLS_TIMESTAMP_FORMAT DD/MM/RRRR HH24:MI:SSXFF

NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR

NLS_TIMESTAMP_TZ_FORMAT DD/MM/RRRR HH24:MI:SSXFF TZR

NLS_DUAL_CURRENCY TL

NLS_COMP         BINARY

NLS_LENGTH_SEMANTICS BYTE

NLS_NCHAR_CONV_EXCP FALSE

Most of NLS_SETTINGS are derived from NLS_LANGUAGE and NLS_TERRITORY parameters. More information is available on the blog post NLS defaults for LANGUAGE and TERRITORY by Franck Pachot. Also, Oracle Official Documentation (NLS_LANG FAQ) is a must-read document.

Also, I found that some queries called from PL/SQL packages within scheduler_jobs are also generating child cursors with LANGUAGE_MISMATCH reason. This is derived from the fact that the client environment session NLS_SETTINGS are different from the applications. Whenever a scheduler job is created, its NLS_SETTINGS are set according to the client's nls_session_parameters. You can use dbms_metadata.get_ddl function to detect them like below.

We changed NLS_ENV settings  of all scheduler jobs which are not owned by Oracle Internal Users and made them same with the common format. We used the command below.

 

Hope it helps.