Topic: Why the fast request can become slow?
Colleagues, gain, a question on a charge, does not come to the answer mind.
Situation: there is job, executable time in 30 seconds which rakes the data from the table to which permanently there is an insertion of the data. For half-minute in it collects the order of 800-1000 lines. In a broad gull I see that it is fulfilled about 4-5 seconds.
Procedure in the most normal - a cycle on the cursor from the table to which there is an insertion, in a cycle any handling, and insert or update other table.
It works perfectly all the day, then (as a rule at night) suddenly the main request hangs also session simply hangs hours on this request. There are no locks, there are no insertions, updates, anything is not present, also all simply hangs.
If to fulfill alter session flush shared_pool - it "wakes up"! For pair seconds finishes and further
Or again works all the day long (it is a good outcome), or hangs up again on next run .
In this case helps the following:
1. A stop .
2. In procedure "" we change the text of the main request in the cursor: it is possible to change in places columns in a phrase select, it is possible to add at a column, it is possible to add to a derived column +0 for example. I.e. the purpose - to change the request text that again made rigid analysis of request.
3. Start .
After that it is guaranteed day works even, hangs up at night. In the morning it is stopped, we return request in a previous day state, we launch - day works.