Oracle Database の DBMS_RESOURCE_MANAGER.CALIBRATE_IO で I/O測定してみてみた

Oracle Databaseの I/O測定は、[DBMS_RESOURCE_MANAGER.CALIBRATE_IOプロシージャ](https://docs.oracle.com/cd/E82638_01/tgdba/IO-configuration-and-design.html#GUID-BCAF1968-C076-41FC-9900-3A8681A21419)を使用します。
このプロシージャは、I/Oを連続的に発行する外部のI/O測定ツールとは異なり、Oracleデータファイルを使用してI/Oをランダムに発行し、ストレージ・サブシステムが耐えられる最大IOPS(1秒当たりのI/Oリクエスト)と MBPS(1秒当たりのI/OのMB)を判定するため、よりデータベースの実際のパフォーマンスに近い結果を得ることができます。

[I/Oについて](https://docs.oracle.com/cd/F19136_01/tgdba/IO-configuration-and-design.html#GUID-B89C41F4-B12A-49E1-BFB9-5BF978E2D6B3)、CPUタイムの大部分をI/Oアクティビティが完了するまでの待機に使用するアプリケーションはI/Oバウンドと呼ばれます。 許容時間内にI/Oリクエストに対応できない場合は、パーティショニング、圧縮、In-Memory、Smart Scan等の [I/Oチューニング](https://www.oracle.com/jp/a/tech/docs/technical-resources/0309-1500-diskio-handout.pdf)を行うと、アプリケーションのパフォーマンスを向上できます。ただし、アプリケーションがI/Oバウンドではない場合(たとえば、CPUが制限要因である場合)、I/Oをチューニングしてもパフォーマンスを改善できません。

ということで、データベースとしての I/O値を知るために DBMS_RESOURCE_MANAGER.CALIBRATE_IOプロシージャを使用して、I/O測定をしてみてみます。

 # ■ I/O測定の前提条件
 前提条件は次になります。

	・ SYSDBA権限でログイン
	・ timed_statistics=TRUE
	・ 非同期I/O有効化


## ● SYSDBA権限でログイン
```
[oracle@basedb ~]$ sqlplus / as sysdba

	SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jun 4 15:57:03 2023
	Version 19.19.0.0.0

	Copyright (c) 1982, 2022, Oracle.  All rights reserved.


	Connected to:
	Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production
	Version 19.19.0.0.0
```

## ● timed_statistics = TRUE 設定
timed_statisticsが TRUEであることを確認
```
	SQL> show parameter timed_statistics

		NAME				     TYPE	 VALUE
		------------------------------------ ----------- ------------------------------
		timed_statistics		     boolean	 TRUE
```


## ● 非同期I/O有効化確認
**1) filesystemio_options パラメータ確認**
ilesystemio_options が ASYNCH または SETALL のいずれかであることを確認
```
SQL> show parameter filesystemio_options

	NAME				     TYPE	 VALUE
	------------------------------------ ----------- ------------------------------
	filesystemio_options		     string	 SETALL
```

**2) Datafaile ASYNC_ON確認**
次のSQL文を使用して、asynch_ioステータスであることを確認
```
SQL> SELECT NAME,ASYNCH_IO FROM V$DATAFILE F,V$IOSTAT_FILE I
     WHERE  F.FILE#=I.FILE_NO AND FILETYPE_NAME='Data File'
     ORDER BY 1;

	NAME												     ASYNCH_IO
	---------------------------------------------------------------------------------------------------- ---------
	+DATA/CDB_TOKYO/DATAFILE/sysaux.268.1138430843						     ASYNC_ON
	+DATA/CDB_TOKYO/DATAFILE/system.261.1138430857						     ASYNC_ON
	+DATA/CDB_TOKYO/DATAFILE/undotbs1.260.1138430879					     ASYNC_ON
	+DATA/CDB_TOKYO/DATAFILE/users.270.1138431375						     ASYNC_ON
	+DATA/CDB_TOKYO/F9D6EA8CCAA09630E0530905F40A5107/DATAFILE/sysaux.265.1138430601 	     ASYNC_ON
	+DATA/CDB_TOKYO/F9D6EA8CCAA09630E0530905F40A5107/DATAFILE/system.264.1138430601 	     ASYNC_ON
	+DATA/CDB_TOKYO/F9D6EA8CCAA09630E0530905F40A5107/DATAFILE/undotbs1.266.1138430601	     ASYNC_ON
	+DATA/CDB_TOKYO/FD210074E2916B27E053B5010A0A0DE2/DATAFILE/sysaux.271.1138431203 	     ASYNC_ON
	+DATA/CDB_TOKYO/FD210074E2916B27E053B5010A0A0DE2/DATAFILE/system.273.1138431193 	     ASYNC_ON
	+DATA/CDB_TOKYO/FD210074E2916B27E053B5010A0A0DE2/DATAFILE/undotbs1.275.1138431385	     ASYNC_ON
	+DATA/CDB_TOKYO/FD210074E2916B27E053B5010A0A0DE2/DATAFILE/users.274.1138431375		     ASYNC_ON

	11 rows selected.
```


# ■ BaseDB の最大IOPS
Oracle Base Database Service(BaseDB)の最大IOPSスペックは、作成時に確認できます。
今回、使用可能なデータ・ストレージ(GB)を 256GB で作成すると、データ・ストレージの理論上の最大IOPSは 19.2K となります。
![作成時のIOPS確認.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/109087/cb3c6587-11e9-ecf4-0776-233bd25782ab.jpeg)



# ■ I/O測定実行

入力パラメータnum_physical_disksはオプションです。
num_physical_disksパラメータにデータベースのストレージ・システム内にある物理ディスクの概数を設定すると、より高速かつ正確に測定できます。

今回ASMなので、ASMDISK数を確認します。
I/O設計計画を作成する場合は、Oracle Automatic Storage Management(Oracle ASM)の使用を検討します。Oracle ASMは、記憶域の管理を管理者に任せるのではなく、データベースで管理するという方針に基づいた、高いパフォーマンスを実現する統合データベース・ファイル・システムおよびディスク・マネージャです。
データベース・ファイル記憶域には、RAWデバイスやオペレーティング・システムのファイル・システムではなく、Oracle ASMを使用することをお薦めします。Oracle ASMの主な利点を次に示します。

    ・ ストライプ化
    ・ ミラー化
    ・ オンラインでの記憶域の再構成と動的リバランス
    ・ 管理対象ファイルの作成と削除

## 1) ASM DISK数確認
次のSQLから。データ・ストレージの DATA DISKは、4つで構成されていることが確認できます。

```
SQL> set lin 100
SQL> col ASMDISK format a20
SQL> SELECT SUBSTR(d.name,1,16) AS asmdisk, d.mount_status, d.state, dg.name AS diskgroup 
     FROM V$ASM_DISKGROUP dg, V$ASM_DISK d
     WHERE dg.group_number = d.group_number order by 1;

	ASMDISK 	     MOUNT_S STATE    DISKGROUP
	-------------------- ------- -------- ------------------------------
	DATA_0000	     CACHED  NORMAL   DATA
	DATA_0001	     CACHED  NORMAL   DATA
	DATA_0002	     CACHED  NORMAL   DATA
	DATA_0003	     CACHED  NORMAL   DATA
	RECODISK1	     CACHED  NORMAL   RECO
	RECODISK2	     CACHED  NORMAL   RECO
	RECODISK3	     CACHED  NORMAL   RECO
	RECODISK4	     CACHED  NORMAL   RECO

	8 rows selected.
```


### 2) 測定実行

```
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
     lat  NUMBER;
     iops INTEGER;
     mbps INTEGER;
   BEGIN
   -- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (<DISKS>, <MAX_LATENCY>, iops, mbps, lat);
      DBMS_RESOURCE_MANAGER.CALIBRATE_IO (4, 10, iops, mbps, lat);
   end;
   /

max_iops = 19398
latency  = 6.558
max_mbps = 152
```

I/O測定は、次の2つのステップで行われます。
最初のステップでは、DBMS_RESOURCE_MANAGER.CALIBRATE_IOプロシージャにより、すべてのデータベース・インスタンスのすべてのデータファイルに対してデータベース・ブロック・サイズのランダムな読取り(デフォルトは8KB)を発行します。このステップでは、出力パラメータmax_iopsに、データベースが維持できる最大IOPSが示されます。max_iopsの値は、OLTPデータベースに重要なメトリックです。出力パラメータactual_latencyには、このワークロードの平均待機時間が示されます。特定の目標待機時間が必要な場合は、入力パラメータmax_latencyを使用して目標待機時間を指定できます(データベース・ブロック・サイズのIOリクエストの最大許容待機時間をミリ秒単位で指定)。

2番目の測定のステップでは、DBMS_RESOURCE_MANAGER.CALIBRATE_IOプロシージャにより、すべてのデータベース・インスタンスのすべてのデータファイルに対して1MBのランダムな読取りを発行します。2番目のステップでは、出力パラメータmax_mbpsに、データベースが維持できるI/Oの最大MBPSが示されます。このステップは、データ・ウェアハウスに重要なメトリックを提供します。


### 3) 測定結果参照
I/O測定プロセス中は、いつでも[V$IO_CALIBRATION_STATUSビュー](https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-IO_CALIBRATION_STATUS.html#GUID-E7D2C4C2-7EE6-4A7F-A4EE-01686C6CF576)で測定ステータスを問い合せることができます。I/O測定が正常に完了したら、[DBA_RSRC_IO_CALIBRATE表](https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_RSRC_IO_CALIBRATE.html)でその結果を参照できます。 

```
SQL> set lin 200
SQL> col ADDITIONAL_INFO format a30
SQL> col START_TIME format a30
SQL> col END_TIME format a30
SQL> select * from DBA_RSRC_IO_CALIBRATE;

	START_TIME		       END_TIME 			MAX_IOPS   MAX_MBPS  MAX_PMBPS	  LATENCY NUM_PHYSICAL_DISKS ADDITIONAL_INFO
	------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ------------------ ------------------------------
	04-JUN-23 03.59.24.797641 PM   04-JUN-23 04.09.00.228331 PM	   19398	152	   150	    6.558		   4
```

### 4) 最大IOPSスペック値確認
Oracle Base Database Service(BaseDB)の最大IOPSスペックは、OCIコンソールから確認できます。
表示された最大IOPS値が 19.2K なので、測定値と同じことが確認できました。
![DBCS-1ocpu.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/109087/05644d3c-8eb1-5ff9-503d-138669b4cd9d.jpeg)
測定結果が異なる場合 BaseDBは、OCPUの数に応じてIOPSは比例してスケーリングされます。OCPU数とIOPSの関係は次を参考にします。
・参考: [Create a DB System Using the Console: Theoretical max IOPS](https://docs.oracle.com/en/cloud/paas/bm-and-vm-dbs-cloud/dbshqbqu/index.html#articletitle)

# ■ I/Oチューニング
Oracle DatabaseのI/Oチューニングは次を参考にします。
・[実践！！ パフォーマンス・チューニング ～DISKI/Oボトルネック解決編～](https://www.oracle.com/jp/a/tech/docs/technical-resources/0309-1500-diskio-handout.pdf)

https://www.oracle.com/jp/a/tech/docs/technical-resources/0309-1500-diskio-handout.pdf

# ■ 参考
　・[パフォーマンス・チューニングの概要](https://docs.oracle.com/cd/E82638_01/tgdba/performance-tuning-overview.html#GUID-5C5C081B-A2C1-442D-9F11-A760234C8815)
　・[データベース・パフォーマンス・チューニング・ガイド: I/O構成および設計](https://docs.oracle.com/cd/F19136_01/tgdba/IO-configuration-and-design.html#GUID-B89C41F4-B12A-49E1-BFB9-5BF978E2D6B3)
　・[データベース内部のI/O測定](https://docs.oracle.com/cd/F19136_01/tgdba/IO-configuration-and-design.html#GUID-08ED4172-9398-402E-B0C4-4CE88DE87603)
　・[Oracle Orion測定ツールによるI/O測定](https://docs.oracle.com/cd/F19136_01/tgdba/IO-configuration-and-design.html#GUID-355C99D8-29C1-421F-8B65-47A3C48324A2)
　・[管理者ガイド: ビューを使用したOracle ASM情報の表示](https://docs.oracle.com/cd/F19136_01/ostmg/views-asm-info.html#GUID-82C92841-3812-4442-86E7-BA1496605685)
　・[CALIBRATE_IOプロシージャ](https://docs.oracle.com/cd/F19136_01/arpls/DBMS_RESOURCE_MANAGER.html#GUID-90D0674E-6A10-41A9-ABBD-783F908502C3)
　・[TIMED_STATISTICS](https://docs.oracle.com/cd/F19136_01/refrn/TIMED_STATISTICS.html)
　・[Create a DB System Using the Console: Theoretical max IOPS](https://docs.oracle.com/en/cloud/paas/bm-and-vm-dbs-cloud/dbshqbqu/index.html#articletitle)
　・[実践！！ パフォーマンス・チューニング ～DISKI/Oボトルネック解決編～](https://www.oracle.com/jp/a/tech/docs/technical-resources/0309-1500-diskio-handout.pdf)