Nurullah Akkaya wrote:
i have a select statement that takes around 100 - 200 msecs to
complete but after some writing to the table i pause writer threads
and same select statement takes forever to complete ( 10 secs to 170
secs ) after restarting the application statement goes back to normal
speed.
any one else experiencing this behavior?
I can only speculate but it could be that the distribution of the data
in the tables have changed to the point where the cached plans for the
queries are no longer optimal. See the Tuning guide about plans going
stale and what can be done. Restarting the application will, of
course, generate a new plan based on the current distribution and
perform well again.
