| |
 |
|
Oracle Tips by Burleson |
Monitoring User Consumer
Groups and Plans
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
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. |
