Oracle存储过程中，返回参考光标VS关联数组 [英] Oracle stored procedure, returning ref cursor vs associative arrays

问题描述

我们的DBA需要我们返回来自存储过程的所有表格数据一组关联数组，而不是使用REF CURSOR这是我在网络上大多数例子中看到。他说，这是因为它的速度要快得多甲骨文做事这种方式，但由于需要对数据进行循环过两次，一次是在存储过程，然后再在应用程序时，它的进程似乎直觉上我。此外，值往往需要从他们的本地类型浇铸为varchar所以它们可以被存储在数组中，然后浇铸背面上的应用侧。使用这种方法还使得难以使用ORM工具，因为它们似乎希望在大多数情况下，引用游标

Our DBA requires us to return all tabular data from stored procedures in a set of associative arrays rather than using a ref cursor which is what I see in most examples on the web. He says this is because it is much faster for Oracle to do things this way, but it seems counter intuitive to me because the data needs to be looped over twice, once in the stored procedure and then again in the application when it is processes. Also, values often need to be casted from their native types to varchar so they can be stored in the array and then casted back on the application side. Using this method also makes it difficult to use orm tools because they seem to want ref cursors in most cases.

的存储过程的一个例子是：

An example of a stored procedure is the following:

PROCEDURE sample_procedure (
                                p_One       OUT varchar_array_type,
                                p_Two       OUT varchar_array_type,
                                p_Three     OUT varchar_array_type,
                                p_Four      OUT varchar_array_type
                            )
IS
p_title_procedure_name        VARCHAR2(100) := 'sample_procedure';
v_start_time DATE :=SYSDATE;    

CURSOR cur
  IS
    SELECT e.one, e.two, e.three, e.four FROM package.table 
    WHERE filter='something';

    v_counter PLS_INTEGER := 0;
BEGIN

    FOR rec IN cur LOOP
        BEGIN
            v_counter := v_counter + 1;
            p_One(v_counter) := rec.one;
            p_Two(v_counter) := rec.two;
            p_Three(v_counter) := rec.three;
            p_Four(v_counter) := rec.four;
        END;
    END LOOP;
END;

光标用于填充一个阵列为每列返回。我试图找到支持他的主张，这是法快，但一直未能如愿的信息。任何人都可以填补我对他为什么要我们（的.NET开发人员）来编写这样的存储过程？

The cursor is used to populate one array for each column returned. I have tried to find information supporting his claim that this is method faster but have been unable to do so. Can anyone fill me in on why he might want us (the .net developers) to write stored procedures in this way?

推荐答案

DBA的要求没有意义。

The DBA's request does not make sense.

什么是DBA是几乎可以肯定的想法是，他要尽量减少SQL到去当你从游标中读取数据的PL / SQL引擎方面的变化的数量。但是正被建议在这个特定问题是针对性很差，将溶液引入在大多数系统中的其他更严重的性能问题。

What the DBA is almost certainly thinking is that he wants to minimize the number of SQL to PL/SQL engine context shifts that go on when you're fetching data from a cursor. But the solution that is being suggested is poorly targetted at this particular problem and introduces other much more serious performance problems in most systems.

在Oracle中，当PL / SQL VM询问SQL VM更多的数据出现的SQL PL / SQL上下文转移，SQL VM通过进一步执行该语句得到它，然后包起来，双手回数据响应到PL / SQL VM。如果PL / SQL引擎被要求行一次，你就获取了很多行，很可能这些方面的变化可能是你的整体运行的显著部分。为了解决这个问题，甲骨文至少在8i的天推出批量操作的概念了。这使得PL / SQL VM在从SQL VM时要求多行。如果PL / SQL VM同时请求100行，你已经消除了环境的变化，99％和你的code可能运行得更快。

In Oracle, a SQL to PL/SQL context shift occurs when the PL/SQL VM asks the SQL VM for more data, the SQL VM responds by executing the statement further to get the data which it then packages up and hands back to the PL/SQL VM. If the PL/SQL engine is asking for rows one at a time and you're fetching a lot of rows, it is possible that these context shifts can be a significant fraction of your overall runtime. To combat that problem, Oracle introduced the concept of bulk operations back at least in the 8i days. This allowed the PL/SQL VM to request multiple rows at a time from the SQL VM. If the PL/SQL VM requests 100 rows at a time, you've eliminated 99% of the context shifts and your code potentially runs much faster.

一旦被引入批量操作，有很多code的可能，为了进行重构是通过显式使用 BULK COLLECT 操作，而不是获取更高效行由行，然后用 FORALL 循环来处理这些集合的数据。由10.2天，但是，甲骨文整合批量操作到隐的循环所以现在一个隐含的的循环自动批量收集在100批次而不是读取行逐行

Once bulk operations were introduced, there was a lot of code that could be refactored in order to be more efficient by explicitly using BULK COLLECT operations rather than fetching row-by-row and then using FORALL loops to process the data in those collections. By the 10.2 days, however, Oracle had integrated bulk operations into implicit FOR loops so an implicit FOR loop now automatically bulk collects in batches of 100 rather than fetching row-by-row.

在你的情况，但是，因为您传回数据到客户端应用程序，使用批量操作是显著要少得多。任何像样的客户端API将不得不功能，允许客户端指定多少行需要从光标在每个网络往返获取那些取请求将直接进入SQL VM，无法通过PL / SQL VM，所以没有SQL向PL / SQL上下文转移到担心。您的应用程序担心在获取行适当数量的每一轮-trip--足够的应用程序不会变得过于繁琐和瓶颈在网络上，但没有这么多，你必须等待很长时间，结果是退回或过多的数据存储在存储器

In your case, however, since you're returning the data to a client application, the use of bulk operations is much less significant. Any decent client-side API is going to have functionality that lets the client specify how many rows need to be fetched from the cursor in each network round-trip and those fetch requests are going to go directly to the SQL VM, not through the PL/SQL VM, so there are no SQL to PL/SQL context shifts to worry about. Your application has to worry about fetching an appropriate number of rows in each round-trip-- enough that the application doesn't become too chatty and bottleneck on the network but not so many that you have to wait too long for the results to be returned or to store too much data in memory.

返回的PL / SQL集合，而不是一个REF CURSOR到客户端应用程序是不会减少发生上下文移位数。但是，它是将有一大堆其他方面的不足而不是其中最重要的是内存使用情况。 PL / SQL集合已被完全存储在数据库服务器上的过程中全局区（PGA）（假设专用服务器连接）。这是一个内存块具有被从服务器的RAM中分配。这意味着服务器将不得不分配内存中获取每一个最后一排，每个客户端请求。这反过来，将会极大地限制了应用程序的可扩展性，并根据数据库配置，可以从Oracle数据库的其他部分，这将是在提高应用程序性能非常有用偷RAM的路程。如果你用完了PGA的空间，你的会议将开始获取内存相关的错误。即使在纯粹的PL / SQL的应用程序，你绝不会希望所有的数据放到集合，你总是希望它在小批量抓取，以尽量减少你使用的PGA量。

Returning PL/SQL collections rather than a REF CURSOR to a client application isn't going to reduce the number of context shifts that take place. But it is going to have a bunch of other downsides not the least of which is memory usage. A PL/SQL collection has to be stored entirely in the process global area (PGA) (assuming dedicated server connections) on the database server. This is a chunk of memory that has to be allocated from the server's RAM. That means that the server is going to have to allocate memory in which to fetch every last row that every client requests. That, in turn, is going to dramatically limit the scalability of your application and, depending on the database configuration, may steal RAM away from other parts of the Oracle database that would be very useful in improving application performance. And if you run out of PGA space, your sessions will start to get memory related errors. Even in purely PL/SQL based applications, you would never want to fetch all the data into collections, you'd always want to fetch it in smaller batches, in order to minimize the amount of PGA you're using.

在另外，所有数据读取到存储器将会使应用程序感到慢得多。几乎所有的框架将允许您根据需要它，这样，例如，如果你有你在的每25行网页上显示一个报告，你的应用程序只需要画前，取前25行获取数据第一个屏幕。它永远也不会获取下一个25行，除非用户碰巧请求结果的下一页。如果你获取数据到数组喜欢你的DBA提出，但是，你将不得不获取所有行之前，应用程序可以开始显示第一行即使用户从来不愿意看到比第一更屈指可数行。这将意味着在数据库服务器上有更多的I / O读取所有的行，在服务器上更PGA，在应用服务器上更多的内存来缓冲的结果，并为网络更长时间的等待。

In addition, fetching all the data into memory is going to make the application feel much slower. Almost any framework is going to allow you to fetch data as you need it so, for example, if you have a report that you are displaying in pages of 25 rows each, your application would only need to fetch the first 25 rows before painting the first screen. And it would never have to fetch the next 25 rows unless the user happened to request the next page of results. If you're fetching the data into arrays like your DBA proposes, however, you're going to have to fetch all the rows before your application can start displaying the first row even if the user never wants to see more than the first handful of rows. That's going to mean a lot more I/O on the database server to fetch all the rows, more PGA on the server, more RAM on the application server to buffer the result, and longer waits for the network.

这篇关于Oracle存储过程中，返回参考光标VS关联数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！