4. Managing Resources – Mastering Oracle Scheduler in Oracle 11g Databases

Chapter 4. Managing Resources

We use Oracle's Resource Manager to manage resources. If a system contains only a few jobs, it might not be worth setting this up. However, for any system where thousands of jobs run concurrently with online users, setting this up makes absolute sense. Resource Manager is the key to Oracle Instance Consolidation—one of the more extreme cost optimization techniques available that is often neglected. Online users don't want to be slowed down because of long-running batches. At the same time, they are waiting for the results of these batches; so the batches need to run as quickly as they can. Traditionally, administrators would often make plans for daily usage by online users and separate plans for nightly usage. In our current 24-hour global economy, this no longer makes sense and is unnecessary. If Resource Manager does its job the way it should, there is no longer a need to switch to a nightly plan where the batches have more resources. If there are no online users, or only a few (compared to potentially thousands), the batch jobs will automatically get all the remaining resources.

Let's see how we can fit Scheduler jobs into a resource plan.

Resource consumer group

As we saw in Chapter 3, a job class is mapped on a resource consumer group. So, it makes sense to start defining resource consumer groups now.

First, think about how you want to control the various tasks and how you want them to interact with each other—or even better, how to not interact with each other. Which jobs should get more resources than other jobs? Which users should get a higher priority over the others, and why?

As most users in a database will be regular online users, we can keep them in a default group that gets the second-highest priority. For those few users who tend to mess up the system, we can create a separate group and isolate them from the rest. Resource Manager can guarantee resources for particular resource consumer groups.

The resources not used at priority level 1 will be redistributed at priority level 2, and the remaining resources will be divided further. This can go on for eight priority levels. Take a look at this screenshot:

The image above shows that about 30% of the CPU resources are not assigned to priority level 1. These resources can then be redistributed to priority level 2.

For the jobs we are going to manage, we will simply create a group for each type of job. For now, we'll keep it simple and there will be only four groups of jobs. They are as follows:

  • Batch_sub_1s: For short-running jobs with little or no impact on the system

  • Batch_sub_1m: For jobs with more impact and which run for up to one minute

  • Batch_sub_1h: For jobs running for up to one hour

  • Batch_long: For jobs running for more than one hour

The measures we need to use to divide the jobs vary from system to system. The expected runtime can be a useful indicator, but a resource consumer group list based on departments is also an option. It might well be that a setup that makes good sense in one system is a nightmare in another.

With Resource Manager, we can divide the system's CPU power over the resource consumer groups in an instance. When multiple instances are running on the same server, they will all fight each other because Resource Manager does not balance the load across instances ( as it works only within an instance), the same as they would do without Resource Manager. As a rule of thumb, there should be only one instance on a server, so that we don't have to face this problem. At the moment, this is the most powerful option. It ensures that when a system is getting low on CPU power, each group receives its fair share of CPU cycles to do its work.

Before Oracle 11g, there was only the round-robin way of scheduling, where every job received its turn to use the CPU. In 11g, Oracle introduced another option—run-to-completion—which gives more emphasis on the already longer-running jobs. This is exactly the opposite of how a Unix operating system works.

In Unix, the longer-running jobs get less CPU. The idea is that if a job is a long-running one, it won't be a part of an interactive process where a terminal user is waiting. In the database, the run-to-completion option might be useful because in the long run, it shortens the usage of undo segments used for the read-consistent view of the job. This is true especially when the batch has to work along with a few hundred online users who are working on the same data. Also, don't forget about locking. As locks are held longer, there is more chance that they will block other users.

Creating resource consumer groups

In this example, we will create a few resource consumer groups. The key to divide the jobs is the expected runtime.

First, create the resource consumer groups.

Note

In Oracle 11g, cpu_mth is deprecated and we should use mgmt_mth instead.

As many of you are still using 10g, we'll stick to the deprecated method. It is still maintained for backward compatibility. The Resource Manager, like other modern processes, works on an area where it assembles its configuration before submitting it to the database. In Resource Manager, this area is called the pending area. Make sure that only a valid configuration is submitted by the Resource Manager. Its use is shown in the following code:

--/
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_consumer_group
(
consumer_group => 'batch_sub_1s',
comment => 'sub second running jobs',
cpu_mth => 'ROUND-ROBIN'
);
dbms_resource_manager.create_consumer_group
(
consumer_group => 'batch_sub_1m',
comment => 'sub minute running jobs',
cpu_mth => 'ROUND-ROBIN'
);
dbms_resource_manager.create_consumer_group
(
consumer_group => 'batch_sub_1h',
comment => 'sub hour running jobs',
cpu_mth => 'ROUND-ROBIN'
);
dbms_resource_manager.create_consumer_group
(
consumer_group => 'batch_long',
comment => 'long running jobs',
cpu_mth => 'ROUND-ROBIN'
);
dbms_resource_manager.submit_pending_area();
END;
/

The result can be seen in the DBA_RSRC_CONSUMER_GROUPS view as shown in the following screenshot:

The job owner is not only marvin, but also the individual who executes the job. We want marvin to be able to use the prepared resource consumer groups. If marvin wants to use these groups, we have to grant him the privilege to switch consumer group using the DBMS_RESOURCE_MANAGER_PRIVS package. In the DBA_TAB_PRIVS view, you will see that marvin has the execute privilege on the resource groups. Giving an execute privilege on a resource consumer group is not the same as granting the privilege to switch the consumer group. To enable marvin to switch to the created resource consumer groups, he needs the appropriate privileges granted to him, as shown in the following piece of code:

--/
begin
dbms_resource_manager_privs.grant_switch_consumer_group
(
consumer_group => 'batch_sub_1s',
grantee_name => 'marvin',
grant_option => true
);
dbms_resource_manager_privs.grant_switch_consumer_group
(
consumer_group => 'batch_sub_1m',
grantee_name => 'marvin',grant_option => true
);
dbms_resource_manager_privs.grant_switch_consumer_group
(
consumer_group => 'batch_sub_1h',
grantee_name => 'marvin',
grant_option => true
);
dbms_resource_manager_privs.grant_switch_consumer_group
(
consumer_group => 'batch_long',
grantee_name => 'marvin',grant_option => true
);
end;
/

User marvin can now switch to all the batch resource consumer groups that we just created. A user has to be able to switch to a resource consumer group in order to use it. If you want a user to always use a resource consumer group, make sure that you give the user a default resource consumer group. A session can be active for only one resource consumer group at a time. However, we can make a session switch to a different group.

These are the resource consumer groups. The connection between a job and a resource consumer group is the job class.

Class

The class definition identifies the resource consumer group to which the job belongs, so we are not required to use Resource Manager. However, for the processes we want to manage, we have to use Resource Manager and couple them to a job_class that has a resource consumer group mapping. Here we define the job classes and map them to a previously defined group:

--/
begin
DBMS_SCHEDULER.CREATE_JOB_CLASS
(
JOB_CLASS_NAME => 'sub_1s',
LOGGING_LEVEL  => DBMS_SCHEDULER.LOGGING_FULL,
LOG_HISTORY    => 5,
RESOURCE_CONSUMER_GROUP => 'batch_sub_1s'
);
DBMS_SCHEDULER.CREATE_JOB_CLASS
(
JOB_CLASS_NAME => 'sub_1m',
LOGGING_LEVEL  => DBMS_SCHEDULER.LOGGING_FULL,
LOG_HISTORY    => 5,
RESOURCE_CONSUMER_GROUP => 'batch_sub_1m'
);
DBMS_SCHEDULER.CREATE_JOB_CLASS
(
JOB_CLASS_NAME => 'sub_1h',
LOGGING_LEVEL  => DBMS_SCHEDULER.LOGGING_FULL,
LOG_HISTORY    => 5,
RESOURCE_CONSUMER_GROUP => 'batch_sub_1h'
);
DBMS_SCHEDULER.CREATE_JOB_CLASS
(
JOB_CLASS_NAME => 'longer',
LOGGING_LEVEL  => DBMS_SCHEDULER.LOGGING_FULL,
LOG_HISTORY    => 5,
RESOURCE_CONSUMER_GROUP => 'batch_long'
);
end;
/

Marvin also needs the execute privileges on the created job classes. So use the following line of code:

grant execute any class to marvin;

This line of code will enable marvin to use all the defined classes. User marvin can now use not only the job classes that we defined, but also the job classes defined by others in the database. If you want your jobs to use a certain resource consumer group, you need to define a job class that maps to that resource consumer group.

In our example, the jobs TEST01 and TEST02 typically run every subsecond, so we can put them in the SUB_1S job class. We can modify an existing job, but only after it has been disabled. If the enabling of any job fails, there can be several possible reasons for that: one is that the privileges are missing; another is that the job dependencies have become invalid. In the following code, we tie the jobs TEST01 and TEST02 to the job class sub_1s.

--/
BEGIN
dbms_scheduler.disable('TEST01' );
dbms_scheduler.set_attribute
(
name => 'TEST01',
attribute => 'job_class',
value => 'SUB_1S'
);
dbms_scheduler.enable( 'TEST01' );
dbms_scheduler.disable('TEST02' );
dbms_scheduler.set_attribute
(
name => 'TEST02',
attribute => 'job_class',
value => 'SUB_1S'
);
dbms_scheduler.enable( 'TEST02' );
END;
/

In the same system, TEST03 and TEST04 run for more than one second, but less than a minute. Therefore, we put them in sub_1m as shown here:

--/
BEGIN
dbms_scheduler.disable('TEST03' );
dbms_scheduler.set_attribute
(
name => 'TEST03',
attribute => 'job_class',
value => 'SUB_1M'
);
dbms_scheduler.enable( 'TEST03' );
dbms_scheduler.disable('TEST04' );
dbms_scheduler.set_attribute
(
name => 'TEST04',
attribute => 'job_class',
value => 'SUB_1M'
);
dbms_scheduler.enable( 'TEST04' );
END;
/

Plan

Now that we have some background about Resource Manager, let's look at an example scenario. Like most successful actions, let's start by making a plan. In this case, it's a Resource Manager's plan. Here, we define how resources are divided among the different users or processes. We will also make a plan with room for administrative users (the DBA has to solve problems), normal online users, and the four classes of batches. Each class will have a maximum or total resource usage allocated to it. This means that the low-impact class can have more jobs running than those in the heavy-impact class. For some jobs, it does not make sense to start when all the resources that the job needs are not available. For instance, a report needs to be run that performs excellently when using 64 pq slaves, but becomes a nightmare when it runs on only four slaves. It will clearly be better to wait for the resources instead of starting on time. Entering a Resource Manager plan on the command line is a tedious job. We will use DB Console to get the code.

In DB Console, go to Server | Resource Manager | Plans.

Note

For 10g, Resource Manager is on the Administration tab.

This brings us to the plan overview, where the Create button is found on the righthand side.

Hit the Create button and you will see:

As you can see, the required group OTHER_GROUPS is already present. Give the plan a name and provide a comment that describes your plan (optional). The Modify button brings us to the screen where we can select which resource consumer groups we want to add in the plan.

If the list is complete, select the OK button that brings us back to the previous screen again, this time with the selected resource consumer groups.

Here we can give each group a maximum allowed percentage of resources to be used. We want more advanced control to give priority scheduling.

Now, switch to the Advanced resource allocation and enter the CPU spreading you want to apply by following this:

  1. Give first priority to the SYS_GROUP. They are the friendly guys who'll help to get the system running again when problems arise. They need these resources to help others.

  2. Second priority is given to the quick, snappy batches and the default consumer group. In this system, most users will initially be in DEFAULT_CONSUMER_GROUP. Only users with special needs or users who tend to blow up the system by running bad SQL get special treatment. The normal users get 60% of the resources, leaving 40% for the quick batches. Don't forget that on this second level we get the CPU resources that are not used on the first level.

  3. On the third level are the medium jobs that should last for up to an hour. They are granted 50% of the resources.

  4. Last, but not the least, are the longer-running batches and the other groups. Users who don't fall into any of the previously mentioned groups automatically fall in to this OTHER_GROUPS. They get resources when the higher prioritized users leave cycles for others to utilize.

We don't want the normal online users to use parallelism, and keep it limited only to the batch groups. The majority of the online users are supposed to run quick and snappy queries that don't need parallelism. The session pool gives room for two long-running (active) jobs and up to 10 (active) users in the default group. This can be seen in the next screenshot:

Using thresholds, we can define actions that the Resource Manager has to perform when a limit is exceeded. We have the ability to re-assign a job originally submitted in BATCH_SUB_1S to a resource group for longer-running jobs, if it didn't finish within the expected time. Another valid action could be to interrupt the running SQL or kill the session.

In order to avoid further complications, we leave the thresholds in the default state, UNLIMITED as you can see here:

The Threshold can be used to make any changes to the job. The change can vary from killing the job to switching it to a different group. For now, we won't use this; we'll leave it to default values, and do the same for Idle Time.

The idea behind this change of the job's resource consumer group is that it removes unwanted load from the system. If the job exceeds the specified runtime limit, and we know for sure that the jobs in the resource group in question can never exceed this threshold when it runs in a healthy way, we can terminate the job to free resources for other jobs. The other option is to put the job in a different resource consumer group where it will receive other limits. How the job should be treated is very application-specific. Maybe it is wise to give the job more priority to prevent it from keeping objects or rows locked for longer than needed. This may result in the job getting a lower priority, and so other processes will be able to get more. This also means that the job will run even longer, keep its locks longer, and get slower because it is likely that it has to visit more undo blocks because of the mutation applied by other processes to construct the read-consistent view.

It is also possible for the Resource Manager to check the expected runtime of SQL and not start the SQL when the prognosis shows that the expected runtime is longer than allowed for SQL. This will hardly make sense for the Scheduler, but in a system where online users can run ad hoc SQL, this could be very valuable.

The Show SQL button reveals the code that is generated for us.

Window

Windows are all around us. In this context, a window specifies a period in time. In Oracle Resource Manager, we can use windows to trigger the start of jobs at a window's opening time, and stop jobs at its closing time. A window also has a Resource Manager plan that activates when the window opens.

Now, all we need to do is to activate this plan. If everything is working as intended, it should be possible to have many jobs running at the same time without losing performance for the normal users. As Oracle has an enabled maintenance plan that is coupled to all weekdays, you might find yourself wondering why your plan is not active anymore—the day after activating the Zaphod's plan. This is because the Zaphod's plan is not yet coupled to a window. Oracle has already defined the windows in which they intend to run standard maintenance tasks. For now, we will leave it that way and create a window called prod that is open 24 hours a day. This means we need to schedule the automated tasks that Oracle includes in the database to some other means of scheduling, that is, other than just a window.

One option might be to create a few windows for every day and tie the Zaphod's plan to all of them. In doing so, it is possible to have jobs activated and deactivated by the opening or closing of a particular window.

The same can be achieved by coupling a job to a schedule and using the max_run_duration in the job definitions.

There are some subtle differences between the two choices. A closing window can terminate a running job, whereas the max_run_duration defined in the job can 'only' raise an event. The event handler can choose to do whatever it wants with the job that raised the event. So be careful with the option that Oracle gives to terminate a running job by closing a window. There are many problems when a job does not terminate in a natural way. Using a schedule requires a little more setup, but it gives more control.

Window groups

By default, Oracle includes a few window groups to combine the days of the week in a maintenance plan. Although it looks pretty, the benefits are uncertain. As only one window can remain open at any given time, using windows in a sensible way requires a lot of planning; and in the end, there will be combinations of windows and schedules making it harder to understand what made a job start (or stop). The restriction of only one open window at any given time makes it hard to use. It would be more convenient to have the ability to keep multiple windows open at the same time. To stick to the predefined days of the week schedules, why not have the window Monday open along with the window morning? For scheduling, a job is tied to a window.

Monitoring

In this case, monitoring is about monitoring the usage of the resource consumer groups. In DB Console, this can be found in the Resource Manager column where we also found the location to create plans; but this time, we have to look at the Statistics entry. We can find an overview of what is happening in the database in there. The following screenshot is taken from a database that is using Resource Manager:

This shows that the system is currently running happily.

At the moment, there are no sessions waiting. We can also see that some sessions have been waiting (see the CPU Waits column). Even worse, Resource Manager has actively taken away the turn from the processes to give the CPU resources to other processes (Yields). When Resource Manager is actively interfering with processes, we can see this in the Top Actions overview in DB Console or Grid Control. The following screenshot shows the waits for Scheduler, which usually means that Resource Manager is capping processes to stay within their CPU quantum:

The graphical tools are just reading some views to get their data, and so can we. There are lots of views in the database that we can use. To quickly find which views are available, we can run the following query:

select view_name from dba_views where view_name like '%RSRC%' or
view_name like '%RESOURCE%' order by 1;

This returns 44 rows in a regular 11gR1 database.

To instantly find which users have privileges for what resource consumer group, we can use this:

select * from DBA_RSRC_CONSUMER_GROUP_PRIVS;

To find what is the default resource consumer group for a user, check this out using:

select * from DBA_RSRC_GROUP_MAPPINGS;

In the DBA_RSRC_PLANS view, we can find which Resource Manager plans are defined and in the DBA_RSRC_PLAN_DIRECTIVES view, we can see how the resources are divided. Effectively, it is the DBA_RSRC_PLAN_DIRECTIVES view that is populated when you configure a Resource Plan in DB Console or Grid Control. This is shown in the following screenshot:

In this case, there is no control on the CPU usage. However, there are a few more columns as shown in the following screenshot:

For this resource consumer group, the only limitation is the degree of parallelism.

For monitoring the usage, the following view is the most interesting:

select * from GV$RSRC_CONSUMER_GROUP;

In this case, we can see that currently there are several jobs waiting. We cannot say whether this waiting is good or bad without knowing what is happening in the database. If you have generated a few thousand jobs in an enabled state, with a start_date set to NULL, they are all available to be picked up by the Scheduler. You don't want them to run all at once; more likely, you want them to enter the system gradually. In that case, the number of jobs waiting for execution will grow. This column does not specify the total number of jobs that are ready to run in that job class. However, more processes than you have configured are running in this resource consumer group.

In the GV$RSRC_SESSION_INFO view, we can see the per-session statistics. Join it with v$session to find who is doing what in your instance. Also, see the following screenshot:

Depending on what you want to see, there are enough views that can help you to find what you are looking for. This should give a good starting point to dig further.

Problems with Resource Manager

There are a few things to keep in mind when you want to make real use of the Scheduler and want it to be controlled by Resource Manager. Sometimes it so happens that a system is idle, which means it's not using much CPU, and still your jobs are not started. Or it could also be that all of a sudden the jobs stop getting started without any modification of jobs or resource plans at all. These are nasty situations. To start with, make sure that every patch for Resource Manager is applied. For Oracle 10.2.0.4, start with the patch for bug 7482069, which is a collection of fixes for Resource Manager and is generic for all platforms.

Also, check the alert log during instance start and also check if the NUMA setting fits your system. By default, Oracle enables NUMA optimization in 10.2.0.4. This can have side effects that are not always positive, especially when your system does not happen to be a NUMA system. NUMA stands for Non Uniform Memory Architecture, which essentially means that unlike most systems, each CPU has its own memory (and not a shared one) on a system bus. If you see that your NUMA settings are enabled, you can disable them by setting the _enable_NUMA_optimization=FALSE and _db_block_numa = 1:

alter system set "_enable_NUMA_optimization" = false;
alter system set "_db_block_numa" = 1;

Sometimes, the job system can get back to life by closing a window or switching back to the internal_plan, which essentially means disabling Resource Manager. This is no solution, but it can help in times of trouble.

When nothing helps, it could make sense to use oradebug to make a trace of the cjq0 process. This trace can help Oracle development to pinpoint the real problem and help you solve the cause of the problems. This can be done using SQL*Plus as follows:

ps –ef|grep ora_cjq0_${ORACLE_SID}

Make a note of the process ID (PID) of the cjq0 process of your instance using the following code:

sqlplus "/ as sysdba"
oradebug setospid PID_of_cjq0_found_using_ps
oradebug tracefile_name

This line of code lists the filename of the trace file:

oradebug Event 27402 trace name context forever, level 37

This line of code enables the trace:

oradebug Event 27402 trace name context off

As a result, the trace is disabled.

The trace file grows rather quickly. Stop the trace as soon as you catch the problem—a job that does not start, is under the control of the Resource Manager, and has plenty of resources available—and upload it to Oracle support.

There are a few problems with the Resource Manager, and they do not always show up. Start using it and see how you can control the load. Start with simple tasks and see how the jobs are gradually consumed by the database. It is real fun to see this happen.

Note

At the time of writing, there is a bug that relates to job_weight, and to which currently there is no fix. In the short term, I advise you to avoid using job_weight because it can result in jobs not being started at all.

This is where the Oracle Scheduler is way ahead of other schedulers. In third-party schedulers, controlling a job load based on CPU usage of the system is very hard, if possible at all. Some implementations open a queue when the system is below a certain level and allow each and every queued job to start running at once. This will, in effect, kill the system before it is able to close the queue again because the CPU usage is above the threshold. Oracle prevents this from happening by using the internal_plan. It treats all the sessions equally, but with a custom Resource Manager plan you can give more priority to the more important processes of users.

Problems that Resource Manager solves

Resource Manager is not without its own problems, but it does solve others' problems and is absolutely worth investigating. The use of Resource Manager is a strategic decision and belongs to a global design system where we put one instance of a database on one server. We won't combine multiple instances on a server. This is called instance consolidation. From the clients, we should address service names and not instance names to connect to the database. If we follow these rules, we can make the best use of Resource Manager. Resource Manager can solve the following issues:

  • Guarantee sessions a minimum amount of CPU resources

  • Distribute load among groups of users or processes

  • Control the degree of parallelism for sessions

  • Control the number of active sessions in a session pool

  • Free the system from unwanted processes that consume more than expected resources (runaway processes)

  • Prevent unwanted long-running operations from being started at all

  • Control session idle times, making a difference between blocking sessions and non-blocking sessions

Using the Resource Manager instead of the operating system, we get better use of the operating system's resources. This can be explained by the fact that the Oracle server knows much better when it is smart to take a process from the run queue in comparison with the operating system. For example, a process that holds a latch does not have to be swapped out because a latch is supposed to be held for only a brief period of time. When a process is taken from the run queue, it takes ages before it is back to being able to use the CPU. This can cause a major slowdown of the whole application.

Another thing that the operating system cannot possibly know is that an idle session blocks another session. The database knows not only this, but also knows for how long that session has been idle. This gives the database—Resource Manager—the ability to terminate a session if it is idle for a given period of time, or to terminate when it is blocking other session. This makes the application run more smoothly.

In a database where ad hoc queries are being used, it could be wise to put those users who run ad hoc queries in a dedicated resource consumer group. Here the Resource Manager uses an estimate before the query is started and can decide to run (or not) the query based on the limits that are applied to the resource consumer group. In the same kind of database, limiting the degree of parallelism can be a good idea because this can prevent the over-utilization of the pq processes that are defined for the database. Over-utilization is never good and effectively slows down the whole application.

Resource Manager can make processes wait for resources and have them run when the needed resources are available, which allows the process to complete much more quickly. In many situations, the hardware is scaled up so that Resource Manager can solve the performance problems in a very elegant and cost-effective way.

Summary

Resources should be managed using the Resource Manager. When jobs are controlled by individual schedules, a lot can be done manually by making sure that plenty of resources are always available.

When jobs are controlled by a window, it is very important to have a good Resource Manager plan in place. Otherwise, all the jobs that have been submitted will flood the system at window open time—causing lots of waits and, most likely, lots of trouble. The goal for the Resource Manager is to make the jobs go in the fastest possible way through the system. Other things we have seen are:

  • How to create a resource consumer group

  • How to grant privileges to switch a resource consumer group to a user

  • How to couple a job_class to a resource consumer group

  • How to couple a job_class to an existing job

  • How to create a Resource Manager plan using DB Console

  • How to control the number of active sessions to be equal to the number of jobs in a Resource Manager plan using the session pool

  • How to control the maximal degree of parallelism that we allow for a resource consumer group

  • Why we should apply patch 7482069 when we are using Oracle 10.2.0.4, and want to do some serious work with Resource Manager

In the next chapter, we will be getting out of the database using Oracle 11gR1's newly introduced remote scheduler agent.