1

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.
What advise?

2

Re: Why the fast request can become slow?

Mr_Frost;
To look at plans of requests while works well
To look at plans of requests while works badly
To compare

3

Re: Why the fast request can become slow?

Also look, what event of a DB at session at the moment of hangup.

4

Re: Why the fast request can become slow?

Mr_Frost;
And after all it was possible to begin with and to simply statistican  (with an option no_invalidate = FALSE) under the table of the main request... wink

5

Re: Why the fast request can become slow?

Anna wrote:

Mr_Frost;
And after all it was possible to begin with and to simply statistican  (with an option no_invalidate = FALSE) under the table of the main request... wink

aha, doctors at us too sometimes so treat. Heart is ill? So you antibiotics of a wide spectrum and vitamins drink

6

Re: Why the fast request can become slow?

Mr_Frost;
Request plans - are not present, the table structure - is not present, the indexing circuit - is not present, the volume and character of allocation of the data in the table - is not present, the text of problem request - is not present.
Therefore all mentioned below - as the assumption that is called - "bad shot".
Probably, your problem - monotonically increasing values in indexed fields (a-lja ID, X_DATE) and samplings with a condition "<monotonically a growing field>> = <a certain value>.

7

Re: Why the fast request can become slow?

Anna wrote:

Mr_Frost;
And after all it was possible to begin with and to simply statistican  (with an option no_invalidate = FALSE) under the table of the main request... wink

Statistics under the table has been collected 7/31/2018 of 9:02:03 PM
Hangup began 8/1/2018 at 1:17:43 AM
During the period between collection of statistics and hangup  it was fulfilled huge number of times, each time for 4-5 seconds

8

Re: Why the fast request can become slow?

Mr_Frost;
You can long argue, we can long guess. But it is necessary for acceleration of process of obtaining of the right answer of more reality. The necessary minimum is sounded. Without it - any assumptions will be no more than assumptions. On "photo" it is possible to deliver the diagnosis, but, nevertheless, with analyses chances of success above... And if there will be "tomogram" and "electrocardiogram" with "X-ray" and other  generally it is wonderful...

9

Re: Why the fast request can become slow?

Anna wrote:

Mr_Frost;
Request plans - are not present, the table structure - is not present, the indexing circuit - is not present, the volume and character of allocation of the data in the table - is not present, the text of problem request - is not present.
Therefore all mentioned below - as the assumption that is called - "bad shot".
Probably, your problem - monotonically increasing values in indexed fields (a-lja ID, X_DATE) and samplings with a condition "<monotonically a growing field>> = <a certain value>.

If I could simulate a situation, I certainly would give all.
But we cannot repeat it at ourselves even how I to you will simulate it?
It is the working server, all is shown only on it, on test apprx.
We need to repair all quickly in the morning.
I can be not so clear explained, I will try in another way.
Present that you have a request, and it very much for a long time .
You its beret, copy and fulfill in separate session, for example:
select col1, col2 from tab where id =:id
And it fulfills we tell for 1 second.
You rewrite it though as, it is unimportant, if only at least one character changed:
select col1 col1, col2 from tab where id =:id
Simply added .
Result - 0,001 seconds
The second result is just that speed from which it and worked before demolition

10

Re: Why the fast request can become slow?

Mr_Frost wrote:

There are no locks, there are no insertions, updates, anything is not present, also all simply hangs.

You do not know, what such waitings and how them to look? [spoiler] latch free: cache buffers chains - WAIT_TIME> 14 hours! [/spoiler]

11

Re: Why the fast request can become slow?

Mr_Frost;
About the reasons of "brakes" you also answered yourselves - the request execution plan "moves down". If to force the server to construct the new plan - a problem disappears.
Being returned to a question from a start post: if you know "the good" and "fast" plan for request - it is possible to "fix" its any accessible in your version  a method (the blessing, in their fresh versions more than one).
But to understand the reasons of such behavior and to eliminate them is somehow it is necessary to receive hardly more information, than you already have...

12

Re: Why the fast request can become slow?

Anna wrote:

Mr_Frost;
About the reasons of "brakes" you also answered yourselves - the request execution plan "moves down". If to force the server to construct the new plan - a problem disappears.

It is clear that moves down, the question was in other

Anna wrote:

being returned to a question from a start post: if you know "the good" and "fast" plan for request - it is possible to "fix" its any accessible in your version  a method (the blessing, in their fresh versions more than one).

Anna, once again. Request number 1 which works today, it good. Will work perfectly till the night. Breaks at night.
I change in it any character, , everything, I transform it in Zapros2. It works one day till next night.
In the morning I do not do Zapros3, I return Zapros2 in Zapros1, it again good, before demolition.
And so on, always on a circle. The request text lives in one day.

Anna wrote:

but to understand the reasons of such behavior and to eliminate them is somehow it is necessary to receive hardly more information, than you already have...

If hangs at night - I will throw off plans of analysis of Zaprosa1 and Zaprosa2 in the morning

13

Re: Why the fast request can become slow?

Anna wrote:

about the reasons of "brakes" you also answered yourselves - the request execution plan

Oh not the fact "moves down".

14

Re: Why the fast request can become slow?

Mr_Frost wrote:

...
Once again
...

I correctly understand: at you "which year in a subfield happens underground knock", and we are offered to explain "as it happens"?
8-o

15

Re: Why the fast request can become slow?

Mr_Frost wrote:

it is clear, what moves down, the question was in other

the Question in the reason plan flip?
Can be beginning from cardinality feedback/adaptive cursor sharing before banal "obsolescence" from library  and parsing with new . Sm v$sql_shared_cursor.
Also various guessings are possible on

 select sql_id, loaded_versions, loads, first_load_time, invalidations, parse_calls, optimizer_env_hash_value, child_number, last_load_time, last_active_time
from v$sql
where sql_Id =... 

The reasons of change of the plan should be investigated operatively because  v$sql_shared_cursor NOT  in ash.
When it seems that also all simply hangs - look in v$active_session_history.

16

Re: Why the fast request can become slow?

Everything, the request hung. It is ready to follow any your instructions

17

Re: Why the fast request can become slow?

The session status - it is active.
Event - async descriptor resize

18

Re: Why the fast request can become slow?

It is visible that many times it was fulfilled quickly, then it was twice fulfilled on 2 with superfluous minutes, and already more than 10 minutes hang.
If now to do nothing - it most likely and till the morning it will not be fulfilled.
If I now make flush shared_pool - will be fulfilled. But further not . Or again hangs, or will work well

19

Re: Why the fast request can become slow?

The gain in the table from which the request of the cursor becomes, did not change. For half-minute it is added till 700-800 lines.
I.e. there is nothing unusual. The data volume for the cursor did not increase.
The active sessions on the server are not present. Locks are not present.

20

Re: Why the fast request can become slow?

I am sorry, today I will be already inaccessible, all requests I can fulfill only tomorrow.

21

Re: Why the fast request can become slow?

Mr_Frost wrote:

the Gain in the table from which the request of the cursor becomes, did not change. For half-minute it is added till 700-800 lines.
I.e. there is nothing unusual. The data volume for the cursor did not increase.
The active sessions on the server are not present. Locks are not present.

Once again I will tell - you look anywhere, but only not there where, at you ask to look. Anybody did not ask you about lines, data volume or other sessions. It, certainly, can appear important, but with smaller probability than other factors.
The most banal and a plausible reason of such behavior is the moved down plan. You already a heap to the people asked to compare plans of requests in "good" and in the "bad" case, but you and did not make it - and do anything you like besides. While you do not make it -  to guess over more refined and rare occurences of similar behavior - it is senseless unless to practise in wit and in that at whom practice was richer.
On a case if you do not do it for that simple reason that do not know as to compare plans - I will inform.
If the license is bought from you, is  dba_hist_active_sess_history. The information is stored In it during the last days, including you can look that was the last nights. Find in it  session on session_id and serial# (number of session at  pl/sql developer shows to you). sql_id - there will be your identifier  request (it just changes if to change though somehow the text of request and does not change if it not to change), sql_plan_hash_value - your plan.
Look identical sql_plan_hash_value at two requests or not - then it is possible to assort already why the plan flied if it happened. Any guessings at this forum are possible only if you attach here the old and new plan by means of function dbms_xplan.display_awr (or display_cursor).
If the plan identical in both cases it is simple  lines corresponding to long performance on several fields to learn that was specific session did. Namely -  it is serial on sql_plan_line_id, on event, on object_id, on blocking_session_id to understand on what was specific to a line of the plan stuck, and that during this moment did.

22

Re: Why the fast request can become slow?

Valergrad wrote:

Once again I will tell - you look anywhere, but only not there where, at you ask to look.
The most banal and a plausible reason of such behavior is the moved down plan.
If the license is bought from you, is  dba_hist_active_sess_history.

I am ready to look where ask, ideas simply are not present, as it to make.
The request from dba_hist_active_sess_history returns nothing, the license is bought from the customer or not - not in my competence to learn.
Since the morning the developer fulfilled flush shared_pool, and changed request in procedure. Therefore I can make on the contrary.
I will make a plan now when it works quickly, and I will make a plan, when hangs up.
So goes?

23

Re: Why the fast request can become slow?

The plan on now when all works

24

Re: Why the fast request can become slow?

To you answered - look latches

25

Re: Why the fast request can become slow?

Found the second request with similar behavior, here it now hangs in the active sessions.
The file 1.txt is received by copying of the text of this request and its performance explain plan in new session.
File 2.txt - explain plan hanging sql_id.
The difference is huge, to what is connected - thoughts are not present (