读取 CLOB 列很慢

Question

希望有人能够阐明我目前在 Oracle DB 方面遇到的问题 - 我确信这很简单！

我已经设法在示例中重新创建它，所以这里是数据库结构：

CREATE TABLE MyTable(
    ixMyTable NUMBER,
    clobData CLOB
)
/

CREATE OR REPLACE PACKAGE PKGTEST
AS
    PROCEDURE DoSomething(
        cur_OUT OUT SYS_REFCURSOR
        );
END PKGTEST;
/

CREATE OR REPLACE PACKAGE BODY PKGTEST
AS

PROCEDURE DoSomething(
    cur_OUT OUT SYS_REFCURSOR
)
AS
BEGIN
    OPEN cur_OUT FOR 
        SELECT ixMyTable, clobData
        FROM MyTable;
END;

END PKGTEST;
/

GRANT EXECUTE ON PKGTEST TO TEST_ROLE
/

BEGIN
    FOR i IN 1 .. 7000 LOOP
        insert into mytable values (i, TO_CLOB('123456'));
    END LOOP;
END;
/

额外信息：

架构所有者是 TEST_SCHEMA

用户是 CARL

CARL 具有 TEST_ROLE 角色

鉴于上述数据库设置，我有一个使用标准的 C# 测试应用程序System.Data.OracleClient.OracleCommand 等，以便执行 PKGTEST.DoSomething 并将结果放入数据网格 (DevExpress)。

很确定网格在这里无关紧要，因为我们通过使用开源 OTL 的 C++ 遇到了同样的问题（幸运的是，不是我的部门）。

好的，问题来了……

从开始到网格填充的时间约为 35-40 秒，哎呀。

但是，如果我执行以下操作：

GRANT SELECT ON MyTable TO TEST_ROLE
/

然后再次执行查询，则需要大约 5-6 秒。

在我看来，这与特权等有关，但我不太确定为什么它仍然实际上是双向的？

只是为了将其他东西扔进锅里，如果我将过程更改为

SELECT ixMyTable, TO_CLOB(TO_NCLOB(clobData))
FROM MyTable;

那么时间约为 5-6 秒，无论有或没有额外的 SELECT 权限。

任何指示或直接的解决方案将不胜感激！

编辑：

操作系统是 Vista x86 Business

Oracle 服务器是 10.2.0.1

Oracle 客户端是 10.2.0.3

编辑：

按照建议，我尝试从 MS OracleClient 更改为 ODP.NET，这确实根据需要加快了速度。

不幸的是，受影响的 C# 应用程序只是一个内部应用程序，用于查看表/运行 SPROCS 等。

我们的主要交付成果是使用 OTL 的 C++ 应用程序 (http://otl.sourceforge.net/otl3_intro.htm) 用于数据库访问。 这并不是真正可以改变的东西，所以我真的很想了解差异的原因，而不必随意地抛出无缘无故的 GRANT SELECT。

如果缺乏 SELECT 权限导致完全失败，那么我可能会忍受这种情况，但缺乏 SELECT 似乎会导致访问 CLOB 数据的某些较慢的路径。

我已经标记了 3 个答案 - 感谢这些 - 但我真的可以这样做有一个理由，所以我会为此添加赏金。

PS 我们一开始确实想在 C++ 中使用 OCCI，但由于 Oracle 始终支持当前版本的 IDE，因此我们无法让它与 Visual Studio 2008 很好地配合。

Answer 1

您确定每次都从磁盘读取 blob，而不是从磁盘缓存中读取第二次及后续的 blob 吗？

我在性能测试中看到过这个问题，特别是在 Oracle 上，第一次运行测试很糟糕。 然后，通过一个微小的（看似微不足道的变化），性能突然显着提高。 但实际上发生的情况是，您正在查询的数据已加载到缓存中，并且可以以 10 倍或 20 倍的速率（内存与磁盘）进行访问。

执行此测试的正确方法是在查询运行之间弹跳数据库。 如果 DBA 不允许您为该测试退回测试生产服务器，请在您的计算机上加载 Oracle XE 的副本。

编辑：或者更好：每次删除并重新创建表。 您可能正在这样做，但没有提及。

Answer 2

我会尝试 odp.net http://www.oracle.com/ technology/tech/windows/odpnet/index.html 而不是 System.Data.OracleClient。

Answer 3

根据上述建议，您可以尝试不同的 ODBC 驱动程序或客户端软件。 事实上，TO_CLOB(TO_NCLOB()) 运行速度如此之快，而直线似乎并不表明问题就在那里。

首先，我将获取两个查询并通过 SQLDeveloper 运行它们并获得解释计划。 这应该为您在 Oracle 端提供执行基准。 添加转换不应对执行路径产生任何影响。 交替运行查询并计时以查看它们的速度。 如果没有区别的话，我建议是你的客户端软件的问题。

如果我的假设是正确的，这也可以解释 GRANT SELECT 更改。 客户端软件正在对 CLOB 进行某种昂贵的转换。 授予和/或显式转换允许客户端避免这种情况。 我不知道为什么。

Answer 4

我知道这是一个非常老的问题，

这是我根据 Oracle OCCI 库的经验所说的，但我相信这适用于任何 Oracle 客户端。 blob/clob 读取操作缓慢的原因如下：

1. 从具有 blob/clob 列的表中进行选择会阻止预取。 您可以在 sqlplus 中使用 set arraysize 1 & 进行实验。 对于带有 & 的表，设置 arraysize 5000 没有 blob/clob 列。 对于包含大量记录且没有 blob/clob 列的表，arraysize 将产生很大的差异。 对于具有 blob/clob 列的表，这不会有太大区别。 因此读取每条记录都会与服务器进行一次往返。 在速度较慢的网络（例如 WAN）上，这会产生很大的影响
2. 获取 blob/clob 列需要获取定位器和索引。 数据分开。 所以这是到服务器的 2 次往返。 现在将其与 #1 结合起来，您可以看到为什么这很慢

请参见此处。 https://docs.oracle.com/cd/ E18283_01/appdev.112/e10646/oci07lob.htm#CHDDHFAB

您可以通过启用 LOB 行预取来缓解此问题。 https： //docs.oracle.com/en/database/oracle/oracle-database/12.2/jajdb/index.html?oracle/jdbc/OracleStatement.html

我从来没有让这个适用于 OCCI，但实现了一个解决方法这里提到（有没有办法在奥奇？）

Answer 5

老实说，我认为这里的问题是 OTL 驱动程序和 OracleClient 在处理来自 SYS_REFCURSOR 的 CLOB 时遇到问题。 SYS_REFCURSOR 很弱，这意味着游标本身可以返回任何类型的数据，这意味着驱动程序需要不断返回数据库并查询游标的元数据（即获取游标中的数据类型），然后加载 CLOB 流，然后加载数据。 显然，如果执行查询的用户有权访问正在查询的表，则驱动程序可以更好地检索元数据并返回正确的数据。

更改存储过程以返回强 ref_cursor 是否有效？

CREATE OR REPLACE PACKAGE PKGTEST
    AS
    TYPE C_DoSomething IS REF CURSOR RETURN MyTable%ROWTYPE;
    PROCEDURE DoSomething(
        cur_OUT OUT c_DoSomething
     );
END PKGTEST;

您可以更改存储过程来执行TO_CLOB()，因为这似乎也有效吗？

Answer 6

您的数据库的 NLS_CHARACTERSET 和 NLS_NCHAR_CHARACTERSET 是什么。 运行

select * from nls_database_parameters;

即可得到结果。 您的客户端软件的 NLS_LANG 设置是什么？

这可能会让您对问题有更多的了解，并有助于回答 TO_CLOB(TO_NCLOB()) 调用的成本有多高的问题。

Answer 7

这些不一定都是直接相关的，但您可能应该检查每一个，以防它们相关。 我怀疑这与缓存相关：完成第一个查询后，您将应用选择，然后速度会很快。 在中间，如果您想正确地对其进行性能测试，则必须反弹服务器以摆脱缓存。 如果您进行此测试并且它突然表现得更好，那么请尝试将表固定到缓存中。 请参阅下面有关内联 clob 存储的内容，因为这可能是相关的。

大约一年前，我在 Oracle 10g 中遇到了有关 clob 性能的问题。 一旦我们得到了出色的 dba 的帮助，我们就解决了其中的大部分问题。 大约花了两个月的时间才让性能达到足够的速度。

您使用的是哪个版本的 Oracle？
在 Oracle 10g（早期版本）中，clob 性能存在大量问题。 事实上，在某些情况下，仅使用两个表和一个 varchar 列实际上更快（将 varchar 连接在一起，就得到了 clob）。 我们升级到了更高的版本，它好多了

另外您的数据存储在哪里？
还有一个选项可以将 clob 存储在表本身中。 根据您的数据有多大，您可能会发现这有助于提高性能。
如果您将数据存储在 SAN 上，那么还值得查看 SAN 上的缓存大小以及块大小。 当缓存大小不正确时，Oracle + SAN 可能会有点有趣。

另一个解决方法：
如果您发现持久性很慢，甚至访问很慢，并且不受 CPU 限制，请压缩数据并将其存储在 blob 中。 我们在这里也看到了巨大的性能优势。

如果您在处理 clob 的任何过程中看到性能问题（与内存相关？），我们发现我们会将对象重新创建为新字符串。 即使数据较小，驱动程序也会预先创建 32K 大小的字符串。

我确实想知道系统表是否可能存在碎片？ 有很多表/模式吗？ 那么同义词呢？

另外，当您存储 Clob 时，它们不会被存储到 Oracle 中的一个大文件中吗？ 如果我没记错的话，你必须小心碎片； 存储不会被释放以供重用。

也许您可以在数据库前面放置一个 .NET Web 服务？ 如果您无法解决性能问题，这可能是一种选择。