将 Varchar2 字段更改为 NVarchar2 后 Oracle 性能非常糟糕
过去几个月我一直在 oracle（版本 10.2）上开发 DotNet 项目，并使用 Varchar2 作为我的字符串数据字段。这很好，即使在导航项目页面时，刷新也不会超过半秒（这是一个安静的数据密集型项目）。数据引用自两种不同的模式，一种是集中式数据存储，另一种是我自己的。现在，集中式模式将更改为符合 unicode 标准（但尚未），因此所有 Varchar2 字段都将变为 NVarchar2，为此我将模式中的所有字段更改为 NVarchar2，从那时起，性能一直很糟糕..页面刷新时间高达 30/40 秒。
这可能是因为集中式架构中的 Varchar2 字段将在某些存储过程上与我的架构中的 NVarchar2 字段相连接。我知道 NVarchar2 的大小是 Varchar2 的两倍，但这并不能解释突然发生的巨大变化。正如我所说，任何有关寻求改进的提示都会很棒，如果我没有很好地解释该场景，请索取更多信息。
I've been developing a DotNet project on oracle (Ver 10.2) for the last couple of months and was using Varchar2 for my string data fields. This was fine and when navigating the project page refreshes were never more than a half second if even (it's quiet a data intensive project). The data is referenced from 2 different schemas, one a centralised store of data and one of which is my own. Now the centralised schema will be changing to be unicode compliant (but hasn't yet) so all Varchar2 fields will become NVarchar2, in preparation for this I changed all the fields in my schema to be NVarchar2 and since then performance has been horrible .. up to 30/40 second page refreshes.
Could this be because Varchar2 fields in the centralised schema will be joined against NVarchar2 fields in my schema on some stored procedures. I know NVarchar2 is twice the size of Varchar2 but that wouldn't explain the sudden massive change. As I said any tips for what to look for to improve would be great, if I haven't explained the scenario well enough do ask for more information.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
首先，做一个
字符集可能很复杂。您可以拥有单字节字符集、固定大小的多字节字符集和可变大小的多字节字符集。 unicode说明
请参阅此处的 ，如果要将单字节字符集中的字符串连接到两字节字符集中的字符串，则您有一个选择。您可以进行二进制/字节比较（如果在单字节字符集和两字节字符集之间进行比较，通常不会匹配任何内容）。或者您可以进行语言比较，这通常意味着一些 CPU 成本，因为一个值被转换为另一个值，并且通常无法使用索引。
索引是有序的，A、B、C 等。但是像 Ä 这样的字符可能会落在不同的位置，具体取决于语言顺序。假设索引结构将 ä 放在 A 和 B 之间。然后您进行语言比较。该比较的语言可能会将 Ä 放在 Z 之后，在这种情况下无法使用索引。 （请记住，您的条件可能是 BETWEEN 而不是 = ）。
简而言之，您需要在模式和中央存储中进行大量准备，以实现不同字符集之间的高效连接。
Firstly, do a
Character sets can be complicated. You can have single-byte charactersets, fixed-size multibyte character set sand variable-sized multi-byte character sets. See the unicode descriptions here
Secondly, if you are joining a string in a single-byte characterset to a string in a two-byte characters set, you have a choice. You can do a binary/byte comparison (which generally won't match anything if you compare between a single-byte character set and a two-byte characterset). Or you can do a linguistic comparison, which will generally mean some CPU cost, as one value is converted into another, and often the failure to use an index.
Indexes are ordered, A,B,C etc. But a character like Ä may fall in different places depending on the Linguistic order. Say the index structure puts Ä between A and B. But then you do a linguistic comparison. The language of that comparison may put Ä after Z, in which case the index can't be used. (Remember your condition could be a BETWEEN rather than an = ).
In short, you'll need a lot of preparation, both in your schema and the central store, to enable efficient joins between different charactersets.
根据您提供的内容很难说什么。当您将数据类型更改为 NVARCHAR2 时，您是否设法检查估计基数和/或解释计划是否发生变化？您可能想阅读以下博客文章，看看是否可以找到线索
http://joze-senegacnik.blogspot。 com/2009/12/cbo-oddities-in-determing-selectivity.html
It is difficult to say anything based on what you have provided. Did you manage to check if the estimated cardinalities and/or explain plan changed when you changed the datatype to NVARCHAR2? You may want to read the following blog post to see if you can find a lead
http://joze-senegacnik.blogspot.com/2009/12/cbo-oddities-in-determing-selectivity.html
它可能不再能够使用以前可以使用的索引。正如纳伦德拉（Narendra）建议的那样，检查解释计划以了解发生了什么变化。一旦集中存储发生变化，索引就有可能再次可用。我建议测试该路径。
It is likely no longer able to use indexes that it previously could. As Narendra suggests check the explain plan to see what changed. It is possible that once the centeralized store is changed the indexes will again be usable. I suggest testing that path.
正确设置 NLS_LANG 初始化参数对于正确的数据转换至关重要。 NLS_LANG 初始化参数指定的字符集应反映客户端操作系统的设置。正确设置 NLS_LANG 可以实现从客户端操作系统代码页到数据库字符集的正确转换。当这些设置相同时，Oracle 假定发送或接收的数据采用与数据库字符集相同的字符集进行编码，因此不执行验证或转换。如果需要转换，这可能会导致数据损坏。
Setting the NLS_LANG initialization parameter properly is essential to proper data conversion. The character set that is specified by the NLS_LANG initialization parameter should reflect the setting for the client operating system. Setting NLS_LANG correctly enables 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 conversions are necessary.