Tom,
What is the maximum number of bytes a single character might occupy, assuming the database has a char set of UTF8.

Thank you

> It looks like "3"

It is definitely "3" for UTF8:

</code> http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14225/appunicode.htm#i634604

But if you use the "new and suggested by Oracle" AL32UTF8 charset, it is "4", thanks to supplementary characters (click on "table B-2" on the link to get a nice breakdown by character type).

It's worth noting that the Euro Symbol needs 3 bytes.

--

For Italian almost all chars need 1 byte, except the "accented chars" (à,è,ò, etcetera) that take 2 bytes, and the Euro Symbol that takes 3 bytes. So basically - a very modest "overhead" since the frequency of accented chars are around 2-3% in a string on average.

For latin-derived languages such as French, Spanish, German, it's almost the same - just increase the frequency of "funny-looking" (to an English native speaker ;) chars (such as the accented chars), probably to 10%.

--

In passing, let me share an outstanding reference on charsets:

http://czyborra.com/ <code>

I use it so frequently that I've committed the URL to memory :)

In the Oracle® Database SQL Reference
10g Release 2 (10.2) Manual

</code> http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm#i54330 <code>

the following is stated for both the CHAR and VARCHAR2 datatypes:
For performance reasons, Oracle recommends that you use the NLS_LENGTH_SEMANTICS parameter to set length semantics and that you use the BYTE and CHAR qualifiers only when necessary to override the parameter.

We're currently trying to upgrade from 9.2 to 10(.2 I think) and the DBA has run into a problem in our DEVL environment with some non-ascii data that was sent to us by an offsite system. It's in a VARCHAR2(30) field, for example, and the underlying definition of the datatype is, of course, the default of bytes. In the past, we've just ignored the '?' in the data, but I'm wondering if it really is some UNICODE character that we currently don't support.

I'm trying to decide if we should just change the default to be CHAR. Are there any gotcha's that we need to be aware of. We are supposed to try the migration in TEST later this week with an implementation in PROD sometime next month.

Sorry, apparently I was misinterpreting what I was reading. I thought the NLS_LENGTH_SEMANTICS governed how the multi-byte characters were handled.

Our DBA informed us that there’re some control characters in the data which caused problems in the import from the old database to the new. The control characters got expanded from single-byte to multiple-byte upon import, and therefore were too long for the table columns, which were defined with a BYTE length of 10. If they’d been defined with a CHARacter length of 10, it would’ve worked fine because even if a character grew from 1 to 3 bytes, it’s still only one character. My development lead wants me to handle this by going through all of the tables in the database and finding all of the char and varchar2 fields and do this (for example):

ALTER TABLE MODIFY (field1 VARCHAR2(30 CHAR));

Is that the correct approach?

Is there some restrictions in it's use?

I'm told that it is not supported to have length_semantics set to char when creating "oracle structures".

The idea is that having this set when creating an AQ queue_table would lead to a queue_table that is not supported and could experience extreme performance issues as a result. Is there any truth to this?

I have not been able to get any documentation for this, other than the reference to anecdotal evidence that it helps to change it back to BYTE. No recreation is said to be needed, but that parameter should somehow improve performance of SQL statements.

Oracle support has said this in a TAR with no supporting documentation and the anecdotal evidence turns out to be postings here (other people, not you).

As we are about to do this in production, I just wanted to see if you saw more of a benefit than I can see. Support is apparently not interested in looking elsewhere (AWR, system state dumps, traces...) until this has been tested in our production system.

Do you want me to post the TAR # here? I will post again if I get some form of documentation for this.

Thank you for validating my feeling that this theory is not obvious.

The postings they have found have reported solving performance issues. It has not indicated how bad the performance is or what kind of performance problem it is that has been experienced.

One reason I've heard for why this would help would be that the translation from character to byte takes some time. But then the object would need to be recreated, and that is said to not be needed.

The problem we have is that the dequeue operation experiences massive contention. It ends up taking 70% of the database server activity and maxing out memory. All contention is on library cache and it is for 50% of the database activity.

I find it very surprising that this parameter could cause this by how it is set, even when no objects are created. The contention is all on an "unknown:" SQL. The SQL ID maps to a LOB retriever (the messages are XMLTYPE payloads) and the table4_ reference for them maps to our AQ table for XML messages.

I have not been able to find a way to get what happens from Oracle instrumentation as this is a background service for the dequeue call, but it results in over 800 million latches per hour for library cache and library cache lock. The wait time per hour is 27 hours spread over many connections.

The fact that there is a library cache issue is undisputable, the problem is with how to prove what it is and how we can solve or work around it. Would you have any pointers for how we would go about identifying the actual problem this LOB retriever service is encountering?

I'll try to use your answers to get the people involved, both Oracle support and the on site production support DBA's, to help with identifying more documentation about this.

The concern we have is that at the end of the month another system will need to be installed on this server and that will cause our performance to be further degraded as we're already have sever performance problems and the CPU is maxed out. That is just background to why I'm so concerned with testing a parameter that probably will do nothing in production. I'm afraid we'll just lose a week or so as the procedure for getting it done in production takes several days.

I understand that it isn't impacted that way. They idea is that there somehow is a bug hiding that has not been identified and this somehow causes cursors to not be shared and that in turn causes this extreme performance issue.

I guess a bug can impact in many odd ways, but this sounds as wishful thinking and I'm afraid we'll be running late on fixing this once this has been tested.

Thank you so much for your answers and help in verifying my fear with this. I hope this thread will help in getting some attention to the need to produce documentation for why this is good and also increased understanding that this change has a very small chance of solving this problem.

Hi,
I have set this parameter to char on session level. How do i check doing the query on database that it worked? or now i have CHAR instead of BYTE.

ops$tkyte%ORA10GR2> show parameter semantics

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics                 string      BYTE
ops$tkyte%ORA10GR2> alter session set nls_length_semantics = 'CHAR';

Session altered.

ops$tkyte%ORA10GR2> show parameter semantics

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics                 string      CHAR

Hi Tom,

When nls_length_semantics is set the system level, it appears that it does not affect the varchar2 columns semantics: the column will default to byte even though this parameter is set to char.  Only when nls_length_semantics is set at session level, it will have an effect. Is this a bug?

Please see the followiing test case:

 SQL> select * from v$version;

 BANNER
 --------------------------------------------------------------------------------
 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
 PL/SQL Release 11.2.0.1.0 - Production
 CORE    11.2.0.1.0      Production
 TNS for Linux: Version 11.2.0.1.0 - Production
 NLSRTL Version 11.2.0.1.0 - Production

 
 SQL> alter system set nls_length_semantics = 'CHAR' scope = memory;

 System altered.

 SQL> show parameter nls_length_semantics

 NAME                                 TYPE        VALUE
 ------------------------------------ ----------- ------------------------------
 nls_length_semantics                 string      CHAR

 SQL> create table t3(c1 varchar2(5));

 Table created.

 SQL> select table_name, data_type, char_used from dba_tab_columns where table_name in ('T3');

 TABLE_NAME                     DATA_TYPE            C
 ------------------------------ -------------------- -
 T3                             VARCHAR2             B


 SQL> alter SESSION set nls_length_semantics = 'CHAR' ;

 Session altered.

 SQL> show parameter nls_length_semantics

 NAME                                 TYPE        VALUE
 ------------------------------------ ----------- ------------------------------
 nls_length_semantics                 string      char
 
 SQL> drop table t3;

 Table dropped.

 SQL> create table t3(c1 varchar2(5));

 Table created.

 SQL> select table_name, data_type, char_used from dba_tab_columns where table_name in ('T3');

 TABLE_NAME                     DATA_TYPE            C
 ------------------------------ -------------------- -
 T3                             VARCHAR2             C

Hi Tom, 

Thanks for your quick response:

The behavior is the same even after reconnect:

 $ sqlplus a/a

 SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 6 13:24:56 2013

 Copyright (c) 1982, 2009, Oracle.  All rights reserved.


 Connected to:
 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
 With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
 Data Mining and Real Application Testing options

 SQL> show parameter nls_length_semantics

 NAME                                 TYPE        VALUE
 ------------------------------------ ----------- ------------------------------
 nls_length_semantics                 string      CHAR

 SQL> create table t4 (c1 varchar2(5));

 Table created.


 SQL> select table_name, data_type, char_used from dba_tab_columns where table_name in ('T4');

 TABLE_NAME                     DATA_TYPE            C
 ------------------------------ -------------------- -
 T4                             VARCHAR2             B


Can you please file the bug?

Thanks!

Thanks Tom.

After I restarted the database, the system level setting for nls_length_format takes effect.

We are setting nls_length_semantics=char in session level.
After that we are creating one table create table ... select * from

Still we can that new table which got created having data type precision as byte insted of character .
Can you please advice hoe to achieve aforesaid .
Source table --
create table test1
(name varchar2(20 byte));

Target I am expecting to be as
create table test1
(name varchar2(20 char));

ops$tkyte%ORA11GR2> create table t
  2  as
  3  select cast( dummy as char(20 char)) xxx,  dummy yyyy from dual;

Table created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> desc t
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 XXX                                               CHAR(20 CHAR)
 YYYY                                              VARCHAR2(1)

I am using DMU to convert an old US7ASCII to AL32UTF8 format. I know how to do the conversion and it works very well, I am concerned about only one field that is defined with BYTE semantics, but in order for conversion to succeed I need to convert that field to CHAR semantics.

I have a field "some_code CHAR(4)" with BYTE semantics in the original US7ASCII database, If I change that field to CHAR semantics, the conversion is successful in my test database. My concern is will the existing application code break? in other words, will the SQL, PL/SQL, embedded SQL, etc that currently exist will still work?

Elsewhere I have been reading in asktom, you say that SUBSTR, INSTR, and all those use CHAR semantics anyway, which is encouraging.

Thanks,
Khalid

I have been making some tests; but I guess it is not possible to export a schema from a database with 'standard' charset (and default byte characters) to a unicode DB - it mantains the original byte setting. Is there anyway to circunvent this ?

Or should I use Oracle DMU? Not sure on how 'instruct' it to change the varchar2(nn byte) to varchar2(nn char) - some columns it did weird things, like changing varchar2(1000) to varchar2(4000 byte), wich is not what I want...

I apologize for not being specific... I think that knowing what we do and pretend to do will help.
We do use an internal database for our application schema structures, used to deploy on new clients. This database uses a single-byte character set, WE8MSWIN1252. We are upgrading this database among the years, since Oracle 8i... So all DDL were using varchar2(nn) without knowing about byte or char, using the implicit database standard of byte in that case. Then we do an expdp of this initial database to deploy on new clients (So we do not do all SQL DDL every time).

One small client asked to use Oracle XE; since it has a unicode charset, we are not able to sucessfully import on it, because of special characters that uses more than on byte. We do not even have need of unicode, once we do work only with portuguese, spanish and english.

Is it possible to do an automated conversion of the implicit varchar2(nn byte) into varchar2(nn char)? The application itself can deal with unicode, so it will be fine to it.

Maybe I have to convert my origin database to unicode - but in that case, will its expdp data works fine again databases that will remain on WE8MSWIN1252?

And how could I do that conversion, if it is required? The Oracle Migration assistant to Unicode (DMU) made some weird things...

Tailor, let´s see the answer from Tom but I personally really do not recommend to change 11g XE characterset (had too many issues in the past after that, such as sessions disconnecting without warning and instabilities in general) or to set NLS_LENGTH_SEMANTICS (in my experience it is simply IGNORED by XE import tools, be imp or impdp).
So the only viable option is the one I use in my customers running XE : do an import with metadata only, run a script that changes BYTE to CHAR in VARCHAR2 columns and later do an import with data only - http://manchev.org/2011/08/migrating-single-byte-encoding-data-to-al32utf8/ have an example.

Best regards,

J. Laurindo Chiappa

Hi Tom

My database is configured with charecter al32utf8.

I have verified that nls_length_semantics is set to char in both instance and session level.

When I create a column of varchar2(10 char) I would expect it to be able to contain 10 characters nomatter how many bytes they take, but I am only allowed to insert 5.

Can you please explain why?

SQL> select * from nls_database_parameters where PARAMETER  = 'NLS_CHARACTERSET' ;

PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_CHARACTERSET
AL32UTF8


SQL> create table test (col1 varchar2(10 char) ) ;

Table created.

SQL> desc test ;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               VARCHAR2(10 CHAR)

SQL> insert into test values ('éééééééééé') ;
insert into test values ('éééééééééé')
                         *
ERROR at line 1:
ORA-12899: value too large for column "SYS"."TEST"."COL1" (actual: 20, maximum:
10)


SQL> insert into test values ('éééééé') ;
insert into test values ('éééééé')
                         *
ERROR at line 1:
ORA-12899: value too large for column "SYS"."TEST"."COL1" (actual: 12, maximum:
10)


SQL> insert into test values ('ééééé') ;

1 row created.

SQL>

Thank you in advance,

Merk