| |
Briefly explain one of the factors affect how redo data, that is, i / o throughput, Oracle media recovery depends on i / o, if i / o bottlenecks, it will certainly affect the standby database media recovery.
Then i / o stack contains hbas, storage-related switches, the physical disk storage. Then the oracle is recommended when the application is deployed, it is recommended to verify the i / o avoid problems. But there is a problem before it sting, and that is how to verify i / o throughput side, in other words how to verify i / o throughput is more in line with real production environment.
In Oracle Database 11g, the Real Application Testing feature (Capture / Replay) was introduced to inject real (captured) workload into the system. However, another new 11g feature is available to help assess the I / O capability of the database's storage system, and gauge maximum IOPS and Mbytes / s.
capability i / o feature is based on a database of internal functions (dbms_resource_manager.calibrateio ()), this function is integrated inside the oracle database, to better meet the test i / o problems, and in the final report information related to the output.
Then execute the package need to pay attention to what?
1, permissions, you must have the sysdba privileges to perform this procedure, you need to open additional timed_statistics.
2. Determine the asynchronous i / o all data files and temporary files in the database have been used to start, we can v $ datafile and v $ views associated iostat_file be confirmed.
eg:
col name format a50
select name, asynch_io from v $ datafile f, v $ iostat_file i
where f.file # = i.file_no
and (filetype_name = 'Data File' or filetype_name = 'Temp File');
If asynchronous i / o does not start, set disk_asynch_io = true start of this feature, it is enabled by default, if the linux biggest slots used up, it will automatically turn off Karma function, which is why although a disk_asynch_io = true up but still did not achieve the desired effect. The biggest slots can check / proc / sys / fs / aio-max-nr currently in use can be viewed / proc / sys / fs / aio-nr to confirm respectively.
3. Make sure the server only needs to open the test database, to avoid affecting other applications.
4, for the RAC, the need to ensure that all instances are open, because you will do a comprehensive proofreading for all nodes, perform the procedure to just one instance.
5, to ensure that only a user to perform a proofreading i / o operations. You can view the current status identified by v $ io_calibration_status.
Also we learned that there are so few data View procedure:
The calibration will run in different phases. In the first phase, small block random I / O
workload is performed on each node and then concurrently on all nodes. The second
phase will generate large block sequential I / O on each node. Note, that the Calibrate
I / O is expecting that a datafile is spread across all disks specified in NUM_DISKS
variable. Furthermore, offline files are not considered for file I / O.
Once completed, the results will be present in dba_rsrc_io_calibrate table.
All right.
Learned these, you can start to experiment operation. Let practice was truth.
The syntax is as follows:
SET SERVEROUTPUT ON
DECLARE
lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (< DISKS>, < MAX_LATENCY>, iops, mbps, lat);
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat);
DBMS_OUTPUT.PUT_LINE ( 'max_iops =' || iops);
DBMS_OUTPUT.PUT_LINE ( 'latency =' || lat);
DBMS_OUTPUT.PUT_LINE ( 'max_mbps =' || mbps);
end;
/
Parameters as follows:
Parameter Description
num_physical_disks
Approximate number of physical disks in the database storage (physical disk number input)
max_latency
Maximum tolerable latency in milliseconds for database-block-sized IO requests (the maximum available number of milliseconds delay tolerant input)
max_iops
Maximum number of I / O requests per second that can be sustained. The I / O requests are randomly-distributed, database-block-sized reads. (Continued in requests per second maximum i / o number of output)
max_mbps
Maximum throughput of I / O that can be sustained, expressed in megabytes per second. The I / O requests are randomly-distributed, 1 megabyte reads. (Maximum sustained throughput M units)
actual_latency
Average latency of database-block-sized I / O requests at max_iops rate, expressed in milliseconds (average delay) |
|
|
|