Is there a way to fix Oracle query in shared pool

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 开发者_如何学JAVAsystem changes, all I want is to exclude query hard parsing.

---

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.

---

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

---

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