Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Thursday, January 3, 2013

EXADATA I/O CALIBRATE ON Database server

We can usually use calibrate_io procedure but for EXADATA it is recommended we manually set the values on the database server.

Traditional Method:
DBMS_RESOURCE_MANAGER.CALIBRATE_IO
Ex:-
unless until you give latency "10" or higher for the second parameter (SLA) it will fail.

SQL> SET SERVEROUTPUT ON
 DECLARE
 lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (84,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;
  /
max_iops = 34349
latency = 11
max_mbps = 10871

Error if you give SLA <=10:
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (84,1, iops, mbps, lat);
DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
dbms_output.put_line('max_mbps = ' || mbps);
end;

DECLARE
*
ERROR at line 1:
ORA-29355: NULL or invalid MAX_LATENCY argument specified
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 1296
ORA-06512: at line 6


Exadata recommended:

SQL> select * from resource_io_calibrate$;

no rows selected

SQL> insert into resource_io_calibrate$ values(current_timestamp,current_timestamp, 0, 0, 200, 0, 0);

1 row created.

SQL> select * from resource_io_calibrate$;

START_TIME
---------------------------------------------------------------------------
END_TIME
---------------------------------------------------------------------------
  MAX_IOPS   MAX_MBPS  MAX_PMBPS    LATENCY  NUM_DISKS
---------- ---------- ---------- ---------- ----------
03-JAN-13 10.13.20.640962 PM
03-JAN-13 10.13.20.640962 PM
         0          0        200          0          0


Reference:- (Doc ID 1297112.1) Best Practices for Data Warehousing on the Oracle Database Machine X2-2

No comments: