Hello everyone,
I'm developing an OLTP application where a complicated view exists that is queried often by the application through Tomcat.
The application itself uses a framework that generates the SQLs on the fly, so no parameter binding is possible.
What I experience is the following:
If I execute the query from the application, it always takes about 3 seconds to complete, also for subsequent runs (Tomcat uses ojdbc6.jar)
If I execute the same query from SQL Developer it is the same behaviour. If I execute from Toad, it's the same behaviour for the first two runs, afterwards it executes in <50ms.
I assume that it has something to do with plan creation/caching. How can I prove it and - best - fix it?
Right now I'm using 11.2 XE running on my laptop together with Tomcat. In production it will be on a 11.2 SE1.
The plan itself shows little to no cost. This is expected, as the tables are almost empty and all joins are indexed as needed (the last row of 103 steps is):
| | 0 | SELECT STATEMENT | | | | | 1 | 4876 | | 26 (12)| 00:00:01 | |
The statement itself is
SELECT
.....all view columns...
FROM myview
WHERE (myview.user_id = 1010)
The statement is always generated like this, but with changing user_id for different users. The plan ID is always the same, no matter what the user_id is.
Is it possible to have the queries fast from the beginning / 2nd run of the query?
Is is only happening on 11.2 XE and will be gone in 11.2 SE1?
Thank you & Best regards,
Blama