另附自己前期关于dbms_resource_manager的小记

 

测试场景:
  Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
  Connected as scott@orcl
 
dbms_resource_manage各个组件构成 
SQL> desc dbms_resource_manager;
Element                        Type     
------------------------------ ---------
CREATE_PLAN                    PROCEDURE
UPDATE_PLAN                    PROCEDURE
DELETE_PLAN                    PROCEDURE
DELETE_PLAN_CASCADE            PROCEDURE
CREATE_CONSUMER_GROUP          PROCEDURE
UPDATE_CONSUMER_GROUP          PROCEDURE
DELETE_CONSUMER_GROUP          PROCEDURE
CREATE_CATEGORY                PROCEDURE
UPDATE_CATEGORY                PROCEDURE
DELETE_CATEGORY                PROCEDURE
CREATE_PLAN_DIRECTIVE          PROCEDURE
UPDATE_PLAN_DIRECTIVE          PROCEDURE
DELETE_PLAN_DIRECTIVE          PROCEDURE
SET_CONSUMER_GROUP_MAPPING     PROCEDURE
ORACLE_USER                    CONSTANT 
ORACLE_FUNCTION                CONSTANT 
SERVICE_NAME                   CONSTANT 
CLIENT_OS_USER                 CONSTANT 
CLIENT_PROGRAM                 CONSTANT 
CLIENT_MACHINE                 CONSTANT 
MODULE_NAME                    CONSTANT 
MODULE_NAME_ACTION             CONSTANT 
SERVICE_MODULE                 CONSTANT 
SERVICE_MODULE_ACTION          CONSTANT 
PERFORMANCE_CLASS              CONSTANT 
SET_CONSUMER_GROUP_MAPPING_PRI PROCEDURE
CREATE_PENDING_AREA            PROCEDURE
CLEAR_PENDING_AREA             PROCEDURE
VALIDATE_PENDING_AREA          PROCEDURE
SUBMIT_PENDING_AREA            PROCEDURE
SET_INITIAL_CONSUMER_GROUP     PROCEDURE
SWITCH_CONSUMER_GROUP_FOR_USER PROCEDURE
SWITCH_CONSUMER_GROUP_FOR_SESS PROCEDURE
SWITCH_PLAN                    PROCEDURE
CREATE_SIMPLE_PLAN             PROCEDURE
SHARED_STORAGE                 CONSTANT 
DEDICATED_STORAGE              CONSTANT 
LOG_FILES                      CONSTANT 
TEMP_FILES                     CONSTANT 
RECOVERY_AREA                  CONSTANT 
ASM_DISK_GROUP                 CONSTANT 
MANAGED_FILES                  CONSTANT 
UNMANAGED_FILES                CONSTANT 
CALIBRATE_IO                   PROCEDURE  

--如上type为CONSTANT的数据,请参阅官方手册:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_resmgr.htm#CFAGGDBD

--Summary of DBMS_RESOURCE_MANAGER 包子程序列表及注解,请参阅官方手册:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_resmgr.htm#CFAGGDBD

依次学习上述各个子程序的语法及概念

---BEGIN_SQL_BLOCK Procedure
 此子程序在11.2.0.1不存在;用于并行语句排队,这个概念和vldb有关,此子程序产生于11.2.0.2
 
--calibrate_io procedure
declare
max_iops pls_integer;
max_mbps pls_integer;
actual_latency pls_integer;
begin
dbms_resource_manager.calibrate_io(1,20,max_iops => max_iops,max_mbps => max_mbps,actual_latency => actual_latency);
dbms_output.put_line(max_iops||'---------'||max_mbps||'------'||actual_latency);
end;
/

SQL>
 
106---------42------18 
(注:最大max iops:每秒106;max_mbps,最大天吐量42;实际延迟:18)也可以理解为最大每秒发生106次数据库块读取;最大每秒发生读取1M大小的次数是42,实际延迟是18毫秒)
子程序运行很慢,
 

num_physical_disks
 

Approximate number of physical disks in the database storage
 

 

max_latency
 

Maximum tolerable latency in milliseconds for database-block-sized IO requests
 

 

max_iops --每秒最大的io请求次数.这个IO请求次数是随机分布的,基于数据库块大小的读取,即每秒基于数据库块大小进行IO请求的次数
 

Maximum number of I/O requests per second that can be sustained. The I/O requests are randomly-distributed, database-block-sized reads.
 

 

max_mbps --每秒以单位MB表示,最大的IO吞吐量,同上,即每秒发生1MB读取请求的最大IO次数
 

Maximum throughput of I/O that can be sustained, expressed in megabytes per second. The I/O requests are randomly-distributed, 1 megabyte reads.
 

 

actual_latency --系统实际的延迟,以毫秒表示,在上述max_iops情形(在每秒最大iops),平均latency
 

Average latency of database-block-sized I/O requests at max_iops rate, expressed in milliseconds

使用说明:
  1,只有sysdba可以运行此子程序
  2,须开启timed_statistics,为数据文件启用asynch_io
  3,或配置filesystemio_options为aysnch或setall
  4,aysnc_io的状态可自如下sql获知:
    select name,asynch_io
    from v$datafile f,v$iostat_file i
    where f.file#=i.file_no
          and filetype_name='Data File';
  5,同时只能运行一个子程序
  6,对于rac环境,多实例会同时产生此工作负荷

-----查询上述子程序运行的结果
SQL> select status,calibration_time from v$io_calibration_status;
 
STATUS        CALIBRATION_TIME
------------- --------------------------------------------------------------------------------
READY         05-JAN-13 08.38.15.540 PM     

---上述视图status各值注解
STATUS:
  IN PROGRESS   : Calibration in Progress (Results from previous calibration
                  run displayed, if available)
  READY         : Results ready and available from earlier run
  NOT AVAILABLE : Calibration results not available.

---存储上述子程序的字典     
SQL> select * from dba_rsrc_io_calibrate;
 
START_TIME                                                                       END_TIME                                                                           MAX_IOPS   MAX_MBPS  MAX_PMBPS    LATENCY NUM_PHYSICAL_DISKS
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ------------------
05-JAN-13 08.35.14.964000 PM                                                     05-JAN-13 08.38.15.540000 PM                                                            106         42         14         18                  1
      
 
 
 
--清除resource manager中pending change
SQL> exec dbms_resource_manager.clear_pending_area;
 
PL/SQL procedure successfully completed