Shulou(Shulou.com)06/01 Report--

Haha, the content about schedule is not over. In this chapter, we talk about Windows. Windows usually refers to the operating system that covers the richest man, while here we talk about Windows.

Refers to a child of the SCHEDULER feature. In SCHEDULER, WINDOW corresponds to the concept of a time window.

We know that ordinary jobs has no concept of runtime management, that is, after a job is started, users can only passively wait for its execution until the task is completed (or the corresponding process of DBA manual kill).

In the meantime, the executed job will compete with other active processes for resources in the current system. For large database systems, system resources are very valuable intangible assets.

The enterprise can use it whenever it wants to use it, without any plan or restraint. Don't say that this was really the case before 9i. Anyone who wants to use it can use it, and no one can control it.

One of the most serious is job. Whether you think of Job Classes, it is true that defining Job Classes can control the resources that job can use, but using Job Classes alone does not have the flexibility to control job to use the right resources at the right time.

After 10g of oracle, WINDOWS was provided in SCHEDULER, and the matter was finally resolved.

WINDOW can specify a time window during which, in combination with Job Classes, you can effectively control the resources at your disposal (use) when job executes.

For example, job is usually executed in the early hours of the morning when the server load is low, then you can set it through WINDOW to allow jobs to use more system resources during this period, and after working hours

If the job execution is not completed, allocate another limited resource to it to minimize the impact of the resources occupied by the job execution on other services.

1. Create a Window

There is a special process for creating a Window: DBMS_SCHEDULER.CREATE_WINDOW handles it, which can be called in two ways

-based on SCHEDULE

DBMS_SCHEDULER.CREATE_WINDOW (

Window_name IN VARCHAR2

Resource_plan IN VARCHAR2

Schedule_name IN VARCHAR2

Duration IN INTERVAL DAY TO SECOND

Window_priority IN VARCHAR2 DEFAULT "LOW"

Comments IN VARCHAR2 DEFAULT NULL

);

-- definition-based scheduling

DBMS_SCHEDULER.CREATE_WINDOW (

Window_name IN VARCHAR2

Resource_plan IN VARCHAR2

Start_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL

Repeat_interval IN VARCHAR2

End_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL

Duration IN INTERVAL DAY TO SECOND

Window_priority IN VARCHAR2 DEFAULT "LOW"

Comments IN VARCHAR2 DEFAULT NULL)

In addition to those that have been introduced and known before, and what they represent by looking at the parameter name, the following parameters need to be paid more attention to.

The parameter Resource_plan is used to specify the resource usage plan to be used. When WINDOW is opened, resources are automatically allocated according to the settings in the specified resource usage plan.

When WINDOW is turned off (yes, window is closed, or how to effectively control the usage of resources), the system will automatically switch back to the appropriate resource planning.

This parameter can even be specified as NULL or a null value "" when executing the procedure. When set to NULL, the default resource plan is used, and when set to a null value "", the resource usage plan is disabled.

Duration specifies the validity period of WINDOW. For example, specifying interval "5" hour means 5 hours. This parameter must specify a parameter value when executing the procedure, otherwise an error will be reported when it is created.

Window_priority this parameter is used to specify the priority of the WINDOW. Because only one WINDOW is valid at the same time, if you find overlap when creating a WINDOW, ORACLE needs to specify a rule based on this parameter

To determine the priority, to put it bluntly, it means who to use the resource first. This parameter has two optional values: HIGH or LOW, and the default value is LOW.

As the previous CREATE_WINDOW procedure syntax structure shows, there are also two ways to call this procedure, the difference being whether to specify an existing defined scheduling SCHEDULE or to specify a schedule when the procedure is executed

The goal and the function are the same, here is only an example, let's choose the most complex way, specify the schedule when executing the process, and the execution script is as follows

Begin

Dbms_scheduler.create_window (window_name = > 'mywindow1'

Resource_plan = > null

Start_date = > sysdate

Repeat_interval = > 'FREQ=DAILY; INTERVAL=5'

Duration = > interval'1' hour)

End

Query the currently owned WINDOW through the * _ SCHEDULER_WINDOWS view (note that there are only DBA and ALL, no USER, because all defined WINDOW belong to SYS users).

In addition to the * _ SCHEDULER_WINDOWS view showing all current WINDOW, there is also the * _ SCHEDULER_WINDOW_DETAILS view, which shows the details of the WINDOW.

The * _ SCHEDULER_WINDOW_LOG view, which displays WINDOW logs, such as opening and closing.

Select window_name,resource_plan,to_char (start_date,'yyyy-mm-dd hh34:mi:ss'), repeat_interval from DBA_SCHEDULER_WINDOWS where window_name = 'MYWINDOW1'

WINDOW_NAME RESOURCE_PLAN TO_CHAR (START_DATE,'YYYY-MM-DDHH24:MI:SS') REPEAT_INTERVAL

MYWINDOW1 2018-05-15 15:12:02 FREQ=DAILY; INTERVAL=5

2. Manage Window

Through the previous study of SCHEDULER objects, we have already understood the characteristics of objects in ORACLE SCHEDULER. The management of most objects is basically the following.

(1) modify object properties and use the SET_ATTRIBUTE process

Begin

Dbms_scheduler.set_attribute ("mywindow1", "start_date", sysdate+1)

End

(2) ENABLE object, using ENABLE procedure

Begin

Dbms_scheduler.enable ("mywindow1")

End

(3) DISABLE object, using DISABLE procedure

Begin

Dbms_scheduler.disable ("mywindow1")

End

(4) delete the object and use the DROP_WINDOW process

Begin

Dbms_scheduler.drop_window ("mywindow1")

End

(5) in addition, for WINDOW objects, because of its special function, there is a manual opening of WINDOW, the use of OPEN_WINDOW process.

Note that WINDOW depends on its scheduling, so when you open WINDOW manually, you must specify the duration property for it

Begin

Dbms_scheduler.open_window ("mywindow1", interval "1" hour)

End

(6) manually close WINDOW and use the CLOSE_WINDOW process

Begin

Dbms_scheduler.close_window ("mywindow1")

End

Log will be recorded when you turn off and open WINDOW, and you can get this information in the * _ SCHEDULER_WINDOW_LOG view.

3 、 WINDOW GROUP

In addition to WINDOW, there is also a relationship with WINDOW called WINDOW GROUP, a WINDOW GROUP may contain multiple WINDOW. The original intention of using WINDOW GROUP is like this

If a job takes a long time to execute, or even executes 24 hours a day, for this kind of job, it is very difficult for a single WINDOW to effectively adjust its resource consumption.

You can set up a WINDOW GROUP that contains multiple WINDOW, each of which is responsible for the resource usage plan at different points in time.

Then, when you create a JOB, specify the schedule_name parameter as the name of WINDOW GROUP (I didn't expect that SCHEDULE_NAME can also be specified as WINDOW GROUP, but it's not just WINDOW GROUP.

It can also be specified as WINDOW directly, so that job and window can be linked together in a very simple way.

The creation and management of WINDOW GROUP is very similar to that described earlier.

Create, using CREATE_WINDOW_GROUP procedures

Delete, using the DROP_WINDOW_GROUP procedure

Add WINDOW members, using the ADD_WINDOW_GROUP_MEMBER procedure

Delete a WINDOW member, using the REMOVE_WINDOW_GROUP_MEMBER procedure

Enable, using the ENABLE procedure

Disable, use the DISABLE procedure

The way to call these procedures is also very simple, you can do experiments in private.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.