More on NLS_LANG

Continuing on the last post, here is an example to demonstrate the decimal/comma separator by changing the territory setting at the session level.

SQL> create table test1( col1 number(9,2) );
SQL> insert into test1 values (12345.12 );
SQL> select * from test1;

COL1
———-
12345.12

–Now, after the session change:
1* alter session set nls_territory=france
Session altered.
SQL> select * from test1;

COL1
———-
12345,12

In case you have separate schemas for the different territory environments, the session level change can also be done based on a log-on trigger i.e. as soon as a session logs on, the trigger can automatically make the session level change but that would apply to all users connecting to that schema.

NLS_LANG

NLS_LANG is used on the client application machines (web server or application servers) for interacting with the database and providing globalization support that enables the end users to interact with the database in their language.  The default value of this parameter is automatically chosen based on the locale setting of the operating system.  On the Oracle database, the three parameters in v$nls_parameters that you should be concerned about are: NLS_CHARACTERSET, NLS_LANGUAGE and NLS_TERRITORY.  On the client machines: NLS_LANG.

The NLS_LANG parameter uses the following format:

NLS_LANG = LANGUAGE_TERRITORY.CHARACTER_SET

where:

• LANGUAGE Specifies the language and conventions for displaying messages, day name, and month name (American, English being some examples)
• TERRITORY Specifies the conventions for calculating week and day numbers, currency, decimal/comma separator etc. (America, UnitedKingdom being some examples)
• CHARACTER_SET Controls the character set used for displaying messages (UTF8, AL32UTF being some examples).

NOTE: If you do not care about the display part and the currency/decimal-comma separators, you can leave the language and territory to be American and America respectively for the US based installs.  Character set is the important one.

The NLS_LANG character (third part) set should reflect the setting of the operating system client.  For example, if the database character set is UTF8 and the client has a Windows operating system, you should not set UTF8 as the client character set because there are no UTF8 WIN32 clients. Instead the NLS_LANG setting should reflect the code page of the client.

NLS_LANG is set as a local environment variable on UNIX platforms.  NLS_LANG is set in the registry on Windows platforms. For example, on an American English Windows client, the code page is WE8MSWIN1252. An appropriate setting for NLS_LANG is AMERICAN_AMERICA.WE8MSWIN1252.

Setting NLS_LANG correctly allows proper conversion from the client operating system code page to the database character set. When these settings are the same,
Oracle assumes that the data being sent or received is encoded in the same character set as the database character set, so no validation or conversion is performed. This can lead to corrupt data if the client code page and the database character set are different and conversions are necessary.

So, bottomline:

DB Server: the nls_characterset should be either UTF8 or AL32UTF8 (the nls_language and nls_territory need to be set on a per deployment basis as needed).
Client machines (Windows) : Based on the code page of the client.  Oracle has a matrix that details what those values should be based on the operating system locale.

On Windows application box:  The NLS_LANG parameter is stored in the registry under the HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME ID\NLS_LANG subkey, where ID is the unique number identifying the Oracle home.

On Unix application box: Set the local environment variable accordingly.

A point of caution:  NLS_LANG settings could be different (as compared to the above rule) when you run in MS-DOS or batch mode i.e. when using Oracle utilities like SQL *LDR, SQL *PLUS, Import/Export, data-pump etc..  That is because the MS-DOS mode uses (there are a few exceptions) a different character set (or a code page) from Windows (ANSI code page) and the default entry in the registry for Windows is always set to the appropriate Windows code page.  If this is not done, then when running in MS-DOS or batch mode, data corruption can occur very easily.  You can set it to it’s correct value by using the “SET NLS_LANG” command at the start of the batch.  Look at Section E in the Oracle globalization guide to get a list of the Oracle character sets for Operating System Locales and make the changes as needed.

Using SARGs (Searchable Arguments)

Searchable Arguments or SARGs is an industry acronym that denotes that one writes the where clause filter criteria in such a way that the optimizer can make use of an index seek operation. Consider this query for example (SQL Server syntax):

SELECT first_name, last_name
FROM authors
WHERE DATEDIFF(yy,birth_date,GETDATE()) > 21

This is a query which does not have a searchable argument since there is a function applied on the indexed column. However, this same query can be re-written as:

SELECT first_name, last_name
FROM authors
WHERE birth_date < DATEADD(yy,-21,GETDATE())

In this re-write of the query, the function has been removed from the indexed column so that that index can be used – thus making it a Searchable Argument in the where clause. A couple of other such scenarios:

a) QTY_COL + 1 > 10: Since there is a expression in the column, this is not a SARG. Right thing to do would be to remove the expression and move it to the other side of the operator.

b) Left(Last_Name, 2) = ‘De’: This is again not a searchable argument and can be re-written as : Last_Name like ‘De%’

We had discussed FBI (Function Based Indexes) in Oracle and computed columns in SQL Server in another blog post on this site. Using those in scenarios in which you do not have a choice and must apply a function or an expression to an indexed column, the performance issues can be resolved. It does involve a bit of an overhead though so do that only if absolutely needed.

Identifying most resource intensive queries in Oracle

The code below will pull the top ten SQL statements as ranked by disk reads per execution in a descending order.  The rownum filter at the end can be changed to  show more or all SQL that has executed in a database.  If you recall from an earlier post on this blog,  the rownum filter needs to be applied after the inline view execution.  In addition, additional WHERE clause predicates can be added that only show the SQL for one or more of the previously  identified top sessions.   You can restrict it to a particular user in the system or have filters on CPU_TIME etc.

select sql_text,
username,
disk_reads_per_exec,
buffer_gets,
disk_reads,
parse_calls,
sorts,
executions,
rows_processed,
hit_ratio,
first_load_time,
sharable_mem,
persistent_mem,
runtime_mem,
cpu_time,
elapsed_time,
address,
hash_value
from
(select sql_text ,
b.username ,
round((a.disk_reads/decode(a.executions,0,1,
a.executions)),2)
disk_reads_per_exec,
a.disk_reads ,
a.buffer_gets ,
a.parse_calls ,
a.sorts ,
a.executions ,
a.rows_processed ,
100 – round(100 *
a.disk_reads/greatest(a.buffer_gets,1),2) hit_ratio,
a.first_load_time ,
sharable_mem ,
persistent_mem ,
runtime_mem,
cpu_time,
elapsed_time,
address,
hash_value
from
sys.v_$sqlarea a,
sys.all_users b
where
a.parsing_user_id=b.user_id and
b.username not in (’sys’,’system’)
order by 3 desc)
where rownum < 11
/