IOCalibrate (IO calibration) in Oracle11g)

With the development of Oracle databases, ldquo and I/O have become a trend. One of the important features of the ExtraData all-in-one machine is to maximize the I/O capability.

With the development of Oracle databases, ldquo and I/O have become a trend. One of the important features of the ExtraData all-in-one machine is to maximize the I/O capability.

Oracle Database is developing to this day, and I/O is king. One of the important features of the ExtraData all-in-one machine is to maximize the I/O capability and increase the I/O throughput.

Compared with CPU and memory, IO Storage has its own particularity. We discuss IO, usually I/O Stack (I/O Stack ). The I/O stack design targets a series of key component layers, including HBA, Storage Switches, Storage Array, and Physical Disks. These objects work together to form the overall IO capability of the system.

Four-layer key components form a "barrel effect ". As long as there is a level of deficiency, it will inevitably become a short board in IO. I/O is difficult to adjust, that is, in this aspect. But for Oracle, we need to focus on the overall IO performance, that is, the overall effect.

Oracle 11g has two performance testing tools: Real Application Test and Calibrate IO ). RAT is a load replay component. When upgrading the system's hardware and software, we are very concerned about whether this change can improve the system performance and performance, will there be new bottlenecks. This cannot be achieved in the past. It can only be discovered after the upgrade through practice. However, RAT can capture the actual system load, repeat it in the new environment, and compare measurements. I/O tuning is also used to simulate I/O loads to determine the actual system I/O status.

This article describes the IO calibration features.

1. IO calibration discovered

First, let's talk about the reason for calibration. IO is a unity of multiple components that affect each other. In most cases, multiple components cannot work exactly as expected. Therefore, calibration is required between the hardware standard indicators and actual conditions to obtain accurate IO data.

What is the purpose of obtaining precise IO? The root cause is the need for Oracle automation and intelligence. After entering the 11g network, Oracle is accelerating its intelligent development. Since CBO, Auto DOP for automated parallel decision-making requires IO calibration information.

2. Configure IO Calibration

In the configuration process, select Oracle 11gR2 for testing.

SQL> select * from v $ version;

BANNER

---------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-Production

PL/SQL Release 11.2.0.3.0-Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0-Production

NLSRTL Version 11.2.0.3.0-Production

The 11g has a view v $ io_calibration_status, which records information about the system calibration process. Unlike the statistics, Oracle does not automatically perform IO calibration, but it must be manually completed by the DBA.

SQL> select * from v $ io_calibration_status;

STATUS CALIBRATION_TIME

---------------------------------------------------------------------------------------------

NOT AVAILABLE

Note: During the calibration process, asynchronous IO is usually required.

SQL> show parameter disk_asy

NAME TYPE VALUE

-----------------------------------------------------------------------------

Disk_asynch_io boolean TRUE

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' or filetype_name = 'temp file ');

NAME ASYNCH_IO

-----------------------------------------------------------

+ DATA/ora11g/datafile/system.256.825944325 ASYNC_ON

+ DATA/ora11g/datafile/system.256.825944325 ASYNC_ON

+ DATA/ora11g/datafile/sysaux.257.825944327 ASYNC_ON

+ DATA/ora11g/datafile/undotbs1.258.825944329 ASYNC_ON

+ DATA/ora11g/datafile/users.259.825944329 ASYNC_ON

+ DATA/ora11g/datafile/example.265.825944513 ASYNC_ON

6 rows selected

IO calibration is integrated into the Resource Manager feature package of Oracle instead of listing functions separately. Call the function package DBMS_RESOURCE_MANAGER.CALIBRATE_IO for IO calibration. Two input parameters are the number of disks, and the other is the maximum IO latency allowed. The two parameters can be achieved by consulting the O & M team and the vendor.

The call process is as follows:

SQL> set serveroutput on;

SQL> DECLARE

2 lat INTEGER;

3 iops INTEGER;

4 mbps INTEGER;

5 BEGIN

6 -- DBMS_RESOURCE_MANAGER.CALIBRATE_IO ( , , Iops, mbps, lat );

7 DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat );

8 DBMS_OUTPUT.PUT_LINE ('max _ iops = '| iops );

9 DBMS_OUTPUT.PUT_LINE ('latency = '| lat );

10 dbms_output.put_line ('max _ mbps = '| mbps );

11 end;

12/

Max_iops = 1, 111

Latency = 8

Max_mbps = 62

PL/SQL procedure successfully completed

Executed in 811.547 seconds

The execution process exceeds 800 s, and the time is not short. Finally, the maximum iops, latency, and maximum mbps (MB per second) are calculated ).

During execution, view v $ io_calibration_status.

SQL> select * from v $ io_calibration_status;

STATUS CALIBRATION_TIME

---------------------------------------------------------------------------------------------

In progress 14-12-13 11.20.2017120 AM

The status changes from Not Available to Ready. During the calibration process, Oracle will form a large number of IO read/write operations on the storage. We use the sar command in Linux to monitor the entire process.

[Root @ SimpleLinux ~] # Sar-B 5 100-o/tmp/res2

Linux 2.6.18-128. el5 (SimpleLinux. localdomain) 12/13/2013

11:25:08 AM tps rtps wtps bread/s bwrtn/s

11:25:13 AM 8.33 0.00 8.33 0.00 134.92

11:25:18 AM 23.02 1.59 21.43 50.79 311.90

11:25:23 AM 5.96 1.59 4.37 50.89 85.88

11:25:28 AM 7.14 1.59 5.56 50.79 89.68

11:25:33 AM 2.78 0.00 2.78 0.00 44.44

11:25:38 AM 5.96 1.59 4.37 50.89 85.88

11:25:43 AM 257.65 253.28 4.37 4141.55 76.34

11:25:48 AM 281.75 276.19 5.56 4415.87 219.05

11:25:53 AM 278.33 273.56 4.77 4427.83 89.07

11:25:58 AM 289.50 266.53 22.97 4264.55 237.62

11:26:03 AM 232.14 228.97 3.17 3688.89 50.79

11:26:08 AM 268.53 264.14 4.38 4608.76 92.43

Pay attention to the TPS change process. After the calibration is started, Oracle generates a large number of IO operations to determine the Storage Limit. This process allows us to understand the upper limit of the current IO architecture.

We use Excel to draw the trend of TPS, RTPS, and WTPS throughout the process.

After completing the IO calibration, we can view the information about the IO training process.

SQL> select * from v $ io_calibration_status;

STATUS CALIBRATION_TIME

---------------------------------------------------------------------------------------------

READY 14-12-13 11.39.10.194 AM

For more details, please continue to read the highlights on the next page: