以下脚本可以用于诊断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='';
