1

Topic: Performance time limit

Hello, I address for council about a following question:
There is some  which launches stored procedure in which some actions in a cycle are fulfilled, then is launched  the materialized representation by means of DBMS_MVIEW. The problem consists that the system tests productivity procorfs, and under certain circumstances   can pour out in the durable waiting of its end and a brake.
In this connection, it would be desirable to have possibility to stop  after a certain time interval, a principal condition should be that procedure in which it is fulfilled , should stop the operation on the outflow of led out time, up to   sessions. While  two decisions: or somehow periodically to check how many on time procedure in which it is launched  is already fulfilled, or somehow to restrict itself  on time. But specific implementation, unfortunately, is not present. It would be desirable to hear your judgements on this question. And still a small question: what in your opinion a necessary minimum of skills of optimization for programmer Orakl? If that, the version of Oracle DB 11.2. Thanks!

2

Re: Performance time limit

in update  hours not

3

Re: Performance time limit

Tesamid;
To you it is more visible, clear business, however strange turns out: if had not time to update, it is necessary to roll away ALL transaction that can exceed already spent time. If intermediate  the data in bow-legs rise, besides  break. Solve with , can limit is better resources for update. Still look:
- On "Fast" (is more exact incremental, instead of fast!) the update, a specific mode and not completed  BUT to work with it it is precisely possible
- That in attribute of update atomik/not , it influences availability  during time  and, accordingly, for a while
- Whether it is possible to disconnect any indexes BEFORE update, then them to rebuild faster normally

4

Re: Performance time limit

Vladimir Filin wrote:

Tesamid;
To you it is more visible, clear business, however strange turns out: if had not time to update, it is necessary to roll away ALL transaction that can exceed already spent time. If intermediate  the data in bow-legs rise, besides  break. Solve with , can limit is better resources for update. Still look:
- On "Fast" (is more exact incremental, instead of fast!) the update, a specific mode and not completed  BUT to work with it it is precisely possible
- That in attribute of update atomik/not , it influences availability  during time  and, accordingly, for a while
- Whether it is possible to disconnect any indexes BEFORE update, then them to rebuild faster normally

Vladimir Filin, thanks for the answer, it agree with you, but requirement business are that that first of all it is necessary to guarantee the installed runtime of procedure. Yes, there are intermediate , but it besides does not contradict business logic, rollback is not required.  there is no possibility to lead to a state when all requirements fast updates will be fulfilled. Now  it is launched with atomic_refresh = true and before update indexes are deleted, then anew form. If to expose atomic_refresh = true as it can help to interrupt  at a given time?
As a whole the primary task, it to stop  procedures in which including it is fulfilled . The Performance gain , in my opinion, cannot precisely guarantee that time performance of procedure does not exceed the given limit since it too can change.
Can make a crutch, creating  which periodically checks time of existence of certain session and does kill in case time quitted?

5

Re: Performance time limit

And why long ?
Inside a heap of UDF-functions, a heap of subqueries with links, the plan of bad?

6

Re: Performance time limit

andreymx, are not used generally, itself  is under construction on  in which there are two heavy CTE, which then  in the several subqueries united union all. While thought over your post, the thought on its optimization, but besides, it not the final decision came. The task in stopping procedure on preset time, in its first part where there is an iteration it is already made, and here for  decisions are not present.

7

Re: Performance time limit

Tesamid wrote:

and here for  decisions are not present.

To you already wrote - which much that changed transaction rollback - the bad idea.
At least because can cause still the big loading on the server for longer. smile
Basically all is simple - esteem about Resource manager . It can even that you ask. Though, probably, to you there will be enough less radical method.

8

Re: Performance time limit

Tesamid wrote:

Can make a crutch, creating  which periodically checks time of existence of certain session and does kill in case time quitted?

Esteem about
DBMS_SCHEDULER.SET_ATTRIBUTE
Attribute max_run_duration

9

Re: Performance time limit

Aliona wrote:

it is passed...
Esteem about
DBMS_SCHEDULER.SET_ATTRIBUTE
Attribute max_run_duration

and how then to check up, fulfilled or not?

10

Re: Performance time limit

andreymx wrote:

it is passed...
And how then to check up, fulfilled or not?

I do not know, did not try, but I suspect that here this request can show result

wrote:

select l.log_id "Log ID";
to_char (l.log_date, ' yyyy/mm/dd hh24:mi:ss.ff tzh:tzm ') "Log Date";
L.operation "Operation";
L.STATUS "Status", L.user_name "User Name", L.CLIENT_ID "Client ID", L.global_uid "Global UID";
to_char (R.REQ_START_DATE, ' yyyy/mm/dd hh24:mi:ss.ff tzh:tzm ') "Required Start Date";
to_char (R.ACTUAL_START_DATE, ' yyyy/mm/dd hh24:mi:ss.ff tzh:tzm ') "Actual Start Date";
to_char (R.RUN_DURATION) "Run Duration";
R.INSTANCE_ID "Instance ID", R.SESSION_ID "Session ID", R.SLAVE_PID "Slave PID";
to_char (R.CPU_USED) "CPU Used", R.ADDITIONAL_INFO "Additional Info (Run)"
from DBA_SCHEDULER_JOB_LOG L, DBA_SCHEDULER_JOB_RUN_DETAILS R
where l. Owner = ' the circuit'
and l.job_name = ' __REFRESH'
and l.log_id = r.log_id (+)
order by 1 desc

If the attribute is invented, means, it to someone

11

Re: Performance time limit

Aliona;
Or through the intermediate label
In the end to write a sign of end and

12

Re: Performance time limit

To stop  on the timer - contradicts nature .
Does not arrange "long"  - define the time delay reason. If a problem in disk writing (for example)  it is necessary in this . And if simply irritates, replace  ordinary.

13

Re: Performance time limit

Tesamid;
If many records   becomes not so collect, it is desirable to do more often, but it is less in volume. It from a private experience and search in forums with similar problems.
Also, if it is impossible to make because of restrictions in one , it is possible to make a little, and on the basis of them to do the necessary. Also in blogs found that people manually from  dens update tables, bypassing restrictions, but there it is a lot of fuss with cleaning and saving of all of it in a consistent state.

14

Re: Performance time limit

All thanks for answers, made decision all the same to make a crutch with session termination   .
The resource manager is not present possibility to use since if I am not mistaken, it is adjusted at circuit level, and restriction of resources should be individual for each session of the given user.