【IT168 技术文章】
本文概述了Oracle 11g R1中资源管理器的所有增强内容，包括：
(1) I/O校准
(2) 每会话I/O限制
(3) AWR中的资源管理器统计
(4) 内置资源计划
I/O校准
DBMS_RESOURCE_MANAGER包包括一个CALIBRATE_IO存储过程，它通过执行一个I/O密集的只读工作量来评估数据库服务器的存储系统的I/O性能，这个评估操作应该在非业务高峰期间执行，以确保校准不影响生产的工作量，以及生产的工作量对校准结果的影响。
只有开启了异步I/O时才能使用该存储过程，否则会报下面的错误：
DECLARE
*
ERROR at line 1:
ORA-56708: Could not find any datafiles with asynchronous i/o capability
ORA-06512: at "SYS.DBMS_RMIN", line 453
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 1153
ORA-06512: at line 6
SQL>
*
ERROR at line 1:
ORA-56708: Could not find any datafiles with asynchronous i/o capability
ORA-06512: at "SYS.DBMS_RMIN", line 453
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 1153
ORA-06512: at line 6
SQL>
通过设置参数FILESYSTEMIO_OPTIONS为ASYNC或SETALL来启用异步I/O。
SQL>SHOW PARAMETER FILESYSTEMIO_OPTIONS
NAME TYPE VALUE
----------------------------------------------- -----------------------
filesystemio_options string none
SQL>
SQL>ALTER SYSTEM SET FILESYSTEMIO_OPTIONS=SETALL SCOPE=SPFILE;
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP;
NAME TYPE VALUE
----------------------------------------------- -----------------------
filesystemio_options string none
SQL>
SQL>ALTER SYSTEM SET FILESYSTEMIO_OPTIONS=SETALL SCOPE=SPFILE;
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP;
该CALIBRATE_IO存储过程接受两个参数来指定物理磁盘(默认1)和可承受的最大延迟(默认20ms)的数目，它返回每秒最大数量I/O请求，每秒最大Mb数和真实的延迟。
SET SERVEROUTPUT ON
DECLARE
l_max_iops PLS_INTEGER;
l_max_mbps PLS_INTEGER;
l_actual_latency PLS_INTEGER;
BEGIN
DBMS_RESOURCE_MANAGER.calibrate_io (
num_physical_disks => 1,
max_latency => 20,
max_iops => l_max_iops,
max_mbps => l_max_mbps,
actual_latency => l_actual_latency);
DBMS_OUTPUT.put_line ('l_max_iops = ' || l_max_iops);
DBMS_OUTPUT.put_line ('l_max_mbps = ' || l_max_mbps);
DBMS_OUTPUT.put_line ('l_actual_latency = ' || l_actual_latency);
END;
/
l_max_iops = 556
l_max_mbps = 48
l_actual_latency = 17
PL/SQL procedure successfully completed.
SQL>
DECLARE
l_max_iops PLS_INTEGER;
l_max_mbps PLS_INTEGER;
l_actual_latency PLS_INTEGER;
BEGIN
DBMS_RESOURCE_MANAGER.calibrate_io (
num_physical_disks => 1,
max_latency => 20,
max_iops => l_max_iops,
max_mbps => l_max_mbps,
actual_latency => l_actual_latency);
DBMS_OUTPUT.put_line ('l_max_iops = ' || l_max_iops);
DBMS_OUTPUT.put_line ('l_max_mbps = ' || l_max_mbps);
DBMS_OUTPUT.put_line ('l_actual_latency = ' || l_actual_latency);
END;
/
l_max_iops = 556
l_max_mbps = 48
l_actual_latency = 17
PL/SQL procedure successfully completed.
SQL>
同一时间只能执行一个校准操作，在RAC环境中，工作量发起的所有节点都可以执行。
[G]V$IO_CALIBRATION_STATUS视图显示了当前校准运行的状况。在运行过程中，会显示一个'IN PROGRESS' 的状态'。一旦运行完成状态切换到'READY'，并且会显示校准时间。
SQL> SELECT * FROM v$io_calibration_status;
STATUS CALIBRATION_TIME
------------- -------------------------------
IN PROGRESS
SQL> SELECT * FROM v$io_calibration_status;
STATUS CALIBRATION_TIME
---------------------------------------------------------------------------
READY 28-JUL-2008 14:37:38.410
1 row selected.
SQL>
STATUS CALIBRATION_TIME
------------- -------------------------------
IN PROGRESS
SQL> SELECT * FROM v$io_calibration_status;
STATUS CALIBRATION_TIME
---------------------------------------------------------------------------
READY 28-JUL-2008 14:37:38.410
1 row selected.
SQL>
通过DBA_RSRC_IO_CALIBRATE视图可以显示校准信息。
SET LINESIZE 150
COLUMN start_time FORMAT A30
COLUMN end_time FORMAT A30
SELECT * FROM dba_rsrc_io_calibrate;
START_TIME END_TIME MAX_IOPS MAX_MBPS MAX_PMBPS LATENCY NUM_PHYSICAL_DISKS
----------------- ---------- ---------- ---------- ------------------
28-JUL-2008 14:32:33.68 28-JUL-2008 14:37:38.40 556 48 48 17 1
1 row selected.
SQL>
COLUMN start_time FORMAT A30
COLUMN end_time FORMAT A30
SELECT * FROM dba_rsrc_io_calibrate;
START_TIME END_TIME MAX_IOPS MAX_MBPS MAX_PMBPS LATENCY NUM_PHYSICAL_DISKS
----------------- ---------- ---------- ---------- ------------------
28-JUL-2008 14:32:33.68 28-JUL-2008 14:37:38.40 556 48 48 17 1
1 row selected.
SQL>
