Oracle 多久修改一次查询计划？
当为我们的数据处理应用程序运行性能基准测试时，我们从一个空的 FOO_TABLE 开始，然后从一个线程插入记录，而在另一个线程中，我们使用如下查询从处理中选择相同的记录：
select * from FOO_TABLE where ID > ?
结合：
stmt.setMaxRows(5000);
在 Java 中限制记录的数量在一个块中选择的记录。 （我们不想在这里使用 BETWEEN 因为 ID 有间隙）。我们继续处理 5000 个块，直到测试停止。
现在，我们的应用程序的性能随着时间的推移而降低，当我检查 Oracle 端发生的情况时，我惊讶地发现“select * from FOO_TABLE where ID > ?”的查询计划执行表扫描，而不是使用 ID 上的 PK 索引。
重新启动我们的应用程序（但没有截断表）后，Oracle 恢复理智并使用了 PK 索引。
所以，我的解释是，Oracle 认为在表几乎为空时扫描该表是个好主意，但随后从未修改此查询计划。这引出了我的问题：Oracle 多久修改一次查询计划？
是因为我重新启动了我们的应用程序吗？我对此有一些疑问，因为我们在 1 小时后回收池连接（因此没有连接可以早于 1 小时）。
是因为已经过了一定的时间了吗？
即使表几乎为空，您如何强制 Oracle 不进行扫描？
环境信息： - 甲骨文11g - jdbc 客户端 (java 6)
更新 10/25/2011：我对 Oracle 10g 进行了回归测试，问题是相同的，所以它既不是由动态游标共享引起的，也不是由动态游标共享修复的。正如马克最初提到的那样，除非发生结构性变化或重新计算表格统计数据等重大事件，否则该计划不会进行修订。
最终我添加了一个提示来强制 PK 访问，但我认为优化器应该能够解决这个问题。如果存在与搜索条件匹配的 PK，那么即使对于小表也可以使用（无论如何，性能差异微不足道）。
When running a performance benchmark for our data processing application we start with a FOO_TABLE empty and then insert records from one thread while in another thread we select the same records from processing using a query like:
select * from FOO_TABLE where ID > ?
in conjunction with:
stmt.setMaxRows(5000);
in Java to limit the number of records selected in one chunk. (We don't want to use BETWEEN here because the IDs have gaps). And we keep processing chunks of 5000 until the test is stopped.
Now, the performance of our application degrades over time and when I checked what happens on the Oracle side, I was surprised to notice that the query plan for "select * from FOO_TABLE where ID > ?" does a table scan instead of using the PK index on ID.
After restarting our application (but without truncating the table) Oracle came back to reason and used the PK index.
So, my explanation was that Oracle thought it's a good idea to scan the table when it was nearly empty but then never revised this query plan. This brings me to my question: How often does oracle revise a query plan?
Was it because I restarted our application? I have some doubts about this, since we recycle our pooled connections after 1 hour (therefore no connection can be older than 1 h).
Was it because a certain amount of time had passed?
How would you force oracle to not do a scan even when the table is nearly empty?
Environment information:
- oracle 11g
- jdbc client (java 6)
UPDATE 10/25/2011: I did a regression test on Oracle 10g and the problem is the same, so it's neither caused nor fixed by the dynamic cursor sharing. As Mark mentioned initially, the plan does not get revised unless there is a major event like structural changes or re-computing the table stats.
Eventually I've added a hint to force access by the PK, but I think the optimizer should have been able to figure this out. If there's a PK that matches the search criteria, then go ahead and use even for small tables (where the performance difference is insignificant anyway).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
什么版本的Oracle？
通常，
如果该语句尚未位于共享池中，则会进行硬解析。这将是生成执行计划的时间。
之后，执行计划就不会改变，除非有什么原因导致它失效。 （删除/添加索引到表，从表中删除列，重新计算表上的统计信息等）。
11g 具有自适应游标共享（这就是我询问 Oracle 版本的原因），并且无需深入了解很多细节，它就会查看绑定变量值并根据新的绑定值确定是否需要更改计划。
What version of Oracle?
Generally,
would get hard parsed if the statement was not already in the shared pool. This would be the time that the execution plan would get generated.
After that, the execution plan wouldn't change unless something caused it to be invalidated. (drop/add an index to the table,drop a column from the table, recompute stats on the table, etc).
11g has adaptive cursor sharing (which is why I asked the version of Oracle), and, without getting into a lot of details, it will peek at bind variable values and determine if a plan change is necessary, based on a new bind value.
我认为这是表统计数据过时的情况。除了自适应游标共享之外，Oracle 仅在收集新统计信息时从优化器角度看到新行。发生这种情况一段时间后，将生成新计划。
对于此查询，您使用的提示是无害的。通常最好是解决根本问题而不是暗示。第一行提示在表达意图时也可能起作用。
I think this is a case of stale table statistics. Adaptive cursor sharing aside, Oracle will only see the new rows from a optimizer perspective when new statistics are collected. Some time after this happens a new plan will be generated.
For this query, the hint you used is harmless. Usually it's best to solve the underlying problem rather than hinting. A first rows hint may also have worked while expressing intent.
如果查询效率低下，因为您使用的是过时的统计信息，就像这里的情况一样，那么答案通常是重新收集统计信息。
通常，您可以依靠 Oracle 来检测统计信息是否过时，并仅为适当的对象重新收集它们，但如果打开表监视，您也可以检查 DBA_TAB_MODIFICATIONS，以查看自上次收集统计信息以来是否发生了大量更改。
如果您的表的行数波动非常频繁，例如在一个表中，它暂存大量数据以供以后处理，那么一个好的策略是删除并锁定表的统计信息，并依靠优化器动态采样来获取数据。要返回的行的估计。
If a query is inefficient because you are using stale statistics, as appears to be the case here, then the answer is generally to regather statistics.
You can generally rely on Oracle to detect that statistics are stale and to regather them only for the appropriate objects, but you can also check DBA_TAB_MODIFICATIONS if table monitoring is switched on to see if a high number of changes has occurred since statistics were last gathered.
If you have tables which fluctuate widely in the number of rows quite frequently - for example in a table which stages bulk data for later processing -- then a good tactic is to delete and lock statistics for the table and rely on optimiser dynamic sampling for an estimate of the rows to be returned.
自适应游标共享是优化器的内置功能，从 11.1 开始。您运行的是哪个版本的 Oracle？ （完整版本号？）我希望 11g 的更高版本（即 11.2.0.2、11.2.0.3）表现更好。
关于自适应游标共享的来龙去脉的讨论可能超出了本论坛的范围，但是，请参阅此处以获取对其的详细讨论：
http://blogs.oracle.com/optimizer/entry/update_on_adaptive_cursor_sharing
另外，使用该博客上的搜索功能可以获取有关同一主题的更多帖子以及许多其他优化器主题。该博客实际上是由 Oracle 优化器开发团队撰写的，因此它是一个极好的资源。
Adaptive cursor sharing is a built-in feature of the optimizer, starting with 11.1. What version of Oracle are you running? (Full version number?) I'd expect later versions of 11g, i.e., 11.2.0.2, 11.2.0.3 to be better behaved.
A discussion of the ins and outs of adaptive cursor sharing is probably outside the scope of this forum, but, see here for a good discussion of it:
http://blogs.oracle.com/optimizer/entry/update_on_adaptive_cursor_sharing
Also, use the search feature on that blog for more posts on the same subject, as well as many other optimizer topics. That blog is actually written by the optimizer development team at Oracle, so it's an excellent resource.