Don't ignore NLS and NLS_LANG

I would like to share an interesting finding about effect of NLS and NLS_LANG on performance which I learned recently. I don’t know if Database administrators and developers really pay attention to some of the environmental variables like NLS_LANG and NLS. NLS_LANG variable tell Oracle what character set the client is using so that Oracle server could do conversion while sending and receiving data from Oracle client tools. This is different from any server parameter setting. I believe you could have a different NLS_LANG setting on client side when compared with server end and it should not affect performance (Some one needs to validate). NLS environmental variable is used for native language support. Setting it in the client helps the terminal to display correct character set which you could read.

We could query NLS_DATABASE_PARAMETERS settings in database to find database settings.

select * from NLS_DATABASE_PARAMETERS where parameter in ('NLS_LANGUAGE','NLS_CHARACTERSET');

PARAMETER                 VALUE

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

NLS_LANGUAGE              AMERICAN

NLS_CHARACTERSET          AL32UTF8

And here are the values I had for client

[oracle@tstxxc03:/home/oracle] $ echo $LANG

en_US.ISO8859-1

[oracle@tstxxc03:/home/oracle] $ echo $NLS_LANG

AMERICAN_AMERICA.WE8ISO8859P1

The rule is that you should have matching values for NLS_LANG and LANG variables. OS host needs to spend considerable amount of time converting characters from one character set to another character set and it could add significant run times to your batch script and CPU load to the host.

I tested this behavior in QJDA1 instance which is used by JDA application and used following script

-- This script is used to check CPU utilization of Oracle processes when run from multiple windows

-- Used by Madhu K Nair 11/05/2010

-- Checking STSC.DFUTOSKU table

set heading off

set linesize 10000

set recsep off

set echo off

set termout off

set trimspool on

set serveroutput off

set feedback off

set pages 0

set time on timing on

spool /backup/qjda1/exp/dfutosku1.lst;

select 'DMDUNIT'||'||'||'DMDGROUP'||'||'||'DFULOC'||'||'||'ITEM'||'||'||'SKULOC'||'||'||'ALLOCFACTOR'||'||'||'CONVFACTOR'||'||'|| 'EFF'||'||'||'DISC'||'||'||'FCSTTYPE'||'||'||'HISTTYPE'||'||'||'MODEL'||'||'||'SUPERSEDESW'||'||'||'FF_TRIGGER_CONTROL' from dual;

select /*+ PARALLEL (d, 4) */ DMDUNIT||'||'||DMDGROUP||'||'||DFULOC||'||'||ITEM||'||'||SKULOC||'||'||ALLOCFACTOR ||'||'||CONVFACTOR||'||'|| EFF||'||'||DISC||'||'||FCSTTYPE||'||'||HISTTYPE||'||'||MODEL||'||'||SUPERSEDESW|| '||'||FF_TRIGGER_CONTROL from stsc.dfutosku d;

spool off;

Above script took 53:24 seconds to finish with mismatched setting and only 7:24 seconds with correct NLS settings.

## Below 2 lines are the original one which was causing high CPU and long run times

#export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

#export LANG=en_US.ISO8859-1

## Below 2 lines are the  modified one which should improve run times

export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

export LANG=en_US.ISO8859-1

Let me thank Israel Anandan again for finding out this mismatch and advising me ways to fix it. I urge all of you to verify these environmental variables in your systems for any potential issues.