.Net 中的 Oracle Datareader - 速度太慢（通过网络）
在我收到“您尝试过 ODP.net 吗？”之前答案是，是的，我已经并且现在正在使用它。
我正在将数据从 oracle 移动到 sql server（不重要），并在 oracle 连接上使用数据读取器。较大的桌子正在爬行。有时甚至每秒 10 条记录。当我注意到性能问题时，我将源代码减少到仅对整个表执行简单的 Reader.Read() 调用，因此没有其他因素会减慢速度。我尝试过 MS 和 Oracle ODP .net 客户端。我目前正在使用 11g Instant Client、win7 64 位上的 64 位、8 GB 内存和所有好东西。我在本地网络上使用过，目前在VPN上，性能基本相同。我已经调整了预取大小，但没有结果。
我可以在 Oracle Sql DEveloper 工具中运行导出数据功能，并在同一台机器上通过同一网络以大约 100 倍的速度导出整个数据库的所有数据。
我可以将我的 .net 应用程序复制到 oracle 服务器并对其运行相同的性能测试，它会在不到一秒的时间内完成。
所以，这不是网络本身慢，也不是数据量（如 SqlDeveloper 导出所示），也不是 .net 代码本身，也不是 oracle 数据库（如在服务器上运行所示），所以它必须是在任何网络上使用的 Datareader 的某种组合。
这是我的即时客户端安装吗？成熟的客户端表现更好吗？ 64 位客户端搞砸了？实在是亏大了。
更新：
此后我运行了相同的应用程序，编译为 32 位，并在安装了“完整”oracle 客户端（显然是 32 位版本）的 Windows XP 虚拟 PC 实例上运行。即使虚拟机的性能有所降低，它的运行速度仍然快了近 10 倍。所以，肯定是 Instant 客户端存在某种问题，我猜特别是 64 位 Instant 客户端。确认这一点的最后一个测试是在同一台计算机上安装 32 位即时客户端并再次运行它。如果我能找到时间...
Before I get the "have you tried ODP.net?" answers, yes I have and am using it now.
I'm moving data from oracle to sql server (not important) and am using a datareader on the oracle connection. Larger tables are CRAWLING. Sometimes as bad as 10 records per second. When I noticed the performance issues, I reduced my source code down to just doing simple Reader.Read() calls over the whole table, so nothing else is slowing it. I've tried both the MS and the Oracle ODP .net clients. I'm currently using the 11g Instant Client, 64bit on win7 64bit, 8 gigs ram and all the goodies. I've used it on the local network, and am currently ona VPN and the performance is basically the same. I've tuned the Prefetch sizes with no results.
I can run the Export Data function in the Oracle Sql DEveloper tools, and export all the data for the entire database, on this same machine, over the same network, at about 100 times the speed.
I can copy my .net app to the oracle server and run the same performance test on it, and it finishes in less than a second.
So, it's not the network itself being slow and it's not the quantity of data (as demonstrated by SqlDeveloper export), and it's not the .net code itself nor is it the oracle db (as demonstrated by running it on the server), so it has to be some combination of Datareader used over any network.
Is it my instant client install? Does the full blown client perform better? 64 bit client messing things up? Really at a loss.
UPDATE:
I've since run the same app, compiled for 32 bit, and run on a virtual pc instance of windows xp which has the "full" oracle client installed (32 bit version, obviously). Even with the reduced performance of a VM, it still ran almost 10 times faster. So, definitely some sort of problem with the Instant client, and my guess specifically the 64 bit Instant Client. The last test to confirm this would be to install a 32 bit instant client on this same machine and run it again. If i can find the time...
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
“我可以将 .net 应用程序复制到 Oracle 服务器并对其运行相同的性能测试，不到一秒即可完成。”
这是您解决方案的根源。同一应用程序（测试中的受控元素）从一个位置移动到另一个位置，唯一发生变化的变量是网络。那么我们有两种可能性。可能一是网络本身有问题，网速太慢。第二个是应用程序及其与网络的接口方式与其性能相悖。
当然，VPN 中服务器上的执行与您所在位置的执行会存在偏差。使用服务器上的导出功能（尽可能接近原始访问）应该允许您根据在 VPN 连接的主机上执行相同操作所需的时间长度差异来测量网络差异分量。
但是，正如您所指出的，这不能解释整个时差。应用程序可能对 WAN 不友好。这通常意味着发送和接收信息需要太多轮次，并且每个数据流中的信息量可能非常大，超过了所需的量。从批量导出到应用程序的底层握手机制可能完全不同。一个可能会一次请求 100 条记录，而另一个可能会按顺序提取每条记录（注意速度上的差异为 100:1）。每次拉取单个记录时不断地应用数据库握手很可能会大大增加您的开销并导致吞吐量下降。
"I can copy my .net app to the oracle server and run the same performance test on it, and it finishes in less than a second."
This is the root of your solution. Same app, a controlled element in the test, is moved from one location to another and the only variable which changes is the network. So then we have two possibilities. Possibility one is that the network itself is the problem, that it is too slow. The second is that the application and how it is interfacing with the network is antagonistic to its performance.
Certainly there will be a deviation from execution on the server vs your location across VPN. Using the export function on the server, which is as close to raw access as you can get, should allow you to measure the network variance component based upon the difference in the length of time required to perform the same action on your VPN Connected host.
But, as you noted, this cannot account for the enture time difference. Applications can be WAN unfriendly. What this typically amounts to is that too many turns are required to send and receive information and the amount of information in each data flow can be substantially large, more than what is needed. It is possible that the underlying handshake mechanism from your bulk export to your application is completely different. One may be requesting 100 records at a time, while the other may be pulling each and every record in sequence (noting your difference of 100:1 in speed). The constant application to database handshake for the single record pull at a time could well be adding substantially to your overhead and resulting in the drop in throughput.
尝试增加提取大小 。也就是说，ODP.net 越高，实际获取数据所需的往返次数就越少，网络性能就越好。 （请注意，据我所知，如果您启用了连接池，则会自动完成。）
Try increasing the Fetch Size. The higher that is, the less round trips ODP.net will have to make in order to actually get the data, and the better performance over the network will be. (Note that AFAIK if you have connection pooling enabled this is done automatically.)
oomhauer：你查到真相了吗？我也遇到类似的问题。通过使用 Fetchsizes，我确实得到了一些小小的提升。
在比产品小几个数量级的测试环境中，我在 ODP.net 中看到 1.8 秒，在 System.Data.OracleClient 中看到 0.2 秒。
通过提高 fetchsizes 我能得到的最好结果是 1.6 秒。
boomhauer: did you get to the bottom of this? I'm experiencing similar issues. I did get a slight boost by playing with Fetchsizes.
In my test environment which is orders of magnitude small than prod I'm seeing 1.8 seconds in ODP.net and 0.2 seconds with System.Data.OracleClient.
The best I can get by bumping up fetchsizes is 1.6 seconds.
我遇到了与此类似的问题，但它是间歇性的。我有一个 Oracle 查询，通常需要大约 1 秒才能运行，但有时在午餐时间使用 .Net 需要 30 秒才能运行。如果此时我在同一台机器上使用 Oracle SQL Developer 运行它，仍然需要 1 秒。
这个问题每天只持续大约10分钟。我的同事也在同一时间遇到同样的问题，并且同时消失。
所以看来它可能是网络和.Net Oracle 驱动程序的组合，但我不知道如何找到它。另外，其他 SQL 查询此时执行速度并不慢，只是一个特定查询仅返回 1200 行。
编辑：我发现另一位同事在速度变慢时正在通过网络进行大型文件复制。为了证明这一点，我让他再做一次，同样的事情发生了 - 但 Oracle SQL Developer 中的查询仍然很快。所以它是网络和.Net Oracle驱动程序的结合。顺便说一句，我使用的是 64 位。
I'm having a similar problem to this but it is intermittent. I have an Oracle query that normally takes about 1 second to run but sometimes around lunchtime it takes 30 seconds to run using .Net. If at this time I run it using Oracle SQL Developer on the same machine it still takes 1 second.
This problem only lasts for about 10 mins per day. Also my colleague gets the same problem at exactly the same time and it goes away at the same time.
So it seems that it might be a combination of the network and the .Net Oracle driver but I don't know how to find it. Also other SQL queries do not perform slowly at this time, just one particular query that only returns 1200 rows.
EDIT: I found out a another colleague was doing a large file copy over the network at the time of the slow down. To prove it I got him to do it again and the same thing happened - but the query was still fast in Oracle SQL Developer. So it is a combination of the network and the .Net Oracle driver. I'm using 64 bit BTW.