oracle 设置使用磁盘IO模式(DBMS_RESOURCE_MANAGER.CALIBRATE_IO的使用)

最新推荐文章于 2021-10-09 15:33:59 发布

daimin1983

最新推荐文章于 2021-10-09 15:33:59 发布

阅读量3k

点赞数 1

分类专栏： Oracle

Oracle 专栏收录该内容

61 篇文章 1 订阅

订阅专栏

oracle 设置使用磁盘IO模式

You can use the FILESYSTEMIO_OPTIONSinitialization parameter to enable or disable asynchronous I/O or direct I/O on file system files. This parameter is platform-specific and has a default value that is best for a particular platform. It can be dynamically changed to update the default setting.

FILESYTEMIO_OPTIONScan be set to one of the following values:

ASYNCH:enable asynchronous I/O on file system files, which has no timing requirement for transmission.
DIRECTIO:enable direct I/O on file system files, which bypasses the buffer cache.
SETALL:enable both asynchronous and direct I/O on file system files.
NONE:disable both asynchronous and direct I/O on file system files.

alter system set filesystemio_options='DIRECTIO' scope = spfile;

oracle 11G可以使用DBMS_RESOURCE_MANAGER.CALIBRATE_IO

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;

http://docs.oracle.com/cd/E18283_01/server.112/e16638/iodesign.htm#insertedID3

10G没有CALIBRATE_IO

就做一个插入1000000次的操作

--DIRECTIO
DECLARE
  T1 DATE;
  T2 DATE;
BEGIN
  SELECT SYSDATE
  INTO   T1
  FROM   DUAL;
  FOR C IN 1 .. 1000000 LOOP
    INSERT INTO T
    VALUES
      ('你好');
  END LOOP;
  SELECT SYSDATE
  INTO   T2
  FROM   DUAL;
  DBMS_OUTPUT.PUT_LINE((T2 - T1) * 24 * 60 * 60);
  COMMIT;
END;