Oracle:在过程中返回带游标的大型数据集

Question

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

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

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

在做了一些调试，试图找到缓慢的根本原因后，我倾向于光标，一次返回一行非常慢。实际上，通过将proc代码转换为PL/SQL块并在SELECT查询之后使用DBMS_SQL.return_result(RESULTS)，我可以看到这种实时情况。在运行这个程序时，我可以看到每行都显示在的脚本输出窗口中。如果游标正是这样将数据返回给调用应用程序的，那么我肯定会看到这是一个瓶颈，因为返回所有5K-10K行可能需要5-10分钟。如果从SELECT查询中删除列，游标显示所有行的速度要快得多，因此，使用游标显示大量列确实是一个问题。

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

编辑:只想说明如何使用常规的SELECT查询来测试性能，而使用带有游标的PL/SQL块：

选择(返回~6K行需要27秒)：

SELECT <1400+_columns>
FROM <table_name>;

带有游标的PL/SQL (返回~6K行需要花费5-10分钟)：

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

    DBMS_SQL.return_result(RESULTS);
END;

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

Answer 1

您可以为SQL的两次执行运行spent报告；这将显示所花费时间的确切位置。我还会考虑在单独的快照间隔中运行这两种方法，并检查来自AWR差异报告和ADDM比较报告的输出；您可能会对这些比较报告提供的惊人细节感到惊讶。此外，尽管根据Oracle的说法，表中> 255列是"no-no“，因为它将在>1个数据库块中分割您的记录，从而增加了检索结果所需的IO时间，但我怀疑您所看到的两种方法的差异并不是IO问题，因为在直接SQL中，您报告的结果都是快速的。因此，我怀疑更多的是记忆问题。您可能知道，PL/SQL代码将使用Program (PGA)，因此我将检查参数pga_aggregate_target并将其放大到5GB(只是猜测)。当代码运行时，ADDM报告将告诉您顾问是否建议更改该参数。