 |
|
Oracle Tips by Burleson |
Monitoring User Consumer
Groups and Plan
New in Oracle8i was the concept of user
resource groups. A resource group specifies how much of a particular
resource a specific user (or role) is assigned. For example, a CEO
group may get a %CPU assignment of 100, while a clerk may get 40. A
resource group is set up using resource plans. The DBA_RSRC series
of views are used to monitor resource groups, and the
DBMS_RESOURCE_MANAGER and DBMS_RESOURCE_MANAGER_PRIVS packages are
used to maintain resource consumer groups and plans.
Monitoring resource plans involves several
layers of reports. The top layer will report on the overall
structure of the resource plans, directives, subplans, and resource
groups. The next level reports on the directive level and the
different CPU usage specifications. The final level deals with the
group and system-level grants and administration privileges
associated with them. Figure 11.3 shows how the PLAN cluster DBA_
views relate to each other. Source 11.6 is an example of a report
that shows the structure of a resource plan. The output from the
script in Source 11.6 is shown in Listing 11.5.
SOURCE 11.6 Script to generate a report on
Oracle8i and Oracle9i group resource plans.
REM
NAME : RESOURCE_PLAN.SQL
REM
PURPOSE : GENERATE DATABASE RESOURCE PLAN REPORT
REM
Revisions:
REM
Date Modified by Reason for change
REM
15-May-1999 MIKE AULT initial creation
REM
COLUMN
plan FORMAT a16 HEADING 'Plan|Name'
COLUMN
cpu_method1 FORMAT a8 HEADING 'CPU|Method'
COLUMN
mandatory1 FORMAT a4 HEADING 'Man?'
COLUMN
group_or_subplan FORMAT a12 HEADING 'Group or|Subplan Name'
COLUMN
type FORMAT a8 HEADING 'Group or|Subplan'
COLUMN
cpu_method2 FORMAT a8 HEADING 'CPU|Method2'
COLUMN
plan2 NOPRINT
COLUMN
queue_meth1 FORMAT A12
COLUMN
queue_meth2 FORMAT A12
COLUMN
session_pool1 FORMAT A25 HEADING 'Sessions 1'
COLUMN
session_pool2 FORMAT A25 HEADING 'Sessions 2'
REM
SET LINES
228 PAGES 55 VERIFY OFF FEEDBACK OFF
BREAK ON
plan ON cpu_method1 ON mandatory1 ON num_plan_directives
START
title132 'Resource Plan Report'
SPOOL
rep_out\&&db\resource_plan.lis
REM
SELECT
DISTINCT
decode(b.plan,'',a.plan,b.plan) plan,
a.active_sess_pool_mth session_pool1,
a.parallel_degree_limit_mth parallel_meth1,
a.queueing_mth queue_meth1,
b.plan plan2,
a.cpu_method cpu_method1,
a.mandatory mandatory1,
DECODE(b.group_or_subplan,'',d.consumer_group,
b.group_or_subplan) group_or_subplan,
DECODE(b.type,'CONSUMER_GROUP','GROUP',b.type) type,
c.active_sess_pool_mth session_pool2,
c.parallel_degree_limit_mth parallel_meth2,
c.queueing_mth queue_meth2,
decode(c.cpu_method,'',d.cpu_method,c.cpu_method) cpu_method2
FROM
dba_rsrc_plans a, dba_rsrc_plan_directives b, dba_rsrc_plans c,
dba_rsrc_consumer_groups d
WHERE...
See code depot
This is an excerpt by Mike Ault’s book “Oracle9i
Administration & Management” . If you want more current Oracle
tips by Mike Ault, check out his new book “Mike
Ault’s Oracle Internals Monitoring & Tuning Scripts” or Ault’s
Oracle Scripts Download. |
