Resource Manager Enhancements in Oracle Database 11g (文件 ID 884082.1)

lovestanford發表於2014-12-07
OracleDatabase
關於IO校準的操作在自動並行查詢/DML等情況下很有必要對資料庫進行實際檢查和校驗。

APPLIES TO:

Enterprise Manager for Oracle Database - Version 11.1.0.6 and later
Oracle Database - Enterprise Edition - Version 11.1.0.6 and later
Information in this document applies to any platform.
***Checked for relevance on 26-Apr-2013***

PURPOSE

This article provides an overview of all the resource manager enhancements in Oracle Database 11g.

TOPICS:

1. I/O Calibration 
2. Per Session I/O Limits 
3. Resource Manager Statistics in AWR 
4. Built-In Resource Plans


DETAILS

1) I/O Calibration (DBMS_RESOURCE_MANAGER.CALIBRATE_IO):

++ This feature help assess the I/O capability of the database's storage system, and gauge maximum IOPS and Mbytes/s. When Calibrate I/O is invoked it will generate I/O intensive read-only random I/O (db_block_size) and large-block (1MByte) sequential I/O workloads. Unlike various external I/O calibration tools, this tool uses the Oracle code stack and runs in the database, issuing I/O against blocks stored in the database. 

IMPORTANT NOTES:

  • This should only be run during off-peak times to make sure the calibration doesn't affect production workloads, as well as the production workloads affecting the results of the calibration.
  •  Only users with the SYSDBA privilege can run this procedure. Qualified users must also turn on timed_statistics, and ensure asynch_io is enabled for datafiles. This can be achieved by setting filesystemio_options to either ASYNCH or SETALL.



Example:  

SQL> col name format a50 
SQL> SELECT name, asynch_io FROM v$datafile f,v$iostat_file i 
2 WHERE f.file# = i.file_no 
3 AND filetype_name = 'Data File' 
4 / 

NAME ASYNCH_IO 
-------------------------------------------------- --------- 
C:\APP\PVELUSAM\ORADATA\PRATH\SYSTEM01.DBF ASYNC_ON 
C:\APP\PVELUSAM\ORADATA\PRATH\SYSAUX01.DBF ASYNC_ON 
C:\APP\PVELUSAM\ORADATA\PRATH\UNDOTBS01.DBF ASYNC_ON 
C:\APP\PVELUSAM\ORADATA\PRATH\USERS01.DBF ASYNC_ON



++ If asynchronous I/O is not enabled, the procedure returns the following error. 

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




++ Only one calibration can be run at a time. If another calibration is initiated at the same time, it will fail. 


SETUP: 

a) Asynchronous I/O shoulbd be enabled by setting the FILESYSTEMIO_OPTIONS parameter to ASYNC or SETALL. 

ALTER SYSTEM SET FILESYSTEMIO_OPTIONS=SETALL SCOPE=SPFILE; 
SHUTDOWN IMMEDIATE; 
STARTUP;



b) Then call the CALIBRATE_IO function using the DBMS_RESOURCE_MANAGER procedure. 

++ The CALIBRATE_IO procedure accepts two parameters to specify the number of physical disks (default 1) and the maximum tolerable latency (default 20ms). 

Example: 
--------

SQL> SET SERVEROUTPUT ON 
SQL> DECLARE 
2 l_max_iops PLS_INTEGER; 
3 l_max_mbps PLS_INTEGER; 
4 l_actual_latency PLS_INTEGER; 
5 BEGIN 
6 DBMS_RESOURCE_MANAGER.calibrate_io ( 
7 num_physical_disks => 1, 
8 max_latency => 20, 
9 max_iops => l_max_iops, 
10 max_mbps => l_max_mbps, 
11 actual_latency => l_actual_latency); 
12 
13 DBMS_OUTPUT.put_line ('l_max_iops = ' || l_max_iops); 
14 DBMS_OUTPUT.put_line ('l_max_mbps = ' || l_max_mbps); 
15 DBMS_OUTPUT.put_line ('l_actual_latency = ' || l_actual_latency); 
16 END; 
17 / 
l_max_iops = 148 
l_max_mbps = 45 
l_actual_latency = 19


++ The V$IO_CALIBRATION_STATUS views show the current status of the calibration runs. During a run the status of 'IN PROGRESS' is displayed. Once a run is complete the status switches to 'READY' and the calibration time is displayed. 


Description of DBA_RSRC_IO_CALIBRATE (DBA table that stores I/O Calibration results): 

START_TIME Start time of the most recent I/O calibration 
END_TIME End time of the most recent I/O calibration 
MAX_IOPS Maximum number of data block read requests that can be sustained per second 
MAX_MBPS Maximum megabytes per second of maximum-sized read requests that can be sustained 
MAX_PMBPS Maximum megabytes per second of large I/O requests that can be sustained by a single process 
LATENCY Latency for data block read requests 
NUM_PHYSICAL_DISKS Number of physical disks in the storage subsystem (as specified by the user) 


++ Output can be seen as below, 

SQL> SET LINESIZE 150 
SQL> COLUMN start_time FORMAT A30 
SQL> COLUMN end_time FORMAT A30 
SQL> SELECT * FROM dba_rsrc_io_calibrate; 

START_TIME END_TIME MAX_IOPS MAX_M 
BPS MAX_PMBPS LATENCY NUM_PHYSICAL_DISKS 
------------------------------ ------------------------------ ---------- ----- 
--- ---------- ---------- ------------------ 
08-SEP-09 11.32.13.703000 AM 08-SEP-09 11.34.47.062000 AM 148 
45 29 19 1

 

 

2) Per Session I/O Limits:

++ In previous versions of Oracle, the DBA could specify the maximum amount of time a session could run before some action was taken; for example, the call was aborted, the session was killed, or the session was migrated to a new consumer group. This feature is configured on a per consumer group basis in a resource plan. 

++ In Oracle Database 11g, DBAs can also specify the maximum number of I/O requests or the maximum megabytes of I/O that a session can issue before the same set of actions are taken. This feature is used for two purposes. The first is to automatically identify runaway queries. The second is to move sessions executing long-running calls to lower-priority consumer groups. 

++ The SWITCH_IO_MEGABYTES and SWITCH_IO_REQS parameters are now available in addition to the SWITCH_TIME parameter. All three parameters default to NULL, meaning unlimited. 

EXAMPLE: 
-------- 
The following example switches the resource consumer group from oltp_group to batch_group for the current call within the session if the elapsed time exceeds 120 seconds, the number of I/O requests exceeds 5000 or the amount of I/O requested exceeds 1024M.  

BEGIN 
DBMS_RESOURCE_MANAGER.create_plan_directive ( 
plan => 'my_plan', 
group_or_subplan => 'oltp_group', 
comment => 'OLTP Group', 
mgmt_p1 => 70, 
switch_group => 'batch_group', 
switch_time => 120, 
switch_io_reqs => 5000, 
switch_io_megabytes => 1024, 
switch_for_call => TRUE); 
END; 
/

 

 

 

3) Resource Manager Statistics in AWR:

++ AWR contains new historical views for Resource Manager statistics. These new views, DBA_HIST_RSRC_PLAN and DBA_HIST_RSRC_CONSUMER_GROUP, contain the historical version of the statistics in the views V$RESOURCE_PLAN and 
V$RESOURCE_CONSUMER_GROUP. AWR also contains per-minute metrics for resource consumption and Resource Manager-induced waits in the view V$RSRCMGRMETRIC. 

This feature makes it easier to view historical Resource Manager statistics.

 

 

 

 

 

4) Built-In Resource Plans

++ Oracle 11g includes a built-in MIXED_WORKLOAD_PLAN, designed to give OLTP operations priority over batch operations. The plan contains two consumer groups (INTERACTIVE_GROUP and BATCH_GROUP). Calls assigned to the INTERACTIVE_GROUP are automatically switched to the BATCH_GROUP if they exceed 60 seconds. The complete definition of this plan is referenced in the Oracle documentation:

Oracle? Database Administrator's Guide
11g Release 1 (11.1)
Part Number B28310-04


The RESOURCE_MANAGER_PLAN parameter is used to tell the instance which resource plan to use.

SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = MIXED_WORKLOAD_PLAN; 

System altered.

 

SQL> show parameter resource 

NAME TYPE VALUE 
------------------------------------ ----------- ----------------------- 
resource_limit boolean FALSE 
resource_manager_cpu_allocation integer 2 
resource_manager_plan string MIXED_WORKLOAD_PLAN



++ You must then grant access on the relevant consumer groups to individual users. The following example grants access on both consumer groups to the TEST user and makes the INTERACTIVE_GROUP the default consumer group. Querying the DBA_USERS view shows the default setting has been recorded. 
 

SQL> BEGIN 
2 -- Assign users to consumer groups 
3 DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group( 
4 grantee_name => 'test', 
5 consumer_group => 'interactive_group', 
6 grant_option => FALSE); 
7 DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group( 
8 grantee_name => 'test', 
9 consumer_group => 'batch_group', 
10 grant_option => FALSE); 
11 DBMS_RESOURCE_MANAGER.set_initial_consumer_group('test', 'interactive_group'); 
12 END; 
13 / 

PL/SQL procedure successfully completed.

 

SQL> SELECT initial_rsrc_consumer_group FROM dba_users WHERE username = 'TEST'; 

INITIAL_RSRC_CONSUMER_GROUP 
------------------------------ 
INTERACTIVE_GROUP




++ Connecting to the database as the TEST user and querying the V$SESSION view shows the correct consumer group assignment is working. 

SQL> conn test/test
Connected. 
SQL> SELECT resource_consumer_group 
2 FROM v$session 
3 WHERE audsid = SYS_CONTEXT('USERENV', 'SESSIONID'); 

RESOURCE_CONSUMER_GROUP 
-------------------------------- 
INTERACTIVE_GROUP

  
++ We can see the consumer group switch take place if we create an artificially long call.

SQL> conn test/test 
SQL> SET SERVEROUTPUT ON 
SQL> DECLARE 
2 l_cg v$session.resource_consumer_group%TYPE; 
3 BEGIN 
4 SELECT resource_consumer_group 
5 INTO l_cg 
6 FROM v$session 
7 WHERE audsid = SYS_CONTEXT('USERENV', 'SESSIONID'); 
8 DBMS_OUTPUT.put_line('Current Consumer Group: ' || l_cg); 
9 DBMS_LOCK.sleep(65); 
10 SELECT resource_consumer_group 
11 INTO l_cg 
12 FROM v$session 
13 WHERE audsid = SYS_CONTEXT('USERENV', 'SESSIONID'); 
14 DBMS_OUTPUT.put_line('Current Consumer Group: ' || l_cg); 
15 END; 
16 / 
Current Consumer Group: INTERACTIVE_GROUP 
Current Consumer Group: BATCH_GROUP


++ As we can see, the call was initially assigned to the INTERACTIVE_GROUP, but switched to the BATCH_GROUP as the call took longer than 60 seconds.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/21754115/viewspace-1359890/，如需轉載，請註明出處，否則將追究法律責任。

相關文章