时间戳

首页 > 解决方案 > Oracle：在过程中返回带有光标的大型数据集

oracle - Oracle：在过程中返回带有光标的大型数据集

问题描述

我看过很多关于在 PL/SQL 中使用游标将数据返回到调用应用程序的帖子，但没有一篇文章涉及我认为我使用这种技术遇到的问题。我对 Oracle 还很陌生，但对 MSSQL Server 有丰富的经验。在 SQL Server 中，当构建由应用程序调用以返回数据的查询时，我通常将 SELECT 语句放在带/不带参数的存储过程中，并让存储过程执行语句并自动返回数据。我了解到，使用 PL/SQL，您必须将生成的数据集存储在游标中，然后使用游标。

我们有一个查询不一定返回大量行（~5K - 10K 行），但是数据集非常宽，因为它由 1400 多列组成。在 SQL Developer 中运行 SQL 查询本身会立即返回结果。但是，调用为同一查询打开游标的过程需要 5 分钟以上才能完成。

CREATE OR REPLACE PROCEDURE PROCNAME(RESULTS OUT SYS_REFCURSOR)
AS
BEGIN    
    OPEN RESULTS FOR
    <SELECT_query_with_1400+_columns>
    ...
END;

在进行了一些调试以尝试找到缓慢的根本原因之后，我倾向于非常缓慢地一次返回一行的光标。DBMS_SQL.return_result(RESULTS)我实际上可以通过将 proc 代码转换为 PL/SQL 块并在 SELECT 查询之后使用来实时看到这一点。运行此程序时，我可以在 SQL Developer 的脚本输出窗口中看到每一行一次显示一个。如果这正是游标将数据返回给调用应用程序的方式，那么我绝对可以看到这是瓶颈，因为完成返回所有 5K-10K 行可能需要 5-10 分钟。如果我从 SELECT 查询中删除列，游标会更快地显示所有行，因此使用游标似乎确实存在大量列的问题。

知道运行 SQL 查询本身会返回即时结果，我如何才能从游标中获得相同的性能？这似乎是不可能的。答案是将嵌入式 SQL 放在应用程序代码中，而不是在这种情况下使用过程/游标返回数据？我们在我们的环境中使用 Oracle 12c。

编辑：只想解决我如何使用常规 SELECT 查询与带有游标方法的 PL/SQL 块测试性能：

SELECT（需要约 27 秒才能返回约 6K 行）：

SELECT <1400+_columns>
FROM <table_name>;

带游标的 PL/SQL（需要约 5-10 分钟才能返回约 6K 行）：

DECLARE RESULTS SYS_REFCURSOR;
BEGIN
    OPEN RESULTS FOR
    SELECT <1400+_columns>
    FROM <table_name>;

    DBMS_SQL.return_result(RESULTS);
END;

一些评论引用了在返回所有数据后控制台应用程序中发生的情况，但我只谈论上述两种方法在 Oracle\SQL Developer 中的性能。希望这有助于澄清我试图传达的观点。

标签： oracleperformanceplsqloracle-sqldeveloper

解决方案

您可以为 SQL 的两次执行运行 SQL Monitor 报告；这将向您准确显示时间花费在哪里。我还会考虑在单独的快照间隔中运行这两种方法，并检查 AWR 差异报告和 ADDM 比较报告的输出；您可能会对这些比较报告提供的惊人细节感到惊讶。此外，即使根据 Oracle 的说法，表中 > 255 列是“不可以”，因为它会将您的记录分成 > 1 个数据库块，从而增加检索结果所需的 IO 时间，我怀疑两者的差异您看到的方法不是 IO 问题，因为在直接 SQL 中，您报告快速获取所有结果。因此，我怀疑更多的是内存问题。你可能知道，PL/SQL 代码将使用程序全局区域 (PGA)，因此我会检查参数 pga_aggregate_target 并将其提高到 5 GB（只是猜测）。在代码运行的时间间隔内运行的 ADDM 报告将告诉您顾问是否建议更改该参数。

推荐阅读