Child Cursors Related with LANGUAGE_MISMATCH

-

The reason behind is NLS_SETTINGS



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 with "LANGUAGE_MISMATCH" attracted my attention and i tried to make clear the reason behind.

NLS_SETTINGS are different


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 got changed in different combinations in every execution, it creates new child and version count increases with LANGUAGE_MISMATCH.

Exactly the same queries and PL/SQL packages are called from different applications. Different applications connect to 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 appropriated 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 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 generates child cursors with LANGUAGE_MISMATCH reason. This is derived from the fact that 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.

Comments

Post a Comment

Popular posts from this blog

Using table stats hint to force a direct path read but with a rownum predicate surprise

-
Image
Manipulating table statistics for the statement period On my blog post " Who makes the decisions? I thought it was Optimizer ", I used parallel hint to force a direct path read. This time i will try to make use of table_stats hint to force a direct path read. There is not much documentation about the usage of this hint. Roger Macnicol blog post ( Correct syntax for the table_stats hint  ) offers up some useful information about it. These are the variables play role in direct path read decision.  STT stands for small table threshold = _db_block_buffers * 0,02 =  3875820 * 0,02 = 77516 MTT stands for medium table threshold = _small_table_threshold * 5 = 77516 * 5 = 387580 VLOT stands for Very Large Object Threshold = _db_block_buffers * 5  = 3875820 * 5 = 19379100 I will show how the thresholds effect the direct path decision. We will use nsmtio (non smart IO) event  to trace the direct path or non-direct path/buffered decision.   Before 11.2.0.2 release, the direct path read d
Read more

Oracle Grid Release Update by using Ansible Playbooks

-
Image
Ansible is a friend in need and a friend indeed for DBAs. In this blog post, I will mention about Redhat Ansible and show how you can patch your grid infrastructure by using it. Ansible is an open source, command-line IT automation software application written in Python. It can configure systems, deploy software, and orchestrate advanced workflows to support application deployment, system updates, and more. Ansible's main strengths are simplicity and ease of use. It is a little bit similar to dcli commands in Exadata environments, but much more powerful as you may run playbooks which consist of different tasks. Ansible stands out from other configuration management tools because it doesn't need agents installed on target hosts. Instead, it leverages SSH for connecting to them and executes commands directly through the shell. This means Ansible communicates with remote hosts securely using SSH, eliminating the need for additional software on the managed machines. It's like h
Read more

Oracle Database Release Update by Using Ansible Playbooks

-
Image
Ansible to Rule Them All In the previous post " Oracle Grid Software Patching by using Ansible Playbooks" , I mentioned how Ansible orchestration can be used to make Oracle Grid Software Patching more clean and simple way. Now I will also patch database software by using Ansible Playbooks. In this post,  I will use blt01 host as the ansible host. Target environment is a two node RAC database. (gns01, gns02). I will patch grid software to 19.22 by using out-of-place methodology with no down time.  Although, there are lots of useful blog posts about how you may use Ansible for automating tasks, there is not much about a complete Oracle database software patching.  Ansible is simply installed by "yum install ansible"  from EPEL repository. Add your hosts(gns01, gns02) in the hosts file under /etc/ansible/hosts and create ssh equivalency for oracle and root users. We need:     * OPatch -   p6880880_122010_Linux-x86-64.zip     * 19.22 RU -  p35940989_190000_Linux-x86-64.
Read more