Lesson 09
===============================================================================
01.
Resource Manager (CPU usage, undo generation, number of active session….)
- Database consolidation - multi instance CPU
consumer by instance caging
- Set default by parameter resource_manager_plan
- Other group must in resource plan and level 1
percentage total not 100%
- Can grant administer_resource_manager for other
user manage
e.g. exec
dbms_resource_manager_privs.grant_system_privilege('SCOTT','administer_resource_manager',false);
-OLTP users: more resource, DSS users: fewer
resource, Batch users: least resource
"dbms_resource_manager_privs"
package included
-
resource_consumer_group
-
resource_plan
-
resource_plan_directives
-
resource_allocation_methods
e.g. create
consumer group named "EmpGroup", add the user scott
to"EmpGroup"
create
new resource manager plan named "EmpPlan" which restricts CPU usges
to 50% and undo tablespace to 2MB (under
undo retention policy) and Maximum Number of active session to 1. This plan
only affects "EmpGroup"
consumer group.
EM
> Server > Resource Manager > Consumer Group > Create > EmpGroup
(*round robin) > add Scott.
EM
> Server > Resource Manager > Consumer group mapping > add rule for
selected user > Scott.
EM Server
> Resource Manager > Plans > select "default_plan" and
action "create like" > Go > Plan name
"EmpPlan"
> click modify to add "EmpGroup" to the list of Resource Allocation
> select mode: percentage, type 50
For EmpGroup,
session pool: 1, undo page 200 > OK.
Active
plan by set scheduler or select active (7x24) > go
select
sid,username, resource_consumer_group from v$session;
EM>
Resource Manager > Statistic
* Round Robin: Multi-session have chance to run
statement
* Run to completion: Session 2 need wait Session 1
complete
- change resource plan by "select plan,
num_plan_directives, status, mandatory from dba_rsrc_plans;"
- create scheduler windows for resource manager
EM>
server > oracle scheduler > windows > create > select
"EmpPlan" and set recurring on everyday 9:00 A.M.
- switch or kill session when consumer group achieved
resource limit
begin
dbms_resource_manager.create_plan_directive(
plan=>'EmpPlan',group_or_subplan=>'EmpGroup',
comment=>'EmpGroup',mgmt._p1=>50,
switch_group=>'low_group',switch_io_reqs=>10000,switch_io_megabytes=>2500,switch_for_call=>true);
end;
/
* Consumer_group limit can use by
max_utilization_limit
* instance caging by parameter cpu_count
02.
Oracle jobs scheduler
- create normal job step by create program > create
schedule >create job
e.g. create a
program, a schedule and a job, it will insert 1 record to scott.emp
EM
> Server > Schedule > Program > 'name', enable, type PL SQL, insert
into scott.emp values(……..
EM >
Server > Schedule > Schedule > 'name', do not repeat > submit
EM >
Server > Schedule > Jobs >
Schedule
job with resource consumer group (create job class)
EM>
Server > Schedule > Job classes > create 'name', assign 'consumer
group' modify previous job class.
Lightweight jobs (EM not support)
- means easy to create and store less meta data in
database, it is not schema object and cannot assign to other users.
- must be a program (not procedure, executable,
java……)
e.g. begin
dbms_scheduler.create_job(job_name=>'name',program=>'name',
schedule_name=>'name',job_style=>'lightweight');
end;
/
Job chains
- when job 1 succes run job 2, if failed run job 3 …..
Create
few programs > create job chains > step > create rule….
