zt_Limiting I/O and CPU resources using 11g Oracle Resource Manager

 

另附自己前期關於dbms_resource_manager的小記

 

測試場景:
  Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
  Connected as
 
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