为什么 ADF 和 SQL Developer 中运行的查询的执行时间有如此大的差异
我的 JDeveloper ADF Web 应用程序中运行的查询遇到一个奇怪的问题。它是一个向 Oracle 10g 数据库发出 select 语句的简单搜索表单。提交搜索后，ADF 框架（首先）运行查询，然后（其次）运行包含在“select count(1) from (...query...)”中的相同查询- 这里的目标是获取总行数，并显示“下 10 个结果”导航控件。
到目前为止，一切都很好。问题来自于我从第二个查询（其中包含“count(1)”的查询）中获得的令人震惊的性能。为了调查这个问题，我在 SQL Developer 中复制/粘贴/运行查询，并惊讶地看到更好的响应。
在比较 ADF 和 SQL Developer 中的查询执行时，我采取了所有措施来确保两种执行的代表性环境： - 刚刚重新启动的数据库 - OC4J 相同 这样我就可以确定差异与缓存和/或缓冲无关，在这两种情况下数据库和应用程序服务器都是新（重新）启动的。
我对两个会话进行的跟踪说明了这种情况：
在 ADF 中运行的查询：
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.97 0.97 0 0 0 0
Fetch 1 59.42 152.80 35129 1404149 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 60.39 153.77 35129 1404149 0 1
在 SQL Developer 中运行相同的查询：
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 1.02 1.16 0 0 0 0
Fetch 1 1.04 3.28 4638 4567 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 2.07 4.45 4638 4567 0 1
预先感谢您的任何意见或建议！
I have a strange issue with a query running in my JDeveloper ADF web application. It is a simple search form issuing a select statement to Oracle 10g database. When the search is submitted, ADF framework is (first) running the query, and (second) running the same query wrapped within "select count(1) from (...query...)" - the goal here is to obtain the total number of rows, and to display the "Next 10 results" navigation controls.
So far, so good. Trouble comes from the outrageous performance I am getting from the second query (the one with "count(1)" in it). To investigate the issue, I copied/pasted/ran the query in SQL Developer and was surprised to see much better response.
When comparing the query execution in ADF and SQL Developer, I took all measures to ensure representative environment for both executions:
- freshly restarted database
- same for the OC4J
This way I can be sure that the difference is not related to caching and/or buffering, in both cases the db and the application server were freshly (re)started.
The traces I took for both sessions illustrate the situation:
Query ran in ADF:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.97 0.97 0 0 0 0
Fetch 1 59.42 152.80 35129 1404149 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 60.39 153.77 35129 1404149 0 1
Same query in SQL Developer:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 1.02 1.16 0 0 0 0
Fetch 1 1.04 3.28 4638 4567 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 2.07 4.45 4638 4567 0 1
Thanks in advance for any comments or suggestions!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
好吧，我终于找到了这种可怕行为的解释。长话短说，答案就在 JDeveloper 中 ViewObject 的定义（调整参数）中。我缺少的是这两个重要参数：
如果没有它们，会发生以下情况 - ADF 运行主查询、绑定变量并获取结果。然后，为了尝试估计行数，它启动了“
select count(1) from (my_query)”中包含的相同查询，但是......（鼓声）......不绑定变量！！！我真的很困惑，在不考虑绑定变量的实际值的情况下估计行数有什么用！无论如何，这一切都在 ViewObject 的定义中：为了获得预期的行为，需要设置以下设置：
执行计划无法帮助我（对于 ADF 和 SQL Developer 来说是相同的），差异仅在使用绑定获取的跟踪文件中可见。
所以，现在我的问题已经解决了 - 感谢所有的提示，最终让我找到了解决方案！
Ok, I finally found the explanation of this ghastly behaviour. To make the long story short, the answer is in the definition (Tuning parameters) of my ViewObject in JDeveloper. What I was missing were these two important parameters:
Without them, the following happens - ADF runs the main query, binds the variables and fetches the results. Then, in an attempt to make an estimate of the rowcount, it launches the same query enclosed in "
select count(1) from (my_query)", but ...(drum roll)... WITHOUT BINDING THE VARIABLES!!! It really beats me what is the use of estimating the rowcount without taking into account the actual values of the bind variables!Anyway, it's all in the definition of the ViewObject: the following settings needed to be set, in order to get the expected behaviour:
The execution plan could not help me (it was identical for both ADF and SQL Developer), the difference was only visible in a trace file taken with binds.
So, now my problem is solved - thanks to all for the tips that finally led me to the resolution!
带计数的查询速度较慢，因为它必须读取所有数据（对其进行计数）。
当您运行另一个查询时，您仅获取第一页数据，因此执行（从游标读取）可以在获得前十个结果后停止。
尝试使用第一个查询加载到第 100 页，它可能会比第一页慢得多。
如果在线选择计数成本太高，常见的技巧是选择比您需要的多一项（在您的情况下为 11）以确定是否有更多数据。您无法显示页数，但至少有一个“下一页”按钮。
更新：您是说计数查询仅在通过 ADF 运行时较慢，但通过 SQL Developer 运行时较快？
The query with count is slower because it has to read all the data (to count it).
When you run the other query, you are only fetching a first page of data, so the execution (reading from the cursor) can stop after you have your first ten results.
Try loading to 100th page with your first query, it will likely be much slower than the first page.
If selecting a count online is too expensive, a common trick is to select one item more than you need (11 in your case) to determine if there is more data. You cannot show a page count, but at least a "next page" button.
Update: Are you saying the count query is only slow when run through ADF, but fast through SQL Developer?
如果是同一个查询，我可以想到：
但是如果没有执行计划或 SQL，则很难说
If it is the same query, i can think of:
But without the execution plans or the SQL, it is hard to say
多年来，我发现“SELECT COUNT...”通常是意外减速的根源。
如果我理解上面发布的结果，则 JDeveloper 的查询需要 153 秒，而 SQL Developer 的查询只需要大约 4.5 秒，并且您将使用此查询来确定是否应显示“Next 10 Results”控件。
我不知道运行时间是 4.5 秒还是 153 秒是否重要 - 即使是最好的情况，初始化页面似乎也相当慢。假设从页面提交时，您可以在 4.5 秒内响应查询 - 当用户只需单击鼠标即可离开去做其他事情时，这仍然是一个很长的时间让用户坐下来等待。在同样的 4.5 秒内，应用程序可能能够获取足够的数据来加载页面几次。
我认为@Thilo 的想法是，获取比填充页面所需的多一条记录以确定是否有更多可用数据，这是一个很好的想法。也许这可以适应您的情况？
分享并享受。
Over the years I've found that "SELECT COUNT..." is often a source of unexpected slowdowns.
If I understand the results posted above, the query takes 153 seconds from JDeveloper, but only about 4.5 seconds from SQL Developer, and you're going to use this query to determine if the "Next 10 Results" control should be displayed.
I don't know that it matters if the runtime is 4.5 seconds or 153 seconds - even the best case seems rather slow for initializing a page. Assume for a moment that you can get the query to respond in 4.5 seconds when submitted from the page - that's still a long time to make a user sit and wait when they're only a mouse-click away from going off to do Something Else. In that same 4.5 seconds the app might be able to fetch enough data to load the page a few times.
I think @Thilo's idea of fetching one more record than is needed to fill the page to determine if there is more data available is a good one. Perhaps this could be adapted to your situation?
Share and enjoy.