Is there a way to fix an Oracle query in a shared pool

Question

I have a reporting engine that runs PreparedStatements in Oracle 11, which is a high priority task.

I see that the first call to a request usually takes much longer than the same request (the request has different parameters and returns different data).

I assume this is due to the rigorous analysis performed by Oracle the first time the query is called.

I wonder if there is a way to hint at Oracle that this request is a high priority request that will be executed frequently, and which performance is critical, so it should remain in the general pool, no matter what?

I know that I can fix the execution plan in Oracle 11, but I don’t want to fix it, I want Oracle to still be able to change it, because changes in the system all I want is to exclude the parsing of the request.

Answer 1

Perhaps you should change your "I suppose ..." to "I tested and determined ..." :)

Not only parsing can affect query performance; when it is executed, it must extract blocks from the disk to the buffer cache - subsequent executions may take advantage of the blocks that are in memory, and therefore faster.

: - , , . , , , .

Answer 2

, , . , , . , . .

rdbms, , dbms_shared_pool.keep. . http://psoug.org/reference/dbms_shared_pool.html

. , .

. http://ronr.blogspot.com

Answer 3

You can try connecting to the shared pool using dbms_shared_pool.keep

But first, I have to make sure that you have an aging problem.