CHAPTER 20

The Resource Manager

Exam Objectives

• 062.13.1    Configure the Database Resource Manager

• 062.13.2    Access and Create Resource Plans

• 062.13.3    Monitor the Resource Manager

Many computer systems will have several groups of users, each with different standards for the level of service it requires. If the system as a whole is highly stressed, it may be impossible to deliver the desired level of service to all groups. But if a priority structure can be negotiated, it should be possible to guarantee a certain level of service to certain groups—perhaps at the expense of other groups.

Configure the Database Resource Manager

In a mainframe environment, the operating system handles allocating resources to tasks. But simpler operating systems such as Unix and Windows may not have proper resource-scheduling capabilities. Oracle’s Resource Manager brings mainframe-style resource management capabilities to all supported Oracle platforms, meaning that you as database administrator (DBA) can guarantee that certain groups of database users will always receive a certain level of service, no matter what the overall workload on the database may be. Configuring the Resource Manager is often easy, but testing its effect and monitoring what it is doing may be difficult.

The Need for Resource Management

Operating systems such as Linux and Windows use a simple algorithm to assign resources to different processes: round-robin time slicing. To the operating system, there is really no difference between any of the background processes that make up the Oracle instance and any of the many server processes that support user sessions. As far as the operating system is concerned, a process is a process; it will be brought onto the central processing unit (CPU), given a few cycles of CPU time, and then switched off the CPU so that the next process can be brought on. The operating system has no way of knowing that one server process is supporting a session doing completely trivial work while another server process is supporting a session doing work critical to the survival of the organization. The Resource Manager provides a mechanism whereby the operating system’s time-slicing algorithm can be adjusted to ensure that some users receive more processing capacity than others—and to ensure that any single query does not destroy performance for everyone else. The underlying mechanism is to place a cooperative multitasking layer controlled by Oracle on top of the operating system’s preemptive multitasking system.

Throughout this chapter, the environment is assumed to be that of a telesales organization. There are several groups of users, and of particular interest are the data-entry clerks and the management accountants. There may be 200 data-entry clerks in the call center taking orders over the telephone. If their database sessions are running slowly, this is disastrous for the company. Customers will dial in only to be told, “You are number 964 in the queue. Your call is important to us. Please do not hang up.” This is happening because the data-entry clerks cannot process calls fast enough. They take an order, they click the Submit button, and then they wait…and wait…and wait…for the system to respond. This is costing money.

On the other hand, the management accountants’ work is not so urgent. Perhaps an advertisement has been run on one local radio station, and the response in terms of sales inquiries needs to be evaluated before running the advertisement nationwide. This is important work, but it doesn’t have to be real time. If the reports take 10 minutes to run instead of 5, does it really matter?

What is needed is a technique for ensuring that if the database sessions supporting the data-entry clerks need computing resources, they get them—no matter what. This could mean that at certain times of day when the call center is really busy, the clerks need 100 percent of computing resources. Resource Manager can handle this, and during that time of peak usage the sessions supporting the management accountants may hang completely. But during other times of day, when the call center is not busy, plenty of resources will be available to be directed to the management accountants’ work. At month end, another task will become top priority, specifically, the end-of-month billing runs and the rollover of the ledgers into the next accounting period. Resource Manager needs to be versatile enough to manage this too.

Clearly, Resource Manager is necessary only in highly stressed systems, but when you need it, there is no alternative. In fact, you are using Resource Manager whether you know it or not; it is configured by default in all databases to control the resources used by the Autotask system, but the default configuration has a minimal effect on normal work.

The Resource Manager Architecture

Users are placed in Resource Manager consumer groups, and Resource Manager plans, consisting of a set of directives, control the allocation of resources across the groups. Each session is assigned to a group, depending on attributes defined when the session was established and possibly modified subsequently. The underlying architecture places a cooperative multitasking layer on top of the preemptive multitasking provided by the operating system. The server process of a session in a low-priority group will, when brought onto the CPU by a context switch, voluntarily relinquish the CPU earlier than it would have done if relying purely on the operating system’s preemptive multitasking algorithm.

Consumer Groups

A Resource Manager consumer group is a set of users with similar resource requirements. One group may contain many users, and one user may be a member of many groups, but at any given moment, each session will have one group as its effective group. When a user first creates a session, his default consumer group membership will be active, but if he is a member of multiple groups, he can switch to another group, activating his membership of that group. The switch can be manual or automatic, depending on a number of factors.

In the telesales example, the 200 data-entry clerks could be in a group called OLTP, and the half-dozen management accountants could be in a group called DSS. Some users could be in both groups; depending on what work they are doing, they will activate the appropriate group membership. Other groups might be BATCH, to be given top priority for month-end processing, and LOW, for people who happen to have accounts on the system but are of no great significance.

Eighteen groups are created by default when a database is created:

•  SYS_GROUP    This group is intended for the database administrators. By default, only the SYS and SYSTEM users are in this group.

•  DEFAULT_CONSUMER_GROUP    This group is for all users who have not been specifically assigned to any other group. By default, all sessions other than SYS and SYSTEM are in this group, and this membership is active when they first create a session.

•  OTHER_GROUPS    All users are members of this group. It is used as a catchall for any sessions that are in groups not explicitly mentioned in the active Resource Manager plan.

•  Demonstration groups    The following groups are intended for separating different types of work: BATCH_GROUP, DSS_CRITICAL_GROUP, DSS_GROUP, ETL_GROUP, INTERACTIVE_GROUP, and LOW_GROUP.

•  ORA$AUTOTASK    The sessions running the autotasks will run under this group.

•  ORA$APPQOS_0 through ORA$APPQOS_7    These eight groups are used if Quality Of Service has been enabled; this is applicable only to clustered systems.

To view the groups in your database, query the views DBA_RSRC_CONSUMER_GROUPS and DBA_USERS. The latter shows the initial consumer group set for each session at connect time (see Figure 20-1).

Figure 20-1    Resource Manager consumer groups

Resource Manager Plans

A Resource Manager plan is of a certain type. The most basic (and most commonly used) type of plan is one that allocates CPU resources, but there are other resource allocation methods. Many plans can exist within the database, but only one plan is active at any one time. This plan applies to the whole instance; all sessions are controlled by it.

The following are the resources that can be controlled by a plan:

•  Total CPU usage for all sessions in a group

•  Degree of parallelism available to each session in a group

•  Number of active sessions permitted per group

•  Volume of undo space permitted per group

•  Time before terminating idle sessions

•  Maximum length of execution time for a call in a session, which can also trigger the switch of a session into another group

In the telesales example, there could be three plans based on CPU usage. A daytime plan would give top priority to the OLTP group. At times of peak activity, with the system working to full capacity, it is possible that the sessions of users in other groups would hang. At night, a different plan would be activated that guarantees the DSS jobs will run, though perhaps still not with the priority of the OLTP group. A month-end plan would give 100 percent of resources to the BATCH group, if it requires this.

A plan consists of a number of directives. Each directive assigns resources to a particular group at a particular priority level. Eleven plans are configured at database creation time:

•  The INTERNAL_PLAN is not for normal use; it disables the Resource Manager.

•  The DEFAULT_PLAN has three directives (see Figure 20-2). The first states that at priority level 1, the highest priority, any sessions connected to the SYS_GROUP consumer group can take 90 percent of CPU resources. OTHER_GROUPS sessions are guaranteed at least 9 percent, and the ORA$AUTOTASK group 1 percent.

•  The DEFAULT_MAINTENANCE_PLAN (shown in Figure 20-2) raises the proportion of resources guaranteed to maintenance tasks and other users to 25 percent.

•  The INTERNAL_QUIESCE plan has a particular purpose covered later in this chapter; it will freeze all sessions except those of the SYS_GROUP members.

•  The MIXED_WORKLOAD_PLAN (also shown in Figure 20-2) gives top priority to the SYS_GROUP, then the INTERACTIVE_GROUP, and then the BATCH_GROUP. Other demonstration plans are DSS_PLAN and ETL_CRITICAL_PLAN.

•  The ORA$AUTOTASK_PLAN is used by the Autotask system and cannot be adjusted.

•  The ORA$ROOT_PLAN is relevant only to the multitenant container database.

•  The ORA$QOS_PLAN is used by the Quality Of Service system.

To enable a plan, set the RESOURCE_MANAGER_PLAN instance parameter. This can be set automatically by the Scheduler (described in Chapter 21), manually with an ALTER SYSTEM command, or programmatically with the DBMS_RESOURCE_MANAGER.SWITCH_PLAN procedure. Following creation of a database with the Database Configuration Assistant (DBCA), the Scheduler will be configured to activate the DEFAULT_PLAN during normal working hours and the DEFAULT_MAINTENANCE_PLAN at night and weekends. It is assumed that these plans will be appropriate for most sites. They give the DBA staff top priority, followed by users, and restrict the resources that can be taken by maintenance jobs.

Figure 20-2    The directives of the DEFAULT_PLAN, the DEFAULT_MAINTENANCE_PLAN, and the MIXED_WORKLOAD_PLAN

Resource Manager works on a “trickle-down” model. All resources not utilized at one priority level are available to the level lower down, and within a level any resources not needed by one group are available to the other groups. So, if the DEFAULT_PLAN is enabled and no member of the SYS_GROUP is doing anything, the entire machine is available to the OTHER_GROUPS sessions, except for 1 percent, which may be used by the ORA$AUTOTASK group if it needs it.

Resource Manager Configuration

A PL/SQL API can be used to administer Resource Manager. This application programming interface (API) consists of two packages: DBMS_RESOURCE_MANAGER_PRIVS and DBMS_RESOURCE_MANAGER. DBMS_RESOURCE_MANAGER_PRIVS is used to put users into consumer groups and also to grant the system privilege necessary to administer Resource Manager (see Figure 20-3).

Figure 20-3    The DBMS_RESOURCE_MANAGER_PRIVS package

Here is how to give user JOHN the capability of administering Resource Manager, with the ability to pass on the privilege to other users:

This procedure call grants the system privilege ADMINISTER RESOURCE MANAGER. You can see this grant by querying the DBA_SYS_PRIVS view. To add a user to a group, use a call such as this:

This call adds ROOPESH to the group OLTP, but without giving him the ability to add other users to the group. If ROOPESH is now a member of several groups, you should nominate one as his default group. This requires a procedure in a different package.

The DBMS_RESOURCE_MANAGER package is used to create consumer groups, plans, and directives. It is also used to create the “pending area.” Before any work can be done with Resource Manager objects, you must create a pending area, which is an area of memory in the System Global Area (SGA) used for storing the objects while they are being configured. A plan may consist of many directives, and each directive is created independently; it would therefore be possible to create a totally impossible plan, one that might, for example, allocate 500 percent of CPU. The pending area is provided to prevent this possibility. The plan is created in the pending area, and then when complete, it is validated to check that it does make sense. Only then does the plan get saved to the data dictionary.

At connect time, a session will pick up the initial consumer group assigned to that user. If the user is a member of multiple consumer groups, the session can be switched to a different consumer group later. This can be done either manually or by using more advanced techniques automatically, according to the work that the session is doing.

Any user can switch their active consumer group to any of the groups of which they are a member by using the SWITCH_CURRENT_CONSUMER_GROUP procedure in the DBMS_SESSION package. Alternatively, a user with the privilege to administer Resource Manager can switch another session over by using one of two procedures in the DBMS_RESOURCE_MANAGER package. The SWITCH_CONSUMER_GROUP_FOR_USER procedure will switch all sessions logged on with a particular username, or SWITCH_CONSUMER_GROUP_FOR_SESS will switch one particular session, identified by SID and SERIAL#.

Access and Create Resource Plans

A plan consists of a set of directives dividing resources between consumer groups. The following principles can be used to control this:

•  CPU method

•  Number of active sessions

•  Degree of parallelism

•  Operation execution time

•  Idle time

•  Volume of undo data

CPU Method

Continuing the telesales example, the daytime plan would give maximum resources to the OLTP group. All other sessions will hang if the OLTP users really do need the whole machine. The only exception is the SYS_GROUP. You should always give the SYS_GROUP priority over anything else. If you, the DBA, need to do something on the production system (such as rebuilding a broken index or doing a restore and recover), you should be able to do it as fast as possible. The plan could look like this:

There are eight possible priority levels; this plan uses four of them. All CPU resources not used at one level trickle down to the next level. When this plan is active, the SYS_GROUP at level 1 can, if necessary, take over the whole machine; all other sessions will hang. But this shouldn’t happen; in normal running, no CPU cycles will be taken by the SYS_GROUP, so the whole machine will be available at level 2, where the OLTP users can use it all. Any CPU resources they do not need drop down to level 3, where they are divided 50/50 between the DSS and the BATCH sessions. If, after the OLTP, DSS, and BATCH users have taken what they need, some capacity is still left, it will be available to members of other groups. It is possible, at times when the OLTP users are working nonstop and CPU usage has hit 100 percent, that the DSS and BATCH sessions will hang.

The nighttime plan will have different settings.

As with the daytime plan, if the SYS_GROUP needs to do something, it will get top priority. But at level 2, the DSS and BATCH users are guaranteed processing time. They still do not have as high a priority as the OLTP group, but their sessions will not hang. The month-end plan might change this further.

When this plan is active, the BATCH jobs will take priority over everyone else, taking the whole machine if necessary. This would be advisable if the month-end processing actually means that the system is not usable, so it is vital to get it done as fast as possible.

A variation on the CPU method is that the “group” can itself be a plan. It is possible by this method to set up a hierarchy, where a top-level plan allocates resources between subplans. These subplans can then allocate resources between consumer groups. A case where this might be applicable would be an application service provider. Perhaps you have installed an application such as an accounting suite, and you lease time on it to several customers. Each customer will have their own groups of users. Your top-level plan will divide resources between subplans for each customer, perhaps according to the amount they are paying for access to the service. Then, within that division, the customers can each allocate resources between their consumer groups.

Creating a plan such as the daytime plan just described requires a series of procedure calls through the API. The first step is to create the pending area.

You then create the plan.

Then you create the directives within it.

Finally, validate the pending area and (if the validation returns successfully) save the plan to the data dictionary.

Here is how to activate the plan:

Use of the Ratio CPU Method

There is an alternative technique for allocating CPU resources. Rather than coding CPU usage as a percentage, you can specify ratios—and let Oracle work out the percentages. In the telesales example in the preceding section, the CPU resources at level 2 for the nighttime plan were as follows:

•  OLTP 50%

•  DSS 25%

•  BATCH 25%

If you decide to add a fourth group (call it WEB) and want to make it equal in priority to OLTP and to double DSS and BATCH, you will have to change all the directives to achieve this.

•  OLTP 33%

•  WEB 33%

•  DSS 17%

•  BATCH 17%

The ratio method lets you specify proportions. The absolute values have no significance. For example, the original ratios could have been as follows:

•  OLTP 20

•  DSS 10

•  BATCH 10

Now, to add the WEB group with a priority equal to OLTP, you have to add only one new directive—WEB 20—and leave the others unchanged.

The Active Session Pool Method

It may be that investigation has shown that a certain number of jobs can be run concurrently by one group of users with no problems but that if this number is exceeded, other groups will have difficulties. For example, it might be that the telesales company has six management accountants, logging on with Oracle usernames in the DSS group. If one, two, or even three of them generate reports at the same time, everything is fine, but if four or more attempt to run reports concurrently, the OLTP users begin to suffer.

The active session pool method of Resource Manager lets the DBA limit the number of statements that will run concurrently for one group, without restricting the actual number of logins. To continue the example, all six accountants can be connected, and if three of them submit reports, they will all run, but if a fourth submits a job, it will be queued until one of the other three finishes. The nighttime plan would remove all restrictions of this nature.

An active session is defined as a session that is running a query or as a session that is in an uncommitted transaction. If parallel processing has been enabled, the individual parallel processors do not count against the session pool; rather, the entire parallel operation counts as one active session. By default, a session will be queued indefinitely, but if you want, you can set a time limit. If a session from the pool does not become available within this limit, the statement is aborted, and an error is returned to the session that issued it. This call will adjust the DAY plan to limit the DSS group to three active sessions, queuing requests for one minute before returning an error.

To monitor the effect of the active session pool, the column CURRENT_QUEUE_DURATION in V$SESSION will show for every queued session the number of seconds it has been waiting. The view V$RSRC_CONSUMER_GROUP gives a global picture, showing how many sessions for each group are queued at any given moment.

What if the active session pool were set to zero for all groups? The result would be that all sessions would hang. This is, in fact, a useful capability, and it is used by the command ALTER SYSTEM QUIESCE RESTRICTED. This command activates the Resource Manager plan INTERNAL_QUIESCE, which sets the active session pool for all groups other than the SYS_GROUP to zero. The effect is that statements in progress will continue until they finish but that no one (other than members of the SYS_GROUP) can issue any more statements. If they do, the session will hang. In effect, the database is frozen for all but the administrators. This can be invaluable to get a stable system for a moment of maintenance work.

To cancel the quiesce, issue ALTER SYSTEM UNQUIESCE.

Limiting the Degree of Parallelism

Parallel processing, both for SELECT statements and for DML, can greatly enhance the performance of individual statements, but the price you pay may be an impact on other users. It may be that your management accountants have discovered that if they run a query with the degree of parallelism set to 50 (and you cannot control this—it is done by hints in the code they write), the report generates faster. But do you really want one session to take 50 parallel execution servers from the pool? That may not leave enough for other work. Furthermore, the query may now run faster but cripple the performance of the rest of the database. Resource Manager can control this by setting a hard limit on the number of parallel processes that each session of any one group is allowed to use. In the daytime plan, for instance, you might limit the DSS and BATCH groups to no more than 4 per session, even if they ask for 50, and not permit OTHER_GROUPS sessions to use parallel processing at all. The nighttime plan could remove these restrictions. Here is an example:

Controlling Jobs by Execution Time

The problem of one large job killing performance for everyone else is well known in the database world. Resource Manager solves this by providing a mechanism whereby large jobs can be completely eliminated from the system at certain times. Alternatively, a session can be allowed to launch the job, but if it exceeds (or is likely to exceed) a time threshold, the session can be switched to a lower-priority group. This will allow the statement to run but with reduced impact on other users. The following are the relevant arguments to the DBMS_RESOURCE_MANAGER.CREATE_PLAN DIRECTIVE procedure:

•  SWITCH_GROUP

•  SWITCH_TIME (measured as CPU time)

•  SWITCH_ELAPSED_TIME (measured as wall-clock execution time)

•  SWITCH_ESTIMATE

•  MAX_EST_EXEC_TIME

The SWITCH_GROUP nominates a consumer group, probably one with lower priority in the plan, to which a session will be switched if a call takes longer than the number of seconds specified by the SWITCH_TIME or SWITCH_ELAPSED_TIME to complete. If SWITCH_ESTIMATE is TRUE, the session will be switched before the statement starts running if the optimizer thinks it might take more than that number of seconds. The MAX_EST_EXEC_TIME argument will block all statements that the optimizer believes would take longer than that number of seconds.

Terminating Sessions by Idle Time

Sessions that are not doing anything waste machine resources. Every session consists, on the server side, of a server process and a Program Global Area (PGA). Even if the session is not executing a statement, the operating system must still bring it onto the CPU according to its round-robin time-slicing algorithm. This is known as a context switch. Every context switch forces the computer to do a lot of work as registers are loaded from main memory, the state of the session is checked, and then the registers are cleared again. If the PGA has been paged to disk, that, too, must be reloaded into main memory. The shared server mechanism, detailed in Chapter 14, will help to reduce idle processes, but it can’t do anything about the number of sessions. The User Global Areas (UGAs)—in the SGA, remember—will still be taking up memory, and Oracle still has to check the state of the session on a regular basis.

Resource Manager can disconnect sessions that are not working, according to two criteria. The first is simply based on idle time: How long has it been since the session executed a statement? The second is more sophisticated: It checks not only how long since a session executed a statement but also whether the session is holding any row or table locks that are blocking other sessions, which is a much more serious problem. Remember from Chapter 6 that a row lock enqueue held by one session will cause another session that needs to lock the same row to hang indefinitely; this can cause the whole database to stop working if the problem escalates from session to session. It is possible for the DBA to detect this problem, identify the session that is holding the lock, and kill it, but this is a tricky procedure. By using Resource Manager, you can configure automatic killing of any sessions that block other sessions for more than a certain length of time.

An important point is that “idle time” is time that the server process has been idle, not time that the user process has been idle. For example, your management accountant might be using a spreadsheet as his user process. He will have downloaded some information to it to work on locally before saving it back to the database. While this is going on, the server process is indeed idle, but the user could be working flat out in the spreadsheet. He will not be pleased if, when he tries to pass the information back, he finds that you have disconnected him and perhaps lost all his work in progress.

The following are the arguments to the DBMS_RESOURCE_MANAGER.CREATE_PLAN DIRECTIVE procedure that will enable idle session termination:

•  MAX_IDLE_TIME

•  MAX_IDLE_BLOCKER_TIME

Restricting Generation of Undo Data

Management of undo data was covered in Chapter 6. All Data Manipulation Language (DML) statements must generate undo data, and this data must be stored until the transaction has been committed or rolled back. Oracle has no choice about this; it is according to the rules of a relational database. If you have configured the UNDO_RETENTION instance parameter and set the RETENTION GUARANTEE attribute for your undo tablespace, the undo data may well be kept for some considerable time after the transaction has committed.

All your undo data will be written to a single undo tablespace. A potential problem is that one badly designed transaction could fill this storage area, the undo tablespace. Programmers should not design large, long-running transactions. In business terms, though, huge transactions may be necessary to preserve the integrity of the system. For example, an accounting suite’s nominal ledger cannot be partly in one accounting period and partly in the next, which is an impossibility in accountancy. So, the rollover from one period to the next could mean updating millions of rows in thousands of tables over many hours and then committing. This will require an undo tablespace the size of Jupiter and will also cause record-locking problems as the big transaction blocks other work. The answer is to break up the one business transaction into many small database transactions programmatically. If this is a problem, go back to the developers; there is nothing you as DBA can do to fix it.

As DBA, however, you can prevent large transactions by one group of users from filling up the undo tablespace. If your batch routines do not commit regularly, they will write a lot of undo data that cannot be overwritten. If too many of these batch jobs are run concurrently, the undo tablespace can fill up with active undo. This will cause all transactions to cease, and no more transactions can start until one of them commits. Resource Manager provides a mechanism whereby the undo tablespace can in effect be partitioned into areas reserved for different consumer groups.

Your calculations on undo generated per second and your desired undo retention (as derived from the V$UNDOSTAT view and your requirements for long-running queries and the flashback query capability) might show that the undo tablespace should be, for example, 8GB. To be safe, you size it at 12GB. But to ensure that the small OLTP transactions will always have room for their undo data, you can limit the space used by the BATCH group to, say, 6GB during normal working hours by assigning an undo pool in a Resource Manager plan. To calculate the undo space necessary for individual transactions, you can query the view V$TRANSACTION while the transaction is in progress. The column USED_UBLK shows how much undo is being used by each active transaction.

When the amount of active undo data generated by all sessions of a certain consumer group hits its pool limit, it will no longer be possible for members of that group to add more undo to current transactions or to start new transactions. They will hang until one transaction commits, thus freeing up space within the pool. Meanwhile, other groups can continue working in the remainder of the undo tablespace. This restricts the effect of generating too much undo to one group, rather than having it impact all users. The argument to the DBMS_RESOURCE_MANAGER.CREATE_PLAN DIRECTIVE procedure that will define an undo pool is UNDO_POOL.

Exercise 20-1: Create and Implement a Resource Manager Plan    There is a shortcut to creating a Resource Manager plan: a single procedure call, CREATE_SIMPLE_PLAN. Use this to enable resource management in your database.

1.  Connect to the database as user SYSTEM.

2.  Create some users and grant them the CONNECT role.

3.  Create two consumer groups. It is necessary to create a pending area first.

4.  Assign the users to groups and set their initial consumer group.

5.  Create the plan.

6.  Activate the plan.

Monitor the Resource Manager

The Resource Manager configuration is documented in a set of DBA views, principally the following:

•  DBA_RSRC_PLANS    Plans and status

•  DBA_RSRC_PLAN_DIRECTIVES    Plan directives

•  DBA_RSRC_CONSUMER_GROUPS    Consumer groups

The current situation is documented in V$ views:

•  V$SESSION    The active consumer group of each session

•  V$RSRC_PLAN    The currently active plan

•  V$RSRC_CONSUMER_GROUP    Statistics for the groups

If a session has been impacted by Resource Manager, this shows up as the wait event resmgr:cpu quantum, which will be visible in the V$SESSION.EVENT column while the session is actually waiting, as well as in the V$SESSION_EVENT view for the cumulative time waited since the session started.

Exercise 20-2: Test and Monitor a Resource Manager Plan    This exercise continues from Exercise 20-1, testing the effect of the plan.

1.  Connect to the database as user SYSTEM.

2.  Restrict the database instance to using only one CPU core to ensure contention when multiple sessions work concurrently.

3.  In two more SQL*Plus sessions, log in as the ACCT and CLERK users.

4.  In the SYSTEM session, run this query to confirm the group memberships of your sessions:

5.  In the SYSTEM session, run this query to show the CPU usage by each group so far:

The figures for the DSS and OLTP groups will be low and nearly identical.

6.  In both the CLERK and ACCT sessions, run this query concurrently, and let it run for a while:

7.  Repeat the query from step 5. Note that the OLTP group has used approximately four times as much CPU as the DSS group.

8.  Run this query to see the wait time in sessions caused by Resource Manager:

Figure 20-4 shows typical results from steps 7 and 8, specifically, a much higher wait time for the ACCT user than the CLERK user.

Figure 20-4    Monitoring the effect of a Resource Manager plan

9.  Tidy up by dropping the users and then returning the database to its default Resource Manager and CPU configuration by restarting the instance.

Two-Minute Drill

Configure the Database Resource Manager

•  Configure Resource Manager with two APIs: DBMS_RESOURCE_MANAGER and DBMS_RESOURCE_MANAGER_PRIVS.

•  Enable Resource Manager by setting the RESOURCE_MANAGER_PLAN instance parameter, either interactively or through the Scheduler.

•  A plan is a set of directives allocating resources to consumer groups.

•  To configure Resource Manager, do the following: First, create consumer groups; second, create a plan; third, create the plan directives that allocate resources to groups.

Access and Create Resource Plans

•  A plan can limit several resources: CPU, active sessions, SQL statement execution time, use of parallel query, and undo space.

•  The default plan (named DEFAULT_PLAN) gives top priority to the SYS_GROUP and lowest priority to the ORA$AUTOTASK group.

Monitor the Resource Manager

•  The Resource Manager configuration is documented in a set of data dictionary views, DBA_RSRC_%. To monitor Resource Manager, query the dynamic performance views.

•  Resource Manager will not appear to have any effect until the system comes under stress.

Self Test

1.  There are several steps involved in setting up Resource Manager:

a.  Assign users to consumer groups.

b.  Create consumer groups.

c.  Create directives.

d.  Create the pending area.

e.  Create the plan.

f.  Submit the pending area.

g.  Validate the pending area.

What is the correct order for accomplishing these steps? (Choose the best answer.)

A.  d, e, g, f, c, b, a

B.  d, b, a, e, c, g, f

C.  d, b, a, c, e, g, f

D.  e, c, d, b, a, f, g

E.  b, a, d, e, c, f, g

2.  Which of the following statements are correct about users and consumer groups? (Choose all correct answers.)

A.  One user can be a member of only one consumer group.

B.  One user can be a member of many consumer groups.

C.  The SYS_GROUP is reserved for the user SYS.

D.  By default, the initial group for all users is DEFAULT_CONSUMER_GROUP.

3.  Some actions in the Resource Manager API are done with procedures in the package DBMS_RESOURCE_MANAGER_PRIVS, and others are done with procedures in the package DBMS_RESOURCE_MANAGER. Which package is needed for each of these actions? (Choose the best answer.)

A.  Granting the privilege to administer Resource Manager

B.  Placing users in groups

C.  Removing users from groups

D.  Switching a session’s effective group

E.  Creating consumer groups

F.  Configuring how to map sessions to groups

4.  Resource Manager plans can use a number of methods to control resources. Which of the following are possible? (Choose three correct answers.)

A.  CPU usage

B.  Tablespace quota usage

C.  Number of active sessions

D.  Number of idle sessions

E.  Volume of redo data generated

F.  Volume of undo data generated

5.  A CPU method plan allocates resources at two levels, as follows:

Level 1: SYS_GROUP, 50% OLTP, 50%

Level 2: DSS, 50% BATCH, 50%

If the only users logged on are from the BATCH group, what percentage of CPU can they use? (Choose the best answer.)

A.  They can use 12.5 percent.

B.  They can use 25 percent.

C.  They can use 50 percent.

D.  They can use 100 percent.

E.  The plan will not validate because it attempts to allocate 200 percent of CPU resources.

6.  You create a Resource Manager plan limiting the active session pool for the group DSS to 3. What will happen if three members of the group are logged on and a fourth member attempts to connect? (Choose the best answer.)

A.  The new session will not be able to connect until an existing session disconnects.

B.  The new session will be able to connect but will hang immediately.

C.  The new session will be able to connect but will be able to run only queries, not DML statements.

D.  Any statements the new session issues may hang, depending on other activity.

7.  If the active Resource Manager plan specifies that sessions belonging to a particular group may have only four parallel execution servers, what will happen if a session in that group issues a statement that requests six parallel execution servers? (Choose the best answer.)

A.  The statement will not run.

B.  The statement will run with four parallel servers.

C.  It will depend on the setting of the PARALLEL_MIN_PERCENT instance parameter.

D.  It will depend on the setting of the PARALLEL_AUTOMATIC_TUNING instance parameter.

8.  When you use Resource Manager to define an undo pool, what happens? (Choose the best answer.)

A.  If a user exceeds their quota on the undo tablespace, their session will hang.

B.  If a user exceeds their quota on the undo tablespace, the statement running will be rolled back, but the rest of the statement will remain intact.

C.  If a group fills its undo pool, all the group’s transactions will hang until one session commits, rolls back, or is terminated.

D.  The effect depends on whether RETENTION GUARANTEE is enabled for the undo tablespace.

9.  How can you determine what is the active Resource Manager plan? (Choose two answers.)

A.  Use SHOW PARAMETER RESOURCE_LIMIT.

B.  Use SHOW PARAMETER RESOURCE_MANAGER_PLAN.

C.  Query the DBA_RSRC_PLANS view.

D.  Query the V$RSRC_PLAN view.

10.  You notice that sessions have been waiting on the resmgr:cpu quantum wait event. What might this indicate? (Choose the best answer.)

A.  That no Resource Manager plan has been enabled

B.  That the system has hit 100 percent CPU usage

C.  That the number of sessions has reached the limit imposed by the SESSIONS parameter

D.  That the CPU_COUNT has been exceeded

Self Test Answers

1.      C. This is the correct sequence, although d, b, e, c, g, f, a will also work.
    A, B, D, and E are incorrect. None of these sequences will work because the pending area must be active when working with groups and plans and cannot be validated after it has been submitted.

2.      B. One user can be a member of many groups, although only one membership is active at any time.
    A, C, and D are incorrect. A is incorrect because there can be a many-to-many relationship between users and groups. C is incorrect because it is possible to put other users in the SYS group. D is incorrect because SYS and SYSTEM are, by default, in the SYS_GROUP group.

3.      A, B, and C: DBMS_RESOURCE_MANAGER_PRIVS; D, E, and F: DBMS_RESOURCE_MANAGER. The DBMS_RESOURCE_MANAGER_PRIVS package handles security, whereas the DBMS_RESOURCE_MANAGER package manages everything else.
    All other possibilities are incorrect.

4.      A, C, and F. The emphasis method controls CPU usage. Active sessions and the volume of undo data are two of the absolute methods.
    B, D, and E are incorrect. Tablespace usage can be limited by quotas, not by Resource Manager. Idle sessions can be timed out, but not limited in number. Redo volume is not a possible limit.

5.      D. If no other sessions are connected, all CPU resources will be available to the connected sessions.
    A, B, C, and E are incorrect. A, B, and C are incorrect because they misinterpret the “trickle-down” nature of resource allocation. E is incorrect because it fails to appreciate that CPU is allocated at each priority level, not across priority levels.

6.      D. The session pool does not limit the number of sessions, only the number of active sessions.
    A, B, and C are incorrect. A is incorrect because it describes the effect of session limits in profiles, not Resource Manager. B is incorrect because this result would occur only if the active session pool were full. C is incorrect because Resource Manager makes no distinction between the types of SQL statements.

7.      B. The limit will override the request.
    A, C, and D are incorrect. A is incorrect because the intent of Resource Manager is not to block statements but to control them. C and D are incorrect because they refer to the instance parameters that drive the optimizer, not Resource Manager.

8.      C. Undo pools refer to whole groups, not to individual users or sessions. If a group fills its pool, all sessions that are part of the group will hang until one issues a COMMIT or a ROLLBACK.
    A, B, and D are incorrect. Tablespace quotas are relevant to neither undo in general nor Resource Manager. RETENTION GUARANTEE does not apply either.

9.      B and C. The active plan is set with the RESOURCE_MANAGER_PLAN parameter and displayed in the V$RSRC_PLAN dynamic performance view.
    A and D are incorrect. A is incorrect because the RESOURCE_LIMIT parameter is relevant to profiles, not to Resource Manager. D is incorrect because the DBA_RSRC_PLANS data dictionary view shows the configuration of Resource Manager, not its current state.

10.      B. Once a system reaches 100 percent CPU usage, a CPU plan will come into effect and start limiting sessions’ usage.
    A, C, and D are incorrect. A is incorrect because if no plan is enabled, there can be no waits on this event. C and D are incorrect because although these parameters do control resource usage, they do not do this through the Resource Manager plan.