我有一个报告引擎，在 Oracle 11 上执行PreparedStatements，这是一项高度优先的任务。
我看到的是，第一次查询调用通常比之后的同一查询执行的时间要长得多（查询具有不同的参数并返回不同的数据）。
我想这是由于 Oracle 在第一次查询调用时进行的硬解析造成的。
我想知道，是否有一种方法可以向 Oracle 暗示该查询是经常执行的高优先级查询，并且性能至关重要，因此无论如何它都应该保留在共享池中？
我知道我可以在Oracle 11中修复执行计划，但我不想修复它，我希望Oracle仍然能够更改它，随着系统的变化，我想要的只是排除查询硬解析。
I have a report engine, performing PreparedStatements on Oracle 11, that is a highly prioritized task.
What I see is that first query invocation usually performs much much longer than the same query afterwards (query has different parameters and return different data).
I suppose this is due to hard parsing done by Oracle, on first query invocation.
I wonder, is there a way of hinting to Oracle, that this query is highly prioritized query which would be performed often, and which performance is critical, so it should remain in shared pool, no matter what?
I know that I can fix execution plan in Oracle 11, but I don't want to fix it, I want Oracle still to be able to change it, as system changes, all I want is to exclude query hard parsing.
发布评论
评论（3）
也许您应该将“我想...”更改为“我测试并已确定...”:)
查询性能可能不仅仅受到解析的影响；当它执行时，它必须从磁盘获取块到缓冲区高速缓存中 - 后续执行很可能正在利用内存中找到的块，因此速度更快。
编辑：回答您直接的问题 - 解决方法可能是定期运行一个作业来解析查询但不执行它。您甚至可以使用它来确定解析或获取是否是问题的根源。
Perhaps you should change your "I suppose..." into a "I tested and have determined..." :)
The query performance may be affected by more than just parsing; when it executes it has to fetch blocks from disk into the buffer cache - subsequent executions quite possibly are taking advantage of the blocks being found in memory and so are faster.
EDIT: to answer your immediate question - a workaround may be to have a job run periodically that parses the query but doesn't execute it. You might even be able to use this to determine whether parsing or fetching is the locus of the problem.
您可以尝试使用 dbms_shared_pool.keep 固定到共享池
但我首先要确保你有老化问题
You can try pinning to shared pool using dbms_shared_pool.keep
But I would first make sure that you have an aging out problem first
安东,
如果您的查询使用绑定变量，它将被重新使用。游标会被缓存，只要被重新使用，它就会保留在游标缓存中。确保它使用绑定变量。这提高了可重用性和可扩展性。
如果您不信任 RDBMS，您可以使用 dbms_shared_pool.keep 固定它。
请参阅 http://psoug.org/reference/dbms_shared_pool.html
您需要在命令这样做。
通常还有其他问题需要解决。
罗纳德.
http://ronr.blogspot.com
Anton,
if your query is using bind variables it will be re-used. The cursor will be cached and as long as it is re-used, it will remain in the cursor cache. Make sure that it uses bind variables. This increases re-usability and scalability.
If you don't trust the rdbms you can pin it using dbms_shared_pool.keep.
See http://psoug.org/reference/dbms_shared_pool.html
You need to find your cursor in order to do so.
Normally there is an other problem that should be fixed.
Ronald.
http://ronr.blogspot.com