I have a (very) complicated application which translates a GET-Request to a number of Hibernate queries to an Oracle DB.
It basically retrieves attributes of an object which are scattered in ~100 tables.
I have to undercut a maximum request time even for edge cases (=big result sets).
In the edge cases, the performance is extremely slow on the first call (i.e. after some time has passed). After that, the query is much faster, even when I flush both the buffer cache and shared pool.
This applies to the SAME GET-Request, i.e. the same object requested. Request of another object, but same attributes again takes a long time on the first call.
For example, same query, same conditions, total of rows fetched is in the (low) thousands:
- first call: 26.000ms
- first call after flush of buffer cache/shared pool: 2800ms
- second call after flush: 1200ms
From researching the web, I already discovered that flushing the pool does not necessarily really flush it, so I cannot rely on that.
As a caveat, I am a developer and have good working knowledge of Oracle, but am not a DBA and do not have access to a full DBA.
I suspect the following reasons for the slow first execution:
Oracle does hard parses which take a long time (the queries executed may contain multiple thousand parameters): I was unable to find out how long a "bad" hard parse could take. Enterprise Manager tells me he only did 1 hard parse on my queries for multiple executions though, so it seems unlikely.
the queries themselves take a long time, but get cached and the caches are not emptied by my actions (maybe disk caching?): Again, Enterprise Manager disagrees and shows very low query times overall.
I did suspect Hibernate/Java reasons at first (lots of objects to create after all), but it seems unlikely with the huge differences in performance
I am at a loss on how to proceed performance tuning and am looking for helpful reading material and/or different ideas on why the first execution is so slow.