What Character Set Supports Which Language

Overview

This list identifies which Oracle ISO or MSWIN character sets contains which supported language(s). All of these character sets contain the 7-bit ASCII English characters and numerals. Oracle lists both the ISO and MSWIN characteristics and their recommendation is to use the MSWIN database character sets instead of the ISO versions, even if all of your clients are not Windows based. This is simply because the MSWIN database character sets are more comprehensive and support more characters than the ISO database character sets.

There are no problems using a MSWIN database character set on a non-Windows/Unix platform.

You can determine what the current value for your database character set is by executing the following statement:

select value from NLS_DATABASE_PARAMETERS where parameter=’NLS_CHARACTERSET’;

To determine the client setting use ‘NLS_LANG’ instead of ‘NLS_CHARACTERSET’ and see note below.

Character Sets

WE8ISO8859P15 (ISO 8859-15), WE8MSWIN1252 : Albanian, Basque, Breton, Catalan, Cornish, Danish, Dutch, Finnish, French, Frisian, Galician, German, Greenlandic, Irish Gaelic, Italian, Luxembourgish, Norwegian, Portuguese, Rhaeto-Romanic, Scottish Gaelic, Spanish, Swedish and the Euro symbol

EE8ISO8859P2 (ISO 8859-2), EE8MSWIN1250 : Albanian, Croatian, Czech, German, Hungarian, Polish, Slovak, Slovenian, Sorbian

NEE8ISO8859P4 (ISO 8859-4), BLT8MSWIN1257 : Danish, Estonian, Finnish, German, Greenlandic, Latvian, Lithuanian, Norwegian, Sami, Slovenian, Swedish

CL8ISO8859P5 (ISO 8859-5), CL8MSWIN1251 : Bulgarian, Byelorussian, Slavic Macedonian, Russian, Sebian, Ukrainian

AR8ISO8859P6 (ISO 8859-6), AR8MSWIN1256 : Arabic

EL8ISO8859P7 (ISO 8859-7), EL8MSWIN1253 : Greek (monitoniko orthography)

IW8ISO8859P8 (ISO 8859-8), IW8MSWIN1255 : Hebrew

VN8MSWIN1258 : Vietnamese

TH8TISASCII : Thai

JA16SJIS : Japanese

ZHS16GBK : Simplified Chinese

KO16MSWIN949 : Korean

ZHT16HKSCS and ZHT16HKSCS31 : Traditional Chinese (Taiwan)

ZHT16HKSCS and ZHT16HKSCS31 : Traditional Chinese (Hong Kong)

WE8ISO8859P2 (ISO 8859-1) : Albanian, Basque, Breton, Catalan, Cornish, Danish, Dutch, Faroese, French, Finnish, Frisian, Galician, German, Greenlandic, Icelandic, Irish Gaelic (new orthography), Italian, Luxemburgish, Norwegian, Portugese, Rhaeto-Romanic, Scottish Gaelic, Spanish, Swedish

SE8ISO8859P3 (ISO 8859-3) : Esperanto, French, German, Italian, Maltese, Portugese, Turkish

WE8ISO8859P9 (ISO 8859-9) : Albanian, Basque, Breton, Catalan, Cornish, Danish, Dutch, Finnish, French, Galician, German, Greenlandic, Irish Gaelic (new orthography), Italian, Luxemburgish, Norwegian, Portugese, Rhaeto-Romanic, Scottish Gaelic, Spanish, Swedish, Turkish

NE8ISO8859P10 (ISO 8859-10) : Dannish, Estonian, Faroese, Finnish, German, Greenlandic, Icelandic, Irish Gaelic (new orthography), Lithuanian, Norwegian, Sami, Slovenian, Swedish

Notes:

An external resource http://www.eki.ee/letter allows you to choose a language and then it displays an overview of all the charactersets that contain all the letters.

Oracle’s Character Set Consolidation

Overview

Oracle’s default character set for new Unicode database installations is AL32UTF8 and Oracle recommends implementing AL32UTF8 for Unicode. Depending on your application requirements you may not be able to choose AL32UTF8 and you should always check the application’s certification matrix to determine the exact database character set.

If you elect to not implement Unicode there are approximately 250 character sets to choose from. Oracle has recently come out with a consolidated list of recommended character sets that should meet standard language and business requirements.

In the Oracle 11g release the number of character sets that can be selected through the Database Creation Assistant (DBCA) has been reduced to reflect this consolidation.

Oracle’s Character Set Consolidation for the Oracle Database

You can still select legacy character sets via the CREATE DATBASE command. With the advent of character set consolidation Oracle is attempting to offer more mainstream choices and strealine the list of character sets.

The default as mentioned above is AL32UTF8 as opposed to the old default of US7ASCII and the DBCA will limit the character sets listed based on the new consolidated list although Oracle continues to support the legacy character sets.

As usual it is important to distinguish between database character sets and the NLS_LANG settings (client character sets). According to Oracle one of the goals of producing a consolidated list is to avoid confusion between the two.

Unicode

AL32UTF8

8 bit Character Sets

EE8MSWIN1250
CL8MSWIN1251
WE8MSWIN1252
EL8MSWIN1253
TR8MSWIN1254
IW8MSWIN1255
AR8MSWIN1256
BLT8MSWIN1257
VN8MSWIN1258
TH8TISASCII
EE8ISO8859P2
NEE8ISO8859P4
CL8ISO8859P5
AR8ISO8859P6
EL8ISO8859P7
IW8ISO8859P8
WE8ISO8859P10
NE8ISO8859P13
WEISO8859P15

When using Windows based clients and using a xx8MSWIN12xx database character set (regardless of the OS the database is using for the Server OS) it makes more sense than selecting a xx8ISO8859Pxx database character set.

Japanese, Korean and Chinese Character Sets

JA16SJIS
JA16SJISTILDE
JA16EUC
JA16EUCTILDE
KO16MSWIN949
ZHS16GBK
ZHT16MSWIN950
ZHT16HKSCS
ZHT16HKSCS31
ZHT32EUC

Oracle’s recommendations for some popular database character sets that should be migrated as follows:

US7ASCII migrate to WE8MSWIN1252 or WE8ISO8859P15 etc.
WE8ISO8859P1 : WE8MSWIN1252 is a superset
UTF8 migrate to AL32UTF8
ZHS16CGB231280 : ZHS16GBK is a superset
ZHS32GB18030 migrate to AL32UTF8
KO16KSC5601 : KO16MSWIN949 is a superset
ZHT16BIG5 : ZHT16MSWIN950 solves various problems of ZHT16BIG5

Notes for Other Character Sets:

Similar to the comment above ZHT16HKSCS31 is excluded from the shortlist (ZHT16HKSCS, however, is included).
JA16SJISYEN and JA16EUCYEN are also excluded from the shortlist. These two character sets are not supported as database character sets, only as client character sets.
Korean KO16KSC5601 is very popular, but considering the purpose of this shortlist Oracle has chosen only to include KO16MSWIN949.
Baltic (Estonian, Latvian, Lithuanian) ISO-8859- character sets Oracle is considering the effects of un-selecting character sets from the list of NEE8ISO8859P4, NEISO8859P10 and BLT8ISO8859P13.

Recommended Database Character Sets for Macintosh

Only recommendation is AL32UTF8.

List of Recommended Database Character Sets for EBCDIC Platforms

AR8EBCDIC420S
BLT8EBCDIC1112S
CE8BS2000
CL8EBCDIC1025R
CL8EBCDIC1158R
D8EBCDIC1141
DK8EBCDIC1142
EE8BS2000
EE8EBCDIC870S
EL8EBCDIC423R
EL8EBCDIC875R
F8EBCDIC1147
I8EBCDIC1144
IW8EBCDIC424S
JA16DBCS
JA16EBCDIC930
KO16DBCS
S8EBCDIC1143
TH8TISEBCDICS
TR8EBCDIC1026S
WE8BS2000
WE8BS2000E
WE8BS2000L5
WE8EBCDIC1047E
WE8EBCIDC1140
WE8EBCDIC1145
WE8EBCDIC1146
WE8EBCDIC1148

Comments on Removed EBCDIC Database Character Sets

The following character sets have been superseded by other character sets and should not be used anymore and Oracle will put them on the obsolete list:

CL8EBCDIC1025
CL8EBCDIC1025S
CL8EBCDIC1025X
EL8EBCDIC875S

The following character sets have been preceded by new duplicate character sets that have the addition of the Euro symbol character:

WE8EBCDIC37
WE8EBCDIC37C
D8EBCDIC273
DK8EBCDIC277
S8EBCDIC278
IBEBCDIC280
WE8EBCDIC284
WE8EBCDIC285
WE8EBCDIC297
WE8EBCDIC500
WE8EBCDIC500C

Choosing The Right Database Character Set

Choosing Unicode

When it comes time to set up a new database system typically it will take a lot of planning but the most important decision is overlooked. That decision is the selection of the proper character set especially in regards to implemeting or upgrading an ERP application.

In order to position an ERP application for globalization, future acquistions that will require globalization, data and system consolidations, and/or support for international regulations and standards the only choice for a database character set is Unicode.

Unicode Definition

Unicode is a universal character-encoding scheme that enables you to store information from any major language using a single character set. Unicode defines an unique codepoint for each character, the properties for each character as well as providing the basis for standardizing scripts, provides a standard algorithm for bidirectional text and defines cross-mappings to other standards.

Unicode has begun to replace character-encoding schemes such as ASCII, EUC, AND ISO 8859. By utilizing UTF-8 encoding, Unicode can be used to support backward-compatible environments that were designed and implemented in ASCII, such as UNIX. UTF-8 is the encoding standard now used for UNIX, Linux and other similar platforms.

All current de facto Web standards, such as HTML, XML, and so on are providing support or require Unicode. If you deploy new systems using Unicode now you will ensure compatibility with the latest technologies and position your application architecture to leverage future advances without the need for costly and time consuming upgrades involving conversions and data migrations.

Oracle’s 11g Unicode Support

Oracle’s Database Release 11g Release 1 now provides full support for Unicode version 5.0, which is now the standard for multilingual support. By choosing the proper database character set you will ensure that you can develop, deploy and host multiple languages in a single database or as part of a grid.

Some Common Concerns

When it comes time to plan a new Oracle database implementation or upgrade the some of the typical concerns that are expressed are related to performance and storage management. Most of the storage management concerns are related to the UTF-8 being based on a varying-width encoding scheme. While ASCII characters occupy 1 byte each accented Latin characters, Arabic, Cyrillic, Greek and Hebrew occupy 2 bytes each. Other characters including Chinese, Indian, Japanese and Korean occuply 3 bytes each and some supplemental characters will occupy 4 bytes.

Implementing a Unicode database doesn’t necessarily mean doubling or tripling your storage requirements. For example, given a database that consists primarily of English data, the actual amount of storage differential should be very low because only the non-ASCII symbols will actually experience expansion from 1 byte to 2 or 3 bytes. Western European languages contain some diacritics and these characters will generally require 2 bytes in UTF-8. Characters used in Asian languages will require the most expansion and will typically require 3-byte characters instead of 2-byte characters.

Oracle introduced initialization parameters to allow specification of length semantics in Oracle 9i which provides the option of declaring strings in terms of characters or bytes. Byte semantics is the default and character-length semantics can be declared at the column, table, session and the database level. Length semantics can help with storage management layout and allows a common database schema to execute successfully on different databases with different character sets easing the transistion to Unicode.

Performance concerns is the other common area of concern. Oracle’s database performance for Unicode based databases improves with each release and now the performance metrics for same release Unicode vs non-Unicode databases are nearly the same. Performance is impacted more for a Unicode database where PL/SQL string-manipulation functions are heavily leveraged than on a single-byte database. This gap narrows significantly with the latest versions of Oracle.

In regards to interoperability since Unicode supports virtually every language and is a superset of all Oracle character sets you should be able to convert and store all valid incoming data properly.

http://blogs.oracle.com/stevenChan/2009/06/handson_with_oracles_iphone_apps.html

http://blogs.oracle.com/stevenChan/2009/06/handson_with_oracles_iphone_apps.html.

Oracle Certification News

Oracle Certification Emagazine

Oracle Enqueues

A Latch as used in Oracle is a low-level mechanism for serializing concurrent access to memory structures such as cache buffer headers. A latch is a simple memory structure that averages 100 to 200 bytes in size (actual size is dependent upong the version of Oracle, Operating System and hardware platform). Latches are contained in a state object structure and reside in either fixed SGA (parent latches) or the shared pool. They are set via the hardware-atomic compare-and-swap (CAS) instructions – LOCK CMPXCHG for Intel. Latches can be shared and are used for some AQ operations.

Latch Contention occurs when a process attempts to get a latch but fails because another process already has control of it. If the attempt (get) was issue with in no-wait mode, then return to the calling process with a failure result. If the get was issued in a willing-to-wait mode then continue attempting to obtain the latch until x number of tries is reached.

The term to describe a get that is executed in a willing-to-wait mode that tries repeatedly without acquiring the latch is spinning – the process issues the get, if the latch is already held then the process will sleep, wake up after x amount of time expires and attempts to acquire the latch again.

The process attempting to acquire a latch may be waken by the process that releases the latch but this method isn’t used for all latches.

The following views are used to troubleshoot contention issues:

• v$session_event
• v$session_wait
• v$latchholder

Troubleshooting latch contention consists of first identifying the session(s) that are currently experiencing problems and then identify the latch the session is waiting for and determine if the wait time is significant enough to warrant attention. Identifying the child latch that is involved will help determine if the contention is concentrated on a particular child latch or are many child latches involved.

As of Oracle 10.2 KGX Mutexes replace latches (Kernel Generic MuteX – KGX). Mutexes resemble latches (a physical allocation of memory) only they are lighter weight and consume less memory space. They can also be embedded inside other structures, they have flexible spin/yield/wait strategies defined by the client process and they do not factor into the accounting for GETS,SPINS or YIELDS, only WAITS.

Keep in mind that KGX mutexes are not OS mutexes.

In 11g+ mutexes are used instead of library cache latches. The default for pinning library cache cursors and parent examination for 10.2.0.2+ is mutexes – _kks_use_mutex_pin=true.

Some known mutex types in 11g:

• Cursor Parent
• Cursor Pin
• Cursor Stat
• Library Cache
• hash table

Mutex Troubleshooting – involves querying the views: v$session_wait, v$mutex_sleep and v$mutex_sleep_history among others. The mutex sleeps are well instrumented in the wait interface and the P1, P2, P3 values contain the hash value of the library cache objects experiencing contention, the session holding the mutex, etc. The views v$event_name and v$session_wait “text” columns document the meaning of P1, P2 and P3. The v$mutex_sleep view shows the wait time and the number of sleeps for each combination of mutex type and location while the v$mutex_sleep_history view shows the last individual occurences of mutex sleeps based on a circular buffer and this view contains the highest level of detail.

Enqueues – Terminology and Views

Locks are called enqueues because the locking mechanism implements an ordered queuing structure for lock waiters. Latches and mutexes do not have sophisticated queuing mechanisms.

Enqueue Resources – v$resource(X$KSQRS)

Enqueue Locks – v$lock(X$KSQEQ)

Enqueue resource types – v$lock_type has two meanings – ID1 and ID2

Slots in a hash table identify a resource. Resources are not locks, they are placeholders that reference the object that can be locked. The unique identifier (primarykey) for a resource will consists of the following:

• Resource type (TM, TX, MR, …)
• Resource identifier 1 (ID1)
• Resource identified 2 (ID2)
• Example:
• TM-XXXXXXXX-XXXXXXXX

The resource identifiers are used to break down a resource class into individual objects. An example of this would be two tables that both have a TM resource allocated to them in memory. The ID1 specifies the object_id of the table (v$lock_type which contains the meaning of ID1/2 for each resource type).

Locks – provide the link between the resource locked and the holder of that resource. If there are many lock holders on a single resource (assuming compatible mode) then it follows that there will be multiple lock structures pointing to that one resource.

PL/SQL Execution Diagnosis

There are new colummns from 10.2.0.3 on in V$SESSION:

• PLSQL_ENTRY_OBJECT_ID
• PLSQL_ENTRY_SUBPROGRAM_ID
• PLSQL_OBUECT_ID
• PLSQL_SUBPROGRAM_ID

Troubleshooting Tools – Session Level

Profile session wait / CPU timing with V$SESSION_WAIT, V$SESSION_EVENT, V$SESS_TIME_MODEL (10g+) and the tools Snapper, Sesspack, Statspack session mode.

Profile session performance counters – V$SESSTAT

Profile a session’s process stack – statisically – with pstack, procstack, ostackprof, dstackprof, procmon.exe, procexp.exe, oradebug short_stack, oradebug errorstack.

Solarix, Linux and HP-UX use pstack. AIX use procstack, see Metalink for procwatcher. If you have gdb or mdb you can use them to exaxmine stack traces.

Windows – windbg and procexp (sysinternals site) – but you will not be able to see symbolic function names in oracle.exe.

Oracle Internals – oradebug short_stack, oradebug dump errorstack 1 and alter session set events ‘942 trace name errorstack’.

Explain Plan Evolution from 9.2 to 11g

Starting with Oracle 9.2 the typical method for generating explain plans were:

1. explain plan for <statement>

2. select * from table(dbms_xplan.display)

Oracle 10g:

1. autotrace uses dbms_xplan

2. set autotrace on

3. select * from table(dbms_xplan.display_cursor())

Oracle 11g:

Introduced DBMS_SQLTUNE.REPORT_SQL_MONITOR

Inherent issues when using explain plan for – all bind variables are treated as VARCHAR2 and you may not see the actual (real) execution plan Oracle used.

An alternative method is to query the view V$SQL_PLAN_STATISTICS or use DBMS_XPLAN.DISPLAY_CURSOR.

Common Terminology Definitions

Access Path – physical data storage access to read data from tables, indexes, external tables and database links. Only access paths can access physical data and return the row sources.

Row Source – virtual stream of data via access paths or from child row sources

Filter Predicate – row source property – rows can be discarded based on defined conditions. Row sources can be cascading, non-cascading or semi-cascading.

Join – rows are filtered and merged based on rows that match from child row sources. Matching is determined by join predicates. Join operators can only operate on two inputs while other operations can combine the result of more than two row sources.

Oracle Kernel functions are responsible for execution plan steps.

Oracle Cloud Computing

Discovering as much as I can about Oracle Cloud Computing. I haven’t been able to determine if it is possible to run PeopleSoft using this architecture but from just reading through the Oracle documentation i see that you can run an Oracle Database and a Web Server (Web Logic) so that leaves the Application Server and Process Scheduler.

It would be great if you could set up your initial PeopleSoft delivered environments in this type of infrastructure to jump start projects while you wait for hardware to be delivered or you just don’t want to own hardware anymore.

Install Blat on Windows

blat -install localhost
blat -install <smtp host> <email address>
blat -install <smtp host> <email address> 3 25

number of times to send message (3)
TCP/IP port to use (26)

blat -install localhost <email adress>  3 25 FromStan1

store the settings in a Profile named: FromStan1

blat -install <smtp server> <email address> – - FromStan2

store the settings in a profile named: FromStan2, default number of tries and port

blat -to -from -subject -body -server -q

blat -profile (executed on ERAC2 lists)

SMTP: 10.160.225.30 “email address” 3 25

Updated HP-UX 11i v3 Kernel List

The following is a list of non-default kernel parameters for a Production PS HCM 9.0, PeopleTools 8.49 environment running on a rx7640 in a VPAR with 15.5 GB of memory. The list of kernel parameters is for the VPAR containing the Production Database Instance:

Tunable                   Value  Expression    Changes

dnlc_hash_locks            4096  4096

filecache_max         138412032  132MB         Imm (auto disabled)

filecache_min         138412032  132MB         Imm (auto disabled)

ftable_hash_locks          4096  4096

lcpu_attr                     0  0             Imm (auto disabled)

max_thread_proc            1200  1200          Immed

maxdsiz_64bit        2147483648  2147483648    Immed

maxssiz               134217728  134217728     Immed

maxssiz_64bit        1073741824  1073741824    Immed

maxuprc                    3780  3780          Immed

msgmni                     4200  nproc         Immed

msgtql                     4200  nproc         Immed

ncsize                    36672  36672

nflocks                    2048  2048          Imm (auto disabled)

ninode                    35648  8*nproc+2048

npty                        256  256

nstrpty                     256  256

nstrtel                     256  256

semmni                     4200  nproc

semmns                     8400  semmni*2

semmnu                     4196  nproc-4

shmmax               7516192768  7516192768    Immed

shmmni                      512  512           Immed

swchunk                    4096  4096

vnode_cd_hash_locks        4096  4096

vnode_hash_locks           4096  4096

vps_ceiling                  64  64            Immed

vx_ninode                100000  100000        Immed

Tunable                   Value  Expression    Changes
dnlc_hash_locks            4096  4096
filecache_max         138412032  132MB         Imm (auto disabled)
filecache_min         138412032  132MB         Imm (auto disabled)
ftable_hash_locks          4096  4096
lcpu_attr                     0  0             Imm (auto disabled)
max_thread_proc            1200  1200          Immed
maxdsiz_64bit        2147483648  2147483648    Immed
maxssiz               134217728  134217728     Immed
maxssiz_64bit        1073741824  1073741824    Immed
maxuprc                    3780  3780          Immed
msgmni                     4200  nproc         Immed
msgtql                     4200  nproc         Immed
ncsize                    36672  36672
nflocks                    2048  2048          Imm (auto disabled)
ninode                    35648  8*nproc+2048
npty                        256  256
nstrpty                     256  256
nstrtel                     256  256
semmni                     4200  nproc
semmns                     8400  semmni*2
semmnu                     4196  nproc-4
shmmax               7516192768  7516192768    Immed
shmmni                      512  512           Immed
swchunk                    4096  4096
vnode_cd_hash_locks        4096  4096
vnode_hash_locks           4096  4096
vps_ceiling                  64  64            Immed
vx_ninode                100000  100000        Immed