SQLDeveloper 与应用程序的 Oracle 性能比较
我试图了解我在 Oracle 中编写的查询的性能。此时我只能访问 SQLDeveloper 及其执行计时器。我可以运行 SHOW PLAN，但无法使用自动跟踪功能。
当我在 SQLDeveloper 中按“执行查询”(F9) 时，我编写的查询运行大约 1.8 秒。我知道默认情况下仅获取前 50 行，但我至少可以确定 1.8 秒包含总执行时间加上将前 50 行传递给客户端的时间吗？
当我将此查询包装在存储过程中（通过 OUT REF CURSOR 返回结果）并尝试从外部应用程序 (SQL Server Reporting Services) 使用它时，查询需要一分钟多的时间才能运行。当我在 SQLDeveloper 中按“运行脚本”(F5) 时，我得到了类似的性能。这里的区别似乎在于，在这两种情况下，Oracle 必须传回所有行而不是前 50 行。这使我相信客户端 PC 和 Oracle 实例之间存在一些网络连接问题。
我的查询仅返回大约 8000 行，因此这个性能令人惊讶。为了尝试证明我上面关于延迟的理论，我在 SQLDeveloper 中运行了一些类似这样的代码：
declare
tmp sys_refcursor;
begin
my_proc(null, null, null, tmp);
end;
...并且运行时间大约为两秒。再说一遍，SQLDeveloper的执行时钟是否准确地指示了查询的执行时间？或者我是否遗漏了一些东西，是否有可能实际上是我的查询需要调整？
任何人都可以根据我可用的有限工具向我提供对此的任何见解吗？或者我应该尝试让 DBA 来做一些进一步的分析？
I am trying to understand the performance of a query that I've written in Oracle. At this time I only have access to SQLDeveloper and its execution timer. I can run SHOW PLAN but cannot use the auto trace function.
The query that I've written runs in about 1.8 seconds when I press "execute query" (F9) in SQLDeveloper. I know that this is only fetching the first fifty rows by default, but can I at least be certain that the 1.8 seconds encompasses the total execution time plus the time to deliver the first 50 rows to my client?
When I wrap this query in a stored procedure (returning the results via an OUT REF CURSOR) and try to use it from an external application (SQL Server Reporting Services), the query takes over one minute to run. I get similar performance when I press "run script" (F5) in SQLDeveloper. It seems that the difference here is that in these two scenarios, Oracle has to transmit all of the rows back rather than the first 50. This leads me to believe that there is some network connectivity issues between the client PC and Oracle instance.
My query only returns about 8000 rows so this performance is surprising. To try to prove my theory above about the latency, I ran some code like this in SQLDeveloper:
declare
tmp sys_refcursor;
begin
my_proc(null, null, null, tmp);
end;
...And this runs in about two seconds. Again, does SQLDeveloper's execution clock accurately indicate the execution time of the query? Or am I missing something and is it possible that it is in fact my query which needs tuning?
Can anybody please offer me any insight on this based on the limited tools I have available? Or should I try to involve the DBA to do some further analysis?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
不，是返回前 50 行的时间。它不一定要求数据库确定整个结果集。
将表视为百科全书。如果您想要名称开头的动物列表使用“A”或“Z”，您可能会很快获得 Aardvarks 和 Alligators，而获得 Zebras 则需要更长的时间，因为如果您的查询正在进行全表扫描，那么您将需要更长的时间。在读完整个表格（或书）之前不会完成，即使在第一章之后没有任何内容可供选择（因为它不知道其中没有任何重要的内容，直到它读完它）。
更具体地说，它将解析查询以确定必要的表、列和权限是否到位。它不会实际执行查询或确定是否有任何行满足过滤条件
。该查询仅返回 8000 行，网络不太可能是一个重大问题（除非它们是非常大的行）。
向您的 DBA 索取有关性能调优的快速教程。
No, it is the time to return the first 50 rows. It doesn't necessarily require that the database has determined the entire result set.
Think about the table as an encyclopedia. If you want a list of animals with names beginning with 'A' or 'Z', you'll probably get Aardvarks and Alligators pretty quickly. It will take much longer to get Zebras as you'd have to read the entire book. If your query is doing a full table scan, it won't complete until it has read the entire table (or book), even if there is nothing to be picked up in anything after the first chapter (because it doesn't know there isn't anything important in there until it has read it).
This piece of code does nothing. More specifically, it will parse the query to determine that the necessary tables, columns and privileges are in place. It will not actually execute the query or determine whether any rows meet the filter criteria.
If the query only returns 8000 rows it is unlikely that the network is a significant problem (unless they are very big rows).
Ask your DBA for a quick tutorial in performance tuning.