测试数据库系统性能步骤如下:
oracle@mmpt-SRJ:~> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue May 8 08:22:51 2018
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL>
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2217464 bytes
Variable Size 499124744 bytes
Database Buffers 281018368 bytes
Redo Buffers 2637824 bytes
Database mounted.
Database opened.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL>
SQL>
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 lat INTEGER;
3 iops INTEGER;
4 mbps INTEGER;
5 BEGIN
6 -- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (<DISKS>, <MAX_LATENCY>, iops, mbps, lat);
7 DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat);
8
9 DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
10 DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
11 dbms_output.put_line('max_mbps = ' || mbps);
12 end;
13 /
DECLARE
*
ERROR at line 1:
ORA-56708: Could not find any datafiles with asynchronous i/o capability
ORA-06512: at "SYS.DBMS_RMIN", line 456
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 1285
ORA-06512: at line 7
SQL> show parameter filesystem
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options string none
SQL> alter system set filesystemio_options=setall scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2217464 bytes
Variable Size 499124744 bytes
Database Buffers 281018368 bytes
Redo Buffers 2637824 bytes
Database mounted.
Database opened.
SQL>
SQL>
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';
NAME ASYNCH_IO
-------------------------------------------------- ---------
/fantlam/oracle/app/oracle/oradata/mmpt/system01.d ASYNC_ON
bf
/fantlam/oracle/app/oracle/oradata/mmpt/sysaux01.d ASYNC_ON
bf
/fantlam/oracle/app/oracle/oradata/mmpt/undotbs01. ASYNC_ON
dbf
/fantlam/oracle/app/oracle/oradata/mmpt/users01.db ASYNC_ON
f
NAME ASYNCH_IO
-------------------------------------------------- ---------
SQL> col name for a60
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';
NAME ASYNCH_IO
------------------------------------------------------------ ---------
/fantlam/oracle/app/oracle/oradata/mmpt/system01.dbf ASYNC_ON
/fantlam/oracle/app/oracle/oradata/mmpt/sysaux01.dbf ASYNC_ON
/fantlam/oracle/app/oracle/oradata/mmpt/undotbs01.dbf ASYNC_ON
/fantlam/oracle/app/oracle/oradata/mmpt/users01.dbf ASYNC_ON
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 lat INTEGER;
3 iops INTEGER;
4 mbps INTEGER;
5 BEGIN
6 -- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (<DISKS>, <MAX_LATENCY>, iops, mbps, lat);
7 DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat);
8
9 DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
10 DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
11 dbms_output.put_line('max_mbps = ' || mbps);
12 end;
13 /
##看结果
max_iops = 6861
latency = 0
max_mbps = 502
PL/SQL procedure successfully completed.
SQL>
转载于:https://blog.51cto.com/roidba/2113820