Script:Diagnostic Resource Manager

以下脚本可以用于诊断Oracle 10g以后的Resource Manager信息:

set echo on;
    set linesize 300;
    set pages 1000;
    set numwidth 10;
    set trimspool on;
    col VALUE for a30;
    col ATTRIBUTE for a15;
    col GRANTEE for a25;
    col CPU_METHOD for a15;
    col COMMENTS for a30;
    col MGMT_METHOD for a15;
    col STATUS for a10;
    alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
    spool info.lst;
    SELECT SYSDATE FROM DUAL;
    SELECT * FROM DBA_RSRC_MANAGER_SYSTEM_PRIVS;
    SELECT * FROM DBA_RSRC_CONSUMER_GROUP_PRIVS;
    SELECT * FROM DBA_RSRC_CONSUMER_GROUPS;
    SELECT * FROM DBA_RSRC_PLANS;
    SELECT * FROM DBA_RSRC_PLAN_DIRECTIVES;
    SELECT * FROM V$RSRC_CONSUMER_GROUP;
    SELECT * FROM V$RSRC_CONSUMER_GROUP_CPU_MTH;
    SELECT * FROM V$RSRC_PLAN;
    SELECT * FROM V$RSRC_PLAN_CPU_MTH;
    SELECT * FROM DBA_RSRC_MAPPING_PRIORITY;
    SELECT * FROM DBA_RSRC_GROUP_MAPPINGS;
    -- For 10gR2 -------
    SELECT * FROM V$RSRC_CONS_GROUP_HISTORY;
    SELECT * FROM V$RSRC_PLAN_HISTORY;
    SELECT * FROM V$RSRC_SESSION_INFO;
    -- FOR 11gR1 -------
    SELECT * FROM V$RSRCMGRMETRIC
    order by BEGIN_TIME,END_TIME,CONSUMER_GROUP_ID;
    SELECT * FROM V$RSRCMGRMETRIC_HISTORY
    order by BEGIN_TIME,END_TIME,CONSUMER_GROUP_ID;
    spool off;

 create_sample_plan.sql
    ----------------------------------------------------------------------------
    set echo on
    begin
       dbms_resource_manager.create_pending_area();
    end;
    /
    begin
       dbms_resource_manager.create_plan(
          plan => 'ONLINE_PLAN',
          comment => 'Resource plan/method for Day Time On-Line sessions');
       dbms_resource_manager.create_plan(
          plan => 'BATCH_PLAN',
          comment => 'Resource plan/method for Night Time Batch sessions');
    end;
    /

opatch lsinventory -detail

 top -b -d 1 -n 3600 >> toplog
 sar -P ALL 1 3600 >> sarlog

ALTER SYSTEM SET resource_manager_plan='';