1

We have an Oracle 12c (12.2.0.1) database server set up with AL32UTF8 (Unicode 5.0 UTF-8 Universal character set) character set as per Oracle recommendations.

However, the application that uses this database comes with a vendor recommendation to use WE8MSWIN1252 (MS Windows Code Page 1252 8-bit West European) character set instead.

I know, at least in general terms, how to convert from one character set to another, using either the Data Migration Assistant for Unicode or via a full Export/Import.

But there are two areas that I'm not clear on.

One is that Oracle says there is a potential for data truncation issues (and it explains how/why). Is there any way to find out if that's an issue within my database, short of converting the database in test and trying to do a row-by-row comparison between the converted and non-converted data?

More importantly, since the vendor wants us in a character set that's not the Oracle-recommended default, is there any way to measure what, if any, performance impacts the conversion may have? From what I can tell, the server is having to convert incoming data from the WE8MSWIN1252 character set to AL32UTF8 on inserts/updates and reversing that on selects, right? Are there any views that would let me see how much time the database is spending handling these conversions? I really can't imagine the performance hit is sufficient to justify the conversion, but I'd like to know for sure.

Improve this question
4
  • 2
    I don't think you are likely to run into performance issues I'd be more concerned about data Truncation. Here's the Oracle Article on the subject.
    – Zane
    Nov 27, 2018 at 16:02
  • Yeah, that's a concern, alright, and one I'm worried about. But the Oracle docs do briefly mention performance issues if the client and server are on different character sets. I'm just curious if there are any ways to track that.
    – CaM
    Nov 27, 2018 at 18:24
  • 1
    I don't think you will face any performance degradation. However, why does the vendor recommends outdated character set? Most likely they are just not smart enough to set up their client properly, i.e. they don't know how to set NLS_LANG (or the equivalent settings for Java) value or they are using still some ancient Oracle drivers.
    – Wernfried Domscheit
    Nov 28, 2018 at 19:46
  • I believe, quite strongly, that you're right: the vendor doesn't know what they're talking about. But I thought I would explore the ramifications, as the support folks are telling me the DB is why the application is slow. (The DB is not why. I've shown the data to prove it. But they're still pointing at Oracle rather than their IIS stack... So I'm researching. Against my will. Sigh. The joys of being a DBA!)
    – CaM
    Nov 29, 2018 at 13:34

1 Answer 1

Reset to default
1

The old csscan used to do this, but have you actually run DMA? DMA does a scan of your whole database and then lists all the tables that will suffer a lossy conversion. This is a preview before it actually goes ahead and does the conversion. You will have a higher chance of lossy data if you go from a multi-byte character set like AL32UTF8 to a single-byte character set like WE8MSWIN1252. There are many reasons why you would have lossy data, and part of these are due to databases being accessed by multiple applications and tools on multiple operating systems, with locale/NLS_LANG set correctly or not. If NLS_LANG is set correctly then oracle does single-pass conversions between source and target database. This depends on the application though.

Oracle does state that there is a performance overhead (maybe 5%) on using a multi-byte character sets, but specifies AL32UTF8 as the default as that covers all character sets world-wide. I don't know of any view that calculates conversion times; that's part of the RDBMS.

We personally have chosen WE8MSWIN1252 on our databases for two reasons:

  1. It was going to take too long to convert from US7ASCII to AL32UTF8
  2. It covers most European character sets and we have separate AL32UTF8 databases in our eastern countries

Hope that helps!

Improve this answer
2
  • It looks like the DMA scanner will locate rows with data that cannot be converted TO Unicode (AL32UTF8), but it does not appear to tell me anything about downgrading.
    – CaM
    Nov 29, 2018 at 13:57
  • No I never said I downgraded, did I? DMA will only tell you about what you want to migrate to, whether it is a superset or subset. DMA does not care what you choose and will not make suggestions.
    – sandman
    Nov 30, 2018 at 12:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.