Managing Resources.
If I were to think of one word that serves as the key frame
or keystone for this Nugget, it would be
the idea of the workload.
We know about the Automatic Workload Repository.
We know how to use SQL tuning tools in Oracle, where we
create a workload and analyze it.
In this Nugget, we look at workloads from a slightly
different perspective.
We're looking at more of a hardware perspective here.
In other words, you obviously have different groups of
sessions coming into Oracle--
your end users, who may be more interested in transaction
processing.
At the same time, you probably have some reporting folks in
there who are doing analysis.
At the same time still, you might have maintenance jobs in
batch and extract, transform load jobs going on.
Maybe the application itself wants to
get into the database.
This constitutes what's called a mixed workload.
And the idea here is how can we guarantee quality of
service for some sessions?
How can we make sure, for instance, that our batch jobs
or maintenance task jobs don't consume too much CPU and close
out our users?
On the other hand, how do we prevent certain user
populations from consuming too much time on the server, too
much CPU, et cetera?
Well, the answer to that is a technology that's been around
in Oracle for a while, so it's fairly mature.
It's called the Database Resource Manager, also call
just the Resource Manager.
And as we're going to learn this Nugget-- we're going to
learn what it is, why it's useful, and then how to
actually use it.
We'll create the various Resource Manager components.
And by the end of this Nugget, you'll certainly be able to
answer successfully any questions about resource
manager that you'll see on your OCP Certification exam.
Let's get started.
So as I said, Database Resource Manager addresses
these problems--
first of all, the notion of the mixed workload.
The fact that the host operating system, regardless
of whether you're running Oracle on Windows, Linux, or
Mac OS X, the OS has no knowledge of Oracle's
priorities.
The operating system can schedule
processes all day long.
But the problem with that is Oracle itself is
one of those processes.
And then, of course, you have your instance sub processes,
like the Manageability Monitor and
Checkpoint Process, et cetera.
But we can't get down to the granular workload aspect at
the operating system level.
So that's specifically were Resource Manager comes in to
fill the gap.
It's a QoS, or quality of service tool in which we can
guarantee session parameters.
CPU is just one of many parameters that we can
schedule through the resource manager, but we can guarantee
sessions a certain level of service and
prioritize them thusly.
Now, that guarantee--
I said it goes to user groups.
That's not the whole picture.
Specifically--
and we'll see this in Technicolor in the demo--
we provide quality of service to Oracle sessions.
In a sense, Oracle doesn't care whether that session
represents an Oracle user, a process, an application.
It has no care about that.
But you'll see that it's crucial for us as
administrators to take those different
sessions into account.
We're actually going to form what are called consumer
groups of those different session types.
And then the consumer groups get plugged
into a resource plan.
I'm getting a little bit ahead of myself, though.
As I said, CPU is just one thing that can be scheduled.
And it's an important aspect of hardware, no
question about it.
As I said, you may want to make sure that at least two
during specified times, sys processes always run at a
higher priority or have more CPU access than anybody else
who may be hitting the server.
And I mentioned the term windows.
We haven't formally specified maintenance windows yet.
We haven't formally explained them, I mean.
We'll do that in the Nugget when we discuss
the scheduler, formally.
But what's especially flexible about Resource Manager is that
we can create a plan that's really granular, that cuts up
CPU among these different consumer groups, and then we
can attach the schedule, or we could create multiple
schedules, actually-- multiple plans, I should say-- and plug
those into different scheduler windows on our system.
So that maybe during the day plan A is in effect that has a
certain weighting of CPU and other resources to different
groups, and then at night we automatically switch to a
night plan that has a different set of processor
priorities.
Isn't that awesome?
Now, look at some of the other aspects that we can schedule
or customize in our resource plans.
The degree of parallelism-- and this, of course, refers to
Oracle's parallel execution of SQL statements.
Increasing the degree of parallelism for a session is
going to give that session much more
processing power, of course.
Undo pool size--
we can stretch or contract the amount of disk space a session
can create in undo.
Concurrent active sessions--
you wind up giving a consumer group a
session pool, basically.
Maybe if we set this to 5 for our daytime staff, the sixth
session that comes along is going to be put in essentially
a waiting area or an on-hold area, and once somebody or
another process logs off and opens up a slot, this waiting
user can come in.
Execution and idle time limits-- after a certain
amount of idle time, you're disconnected.
Execution time means that processes that hold resources
and therefore hold locks and latches for x number of
seconds or minutes will get terminated unless they're able
to finish within that time limit.
Actually, if you studied the DBA 1 Oracle skill set, a lot
of this Resource Manager stuff, these parameters that
we can schedule and prioritize, are similar to the
notion of user profiles, where we can customize an Oracle
user's environment.
This works very much the same way, but of course the purpose
here is fundamentally different.
Here, we want to guarantee quality of service for
specific session populations in Oracle.
Now let's get to the really important stuff
for your exam success.
It's absolutely crucial that you be able to differentiate
among these Resource Management components.
I've already kind of slipped in some of the terminology
just to get you initially familiar with it.
A resource consumer group, or, for short, consumer group, is
a user or a session.
As you'll see, consumer groups can actually be mapped to more
than just Oracle users.
You can plug in application names, service names, host
names, computer host names.
It's very rich and robust in terms of how
you define a session.
But the bottom line with the resource consumer group, it's
a logical entity that contains mapped sessions that share
similar resource requirements.
Now, how you grow your sessions is completely up to
your workload.
I gave the example of daytime and nighttime processing.
Maybe you want to favor OLTP during business hours and you
want to favor OLAP and maintenance tasks at night, to
wit, you'll want to group sessions who are doing OLTP
stuff into one consumer group.
OLAP might be a second consumer group, maintenance
tasks might be a third.
You can encompass as many of these groups as you want.
I would suggest that you keep it as simple as you can while
being as granular as you need to be to
cover all of your workloads.
Now, a resource plan represents a blueprint for
resource allocation.
In this schema, you would have one resource plan for day use
and another resource plan for night use.
What can become kind of complicated is the fact that
Oracle can have only one plan active at once.
Now, in this case, day and night, that's pretty easy.
But what if you had subsets on your day group?
Maybe you had some OLTP'ers, but you also
had some batch jobbers.
You see what I'm saying?
And you wanted to further partition resources among
those subgroups.
As it happens, you can create sub plans.
So you would have top-level plan that divides CPU,
parallelism, and the like not among consumer groups, but
among sub plans, which themselves partition those
resources on the basis of consumer groups.
So that's how we get around the notion that Oracle can
have only one Resource Manager plan active at once, and we
can, in fact, embrace multi-level
more complicated schemas.
Now, within each resource plan, the actual rules are
called Resource Plan Directives.
So if I were to draw this schematically, we would have a
resource plan that consists of one or more directives or
rules, and each of those rules is going to
specify a consumer group.
If you've worked with firewall configuration, VPN
configuration, you're used to the notion of rules.
And it works very similarly here.
We want to create rules that are granular.
For instance, maybe you want to always reserve a certain
amount of CPU for the sys users, and then any left over
resources go to a second level of priority, where you can
divide them among two or more or just one consumer group.
You can actually go out eight levels of
priority if you want to.
Again, I would suggest, though, that you try to be a
simple and straightforward as you can with your plans.
Before we head out to the demo, I want to show you some
extracts from the Oracle documentation.
I provide the reference down below so you can check out the
context at your leisure.
This whiteboard shows a simple sample resource plan from the
Oracle docs.
And it mirrors what I've been talking about in passing,
having, say, a daytime plan and a nighttime plan.
This one just shows a daytime resource plan that consists of
three directives.
And again, this is really simple.
We're just concentrating on CPU utilization here, such
that we're devoting 75% of the CPU to the consumer groups
OLTP, 15% of the CPU to reporting, and
10% to other groups.
So it looks like the OLTP sessions are always, when the
server is CPU-starved, going to favor the OLTP, and
reporting will always have favor
CPU-wise over other groups.
Now, this other groups is pretty important.
There's actually several
pre-configured consumer groups.
OLTP and reporting would have been created by the DBA.
They're not in box.
But there are at least two consumer groups that you need
to remember for the exam.
Other groups is one and the other is called sys group.
Now the sys group built-in consumer group automatically
is mapped to all sessions created by the
sys or system accounts.
If we wanted to create a directive that customized
access for the administrators, we would invoke the sys group
built-in consumer group for that directive.
By contrast, a resource plan always has to have a directive
that's mapped or associated with the built-in other group.
Now, those of you who have done VPN and remote access and
firewall configuration and have done rules along those
lines know that you always need to have a catch-all.
I always think of rule sets as a filter basket.
You get all of these incoming connections coming in, and
your rules are basically nets within that filter basket.
The first net might catch some users or some sessions.
The second net might catch some more.
Eventually, you're going to come up against sessions that
aren't affected by any of your other rules.
So you don't want them to slip out of the filter basket into
your coffee.
It's going to taste bad.
[LAUGHING]
Instead, you want to always have a default rule at the end
that catches anybody who hasn't been picked up by a
previous directive.
This is where other groups fits in.
It applies to all sessions that belong to a consumer
group that's not part of the active plan.
And just, like I said, is not affected by any of your other
directives.
I also want to make a comment based on this picture that
these percentages only go into effect when the CPU
utilization becomes in question.
In other words, when it's 100% when Oracle is, in fact,
coming up against CPU contention, then it will start
provisioning according to these percentages.
These don't represent caps or a maximum CPU.
They represent minimum CPU.
That's an important distinction.
So in the event of CPU being pegged, we can guarantee in
directive 1, that OLTP will have at least 75% access to
the CPU, reporting will have at least 15%,
other groups 10%.
Now, if OLTP are not doing anything-- maybe those folks
are out to lunch--
and the reporting people aren't doing anything in
Oracle at the moment, it's possible that other groups
could have 100% of the CPU.
It's always a case of spillover and just
guaranteeing a particular CPU minimum.
Now, as we're going to see in the demo, some of the other
provisions that we can add to directives are
more cut and dry.
The degree of parallelism, for instance, is
just an integer value.
The maximum of concurrent sessions.
CPU is a little bit more fluid in terms of how it's
provisioned.
Also you might be thinking to yourself, what if a user
belongs to more than one consumer group?
In other words, what if we had Joe User or Jane user or an
application process, some session that was associated or
mapped to both OLTP and reporting?
How would we know which one should go into effect?
Which one takes priority?
Well, there's two things we can do there.
As I mentioned, you have up to eight levels of priority such
that we can specify OLTP as being a higher priority
consumer group and higher priority directive than the
one for reporting such that if the session were in both
groups, they would be associated with OLTP.
That's one way.
The other way is called consumer group switching.
We as an administrator can take a session out of one
group and switch it to another such that in this case, if we
had OLTP prioritized above reportings directive, but this
particular session we wanted to make sure was in reporting,
we can make that switch happen.
That's no problem.
I also want to show you that out-of-the-box resource plans.
The thing that I really admire about Oracle is that they take
something that's very complicated--
like Resource Manager is very complicated and consists of
many moving parts.
They make it as easy as possible for us DBAs and
Oracle application developers to get off the ground.
In other words, they've given us a bunch of out-of-the-box
stuff to start with.
I mentioned two of the built-in consumer groups, sys
group and other groups.
Here, we're looking at pre-built resource plans.
And you'll see that they cover really a lot of the standard
bases that most businesses have on their plates who are
using Oracle in an enterprise framework.
The default plan is a general purpose plan
that prioritizes sys.
That's all this is.
So you could just simply activate this plan out of the
box, blind, to guarantee that sys accounts, that
administrators will have more privilege on the system than
anybody else.
Default maintenance plan-- another pre-done, canned,
out-of-the-box plan that's associated with maintenance
windows that caps CPU resources for
your maintenance tasks.
We're going to revisit this in the next Nugget when we deal
with the scheduler.
It's always a concern for many DBAs that they feel a little
nervous about instituting automatic maintenance tasks
because there's a thought, well, I can't control how much
CPU they're using, and I'm afraid that even if I run
these jobs at night, that might interfere with some
other things I have going on on the server.
Well, something like the default maintenance plan--
which we can customize.
You can always come in and tweak up these pre-configured
resource plans.
They're not carved in stone.
But here, we can rest assured that when the default
maintenance plan plan is active, we're preserving CPU
for those tasks.
Mixed workload is a general purpose resource plan.
DSS--
DSS, you might recall, stands for Decision Support System.
Extract, transform, and load, batch, and then we have one
that prioritizes ETL over DSS.
These are basically opposites of each other.
And as you'll see in our upcoming demo, we're going to
drill in and take a look at the rules or directives behind
each of these pre-configured plans.
Because quite honestly, friends, you may just want to
stay with these out-of-box plans.
Maybe tweak them up a little bit to suit your needs, but
you don't necessarily have to start from scratch and build a
plan from the ground up.
I just want that to be crystal clear to you.
Finally, I wanted to show this table.
This is an important table from the Oracle documentation
that shows the group mapping rules.
Remember, I mentioned that the consumer group is a logical
entity that's meant to aggregate sessions with
similar resource needs.
Now, how do you populate those groups?
As a systems administrator, you know, for instance, in
Windows we could create, say, a global group, a domain
global group, and populated it with user accounts.
Simple as that.
It's kind of the same idea here, only it gets a little
bit more abstract because we have the notion of the session
rather than strictly speaking a user.
Now, certainly when Joe or Jane User uses their own
Oracle account to connect to the database, that is a
session that happens to correspond to an Oracle user.
That's true.
But it doesn't have to be that way.
It could be a module within your application.
It could be a specific sub routine within a code module.
You see?
Look at all these options we have for tying your
application code and SQL code into a consumer group.
Think of processes.
We have client program.
We can map to operating system users.
We can map to host names, client IDs.
The idea here is that we can create consumer groups that
map to whoever or whatever is logging in to Oracle, be it a
module within our line of business application.
It could be an Oracle sub process.
It could be a user, as I said.
It could be a machine.
Now, let's finally do that demo I keep referencing so you
can see what this stuff looks like.
Welcome to our demo on Oracle Database Resource Manager.
We're going to start this nugget in SQL Developer, then
we'll go into Oracle Enterprise Manager, and then
finally return to SQL Developer.
Because I want to show you Resource Manager components
both from Database Control, GUI interface, as well is by
using the PL/SQL package itself.
You might be asked questions that require you to know a bit
about the API here.
Now, as you'll see in my sample file here, I wanted to
start you off by just showing you how simple it is to
initiate a plan on your system.
Alter system set.
The initialization parameter, the one and only one for
instituting a plan, is resource manager plan.
And you just specify the name of your plan.
This could be either a built-in plan, like default
plan, or it could be one you've created yourself.
And then there's also a parameter for sid.
That's all there is to it as far as turning on or
activating a plan on your system.
Now, the question--
this is probably question and answer one.
Question and answer zero would be is resource manager enabled
on my system?
Oracle's receive some flak from the user community in
recent versions of Oracle Database in terms of whether
your default installation has Resource Manager turned on or
turned off.
Some DBAs hit the roof, for instance, when they upgraded
to 11gR1 and realized, wait a minute now,
what's going on here?
Why is my CPU automatically being throttled?
Well, as it turned out, Resource Manager was turned on
in those systems by default.
We can get an answer to that question on any Oracle 11g
system by running a query against the v$rsrc_plan
dynamic performance view.
Let's run that right now.
I'm going to connect to sys.
If the query returns a row, then that means you do, in
fact, have a plan active.
I don't, as you see.
So this means that Resource Manager is effectively
disabled on the system.
Another useful query I just want to give you right at the
start answers the question which consumer group is a
particular session in.
That's obviously something we want to know once we get a
resource plan off the ground.
There's doubtless going to be a period of debugging and
troubleshooting when you roll one of these out.
We can select sid and resource consumer group from v$session,
and this will give you a list of session IDs as well is
their consumer group mapping.
Obviously they're all null in this case because I don't have
a plan active at this time.
If I come to the top line and initiate a plan, which I just
did, we now can rerun the second query and learn that we
have the default plan turned on.
And finally, if we run this third diagnostic or monitoring
query, we now have options.
We have session IDs as well as a reference of which built-in
groups these users fall into.
And essentially it's going to be other groups, Oracle
background group, my connection as sys will be in
the sys group.
And then as you deploy additional groups and add them
to your own resource plans, you'll see your own
personalized results to that.
Now, if you want to delegate the privilege to administer
Resource Management to another administrator but you don't
want to make the user a system administrator, sys DBA, you
can run the Grant System Privilege routine from DBMS
Resource Manager privs.
In this case, this bit of code is going to grant the Scott
built-in user the ability not only to manage Resource
Manager, but because the Admin option is turned on, Scott can
actually delegate Resource Manager privileges to yet
other Oracle users.
The rest of the code in the sample file goes through a
fictional case where we want to create a resource plan that
gives a consumer group, called Reporting Group, a maximum of
five active sessions and 5% of the CPU.
So this reporting group is going to get heavily throttled
from the looks of it.
And the code that we'll be using in the sample has been
adapted from, of all places, and obscure
IBM reference site.
This is the shorty URL that'll take you directly to that
article by way of citation.
Now, I told you I wanted to show you the graphical
interface of Resource Manager.
That's what we'll do right now.
I'm going to log into Database Control using the sys account
with sys DBA privilege.
We can find Resource Manager on the Server tab.
It actually has its own group carved out for it, and it's
divided into six sessions.
Getting Started, which is a really nice helpful overview
that helps us walk through Resource Manager-- it's
definitely where I recommend you get started with this.
And then we have specific links to drill into each of
those components--
consumer groups, consumer group mappings, also called
consumer group memberships, plans, settings, and
statistics.
We'll go to Statistics first.
There's not going to be too much--
well, there is some stuff to see now.
I just remembered that we do have a plan enabled.
And this is going to give us color-coded histogram that we
can customize Automatic Refresh or Manual Refresh, as
you see here, that shows you those different metrics.
CPU-consumed, depending upon the group-- it looks like our
other groups are consuming more CPU than
I am as a sys DBA.
I/O requests, I/Os per second, weights, and then we have
queued sessions, reprioritization,
idle session data.
This is a good screen to go to when you have a resource plan
in use and you want to see basically metrics on how it's
performing--
who's doing what is a good way to say it.
The Settings page, as you see here, allows us to choose a
resource plan from the dropdown list.
I haven't created any on my own, so all of what you're
seeing here are built-in or in-box with Oracle 11g
Enterprise Edition R2.
I want to draw your attention here to the default
maintenance plan.
This is the built-in plan that's meant to customize
maintenance plan tasks.
You can activate a plan manually from this setting
screen just by clicking Activate, or we can click View
Selected Resource Plan to go to the View
page, as you see here.
And this is showing, first of all, the metadata in terms of
its status, it's description, which scheduler window or
windows it's associated with.
And it looks like this plan is attached to
every window each week.
So it will automatically kick in depending
upon your window timeframe.
As I said, we'll revisit windows in the next Nugget.
The guts of it is down here, under Resource Allocations and
Directive Values.
The Resource Allocation deals with the CPU utilization.
And it looks like the first priority, or the level one
priority, gives the sys users 75% of the CPU.
Remember, this is just a minimum guarantee when the CPU
is pegged on the system.
Unless a session or the server is starved for CPU, there will
be no need for the plan to step in.
But if the plan does need to step in and actually exert
some of this muscle, sys users are guaranteed 75% of the CPU.
Now, the remaining 25% cascades down to level two, as
well as any overflow.
If the sys group is only using 25% of the CPU, then that
gives an additional 50%, the 25% that's left over, to
cascade to level two.
You see how that works?
And in that schema, it looks like the auto task sub plan,
which is a plan on its own, has 25% guaranteed CPU, and
the diagnostics, whatever that is--
I would guess that that's a consumer group-- gets 5%.
Now, we would have to step back a window to look at the
components of the auto task sub plan, but I would reckon
that your maintenance tasks are associated as consumer
groups within that sub plan.
The rest of this deals with other metrics--
execution time limits, I/O time limits, as we said
earlier, maximum undo space, execution times.
And you'll see that this default plan doesn't mess with
any of that.
All of those are set to their default values.
It looks like this default maintenance
plan, all it does--
and actually we can read this from the description.
It's set for maintenance windows that prioritize the
sys operations, allocates the remaining 5% to diagnostic,
25% to automated maintenance operations.
So we can rest assured that no more than 25% of the system
CPU is going to be chewed up by maintenance.
Now, you may want to devote more processing power to the
auto task sub plan, here.
You can easily do that, as I said, by editing
a maintenance plan.
And as you've come to expect, Oracle Enterprise Manager is
kind of a loony labyrinth in terms of there's like three or
four different pathways to get to each screen.
I'm just going one of many.
But as you see here in the actions area,
we can click Edit.
And this allows us to customize exactly what's going
on with regard to those allocations.
For instance, we know that this is going to add up to
100%, right?
We might want to boost the auto task up to 50%, and maybe
we'll make other groups 45% and ORA$DIAGNOSTICS 5%.
We can do that if we want to.
If we click Show SQL, this shows us exactly
what's going on.
And it's giving us a pretty honking large SQL block.
The reason I'm showing you this, friends, is to see that
under the hood, what's going on is that Oracle is calling
the DBMS Resource Manager package.
This is the big daddy package, so to speak, that covers the
Resource Manager.
Basically, the vast majority of your operations with
Resource Manager come through subroutines of the DBMS
Resource Manager PL/SQL package.
Now, you'll notice these calls to something called the
pending area.
This is one of the weird aspects of Resource Manager.
Consider that a scratch space.
If you're going to create your plans from scratch--
I guess pun intended--
using PL/SQL, the first thing you always want to do is clear
out an existing pending area that might be there with clear
pending area and then create a fresh one.
That's just, like I said, one of those things.
You have to have a pending area, and in-memory scratch
space available, to do your plan.
Then you do your plan itself, and this shows really just the
directives of each plan.
Basically what you saw on the GUI, this is just showing you
the details with PL/SQL.
But once you've finished your plan, it's a question of
submitting the pending area, which takes your scratch
space, your in-memory scratch space, and commits it to disk
and commits it logically to the data dictionary so it'll
stick around.
And then, of course, there's the question of activating the
plan, which I showed you how to do before with alter system
set, resource manager plan = the name of the plan.
So let's return and make sure that my change is still there.
And you see that for Resource Allocation, you can switch
between Percentage and Advanced.
I tend to stick with Advanced because it gives us more
flexibility.
For instance, you might, as a general practice, always want
to make sure your first level gives sys group priority, and
then any leftovers CPU goes to additional
groups or sub plans.
Secondarily, remember you always have to have a
directive that covers the other groups
that serves as a catch-all.
I'm going to apply this change to show you, or basically to
prove that we can change those built-in plans.
Now then, let's come back to the main page and come to
Getting Started.
I showed you some of the settings and statistics.
The Getting Started page essentially gives us some
verbosity or some language that tries to teach you, in a
really encapsulated way, what I've been teaching you myself
over the last half hour or so.
It gives you handy dandy links to each building block-- the
consumer groups, the consumer group mappings, the plans, and
then we've already done the settings and the statistics.
If we go into Consumer Groups, that's going to give us a run
of all of the built-in default consumer groups.
They've got really whacky names.
Some of them are fairly easy to understand.
For instance, auto task consumer group, I guess we can
assume is a consumer group that deals with or is mapped
to maintainance processes.
Let's see.
What else is there?
Interactive group for interactive OLTP, ETL.
None of these are in use unless they're part of on an
active current plan.
They're just pre-configured on Oracle's part to give us
something to work with based on different scenarios.
Creating a consumer group is really easy.
Let's click Create.
I'm just going to call this Test_Group.
Scheduling policy is Run to Completion or Round Robin.
I would recommend you leave Round Robin as the default.
And the second option is Users Permitted to Run in this
Consumer Group.
And we can click Add and add individual users here.
I'm going to manually associate HR, for instance.
Click OK.
If we come back Home and go to Consumer Group Mappings, this
determines how Oracle can automatically assign sessions
to consumer groups.
You saw just a moment ago how to manually attach a user to a
consumer group, but it's not always that easy.
Sometimes you might have processes, applications that
you want to dynamically associate with the consumer
groups such that when that module in action is fired off,
it automatically joins a particular group.
When a particular client program makes a connection to
Oracle, same thing.
When a particular client machine
connects, and so forth.
So the idea here is we can select a connection type--
for instance, client machine--
and then choose Add Rule for Selected Type.
In this case, the screen is giving us a run of available
client machines.
In this case, it's just the local server, Oranugget.
And then we take that machine and apply it to a specific
consumer group.
For instance, Test-Group.
So what this is saying is any incoming connections from
Oranugget are going to automatically be mapped to the
Test_Group consumer group.
On row six, I didn't do this.
This is a default mapping this says if Oracle users sys or
system connects, there automatically
dumped into sys group.
You see how that works?
So you'll want to spend time here on the Consumer Group
Mappings and make sure that you have whenever session
types going on in your organization that you need
mapped correctly.
In fact, I don't want Oranugget, which is my server,
to be associated with Test_Group.
So you notice that when you create a mapping, you can
remove it by clicking this little pencil and paper.
It goes away almost immediately.
If we go to the Plans page, this shows us again our
default plans.
We've kind of been there and done that earlier when we
looked at the default maintenance plan.
This time, let's go to default plan, which is actually active
on the system right now.
And this default plan, again, doesn't seem to touch anything
else other than CPU.
It looks like the sys group gets 75% of CPU guaranteed out
of the box.
Any left over, it looks like, is heavily favored toward
other groups, which is your catch-all bucket-- basically
any other session.
But it looks like we are guaranteeing 5% CPU for our
maintenance tasks.
And that may be just ducky for you.
Maybe you don't care about automatic tasks, so you could
cut that down or bring it to zero.
It's completely up to you on how you want that to work.
Now, in terms of creating your own plan from scratch, let's
come back to SQL Developer.
And you'll see here our goal is to create a plan such that
a particular consumer group has five active sessions and
only 5% CPU.
The first thing we want to do is create our pending area.
So in this block, I'm going to call DBMS Resource Manager.
We're going to clear out any pending area that may be
active right now and create one from scratch.
Next in this example, we'll create some test users that we
can and will associate with our consumer group.
I'm just creating simple users called report1,
report2, and report3.
To create the consumer group using PL/SQL, what we do there
is call DBMS resource manager create consumer group, and the
parameters are the name of the group, which is going to be
called reporting group.
And you can add an optional comment.
I would recommend using comments wherever possible.
They show up in the graphical user interface, as you see.
And for something as potentially complex as
resource plans, documentation is especially helpful.
All right.
So there's our consumer group.
We haven't created any mappings for it yet.
We haven't populated it with any sessions.
But the actual plan itself will create the plan called
Daytime, and this is going to govern Resource Management for
reporting group applications.
And then we use Resource Manager Create Plan Directive
to fill out each directive.
It's frankly not anywhere near as intuitive as filling out
the grid that you saw in Oracle Enterprise Manager.
It looks like our level one priority is to give the sys
group 100% CPU.
Now, that's pretty generous, I would say, wouldn't you?
It's just going to be whatever is left over.
That is, if the sys group is using 70% CPU, then any
spillover is going to go over into level two, which is
MGMT_P2, priority two, in which we're specifying the
reporting group getting 5% of the CPU.
And it looks like we've got a third level priority that we
bring in other groups--
that any CPU left over that is 100% is devoted to them.
So there is our plan-- relatively simple.
Actually, it's extraordinarily simple.
We really don't need to get any more
complicated than that.
We want to make sure you understand
how this stuff works.
Our next step is to validate the pending area, submit the
pending area, and then to be a good Oracle user or DBA--
clear it out for the next DBA's work.
Remember, submitting is going to commit our work to the data
dictionary.
And that's what we want to see down here in the status area.
So right now we've created the consumer group, we've created
our plan, we've defined our directives.
We need to add users or sessions to
those consumer groups.
So again, we're going to open up another pending area and
we're going to run DBMS Resource Manager set consumer
group mapping, and that involves and nested call to
the Oracle user routine.
There's going to be a different sub routine for each
of those mapping types.
Oracle user, remember, is just one of them.
We'll take report1, report2, and report3, assign those
users to the reporting group consumer group.
Again, we validate the pending area, submit it, and clear it.
So let me highlight that information and we'll go ahead
and run it.
Before we quit, we have to put this plan into effect--
enable or activate the plan.
We already know the initialization parameter and
use for that.
So now we can scroll all the way up to the top, verify just
with our own eyes, that we have that resource plan in
use, and we can always check which sessions are part of
which consumer group by using the second query.
Managing Resources review--
in this Nugget, we tackled really a gargantuan subject.
There's no question about it.
The Oracle database resource manager is quite a beast.
But I hope that I described it in such a way that, without
minimizing its importance or complexity, I've translated to
you what the tool is, what we use it for, what it's business
use cases are, and then, of course, you understand how the
various and sundry components or moving parts of resource
manager work together--
the resource plan, the consumer groups, the
directives.
We understand how this works using the PL/SQL API, as well
as through Oracle Enterprise Manager.
We're going to revisit a little bit of this stuff, as I
said before, in the next Nugget, in which we cover the
Oracle Scheduler, maintenance plans,
and maintenance windows.
I hope that this has been informative for you, and I'd
like to thank you for viewing.