Autonomous Database:Elastic Poolのプール・リーダーに対するクエリをプール・メンバーにオフロードする

# はじめに
Autonomous Databaseに、1つまたは複数のリフレッシュ可能クローンがElastic Poolのプールリーダーのクエリーを処理できるようにすることで、プール・リーダーの負荷をオフロードして処理パフォーマンスを向上するためのElastic Pool Query Offloadの機能が追加されました。
この機能により、リフレッシュ可能クローンを追加して、クエリー（読み取り）需要の増加に対応することもできます。クエリをオフロードすることで、アプリケーションを水平方向に拡張することができ、クエリ需要を満たすために必要なリフレッシュ可能クローンを追加してシステム全体のパフォーマンスを維持することができます。

![スクリーンショット 2025-03-06 19.19.33.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/10dc4461-1035-4583-9c8a-5b40b26709b1.png)

# 1. Elastic Pool Query Offloadの前提条件

Elastic Pool Query Offloadを利用する前提条件は以下の通りです。
・Elastic Poolの利用
・Elastic Poolのプール・リーダーのリフレッシュ可能クローンとしてプール・メンバーを作成
・アプリケーション・ユーザーはプール・リーダーに接続してクエリを実行

# 2. 検証環境

前提条件に沿って、以下のような環境を準備しました。
・Elastic Poolのプール・リーダーとして、POOLLEADER19Cという名前のAutonomous Databaseを作成
・Elastic Poolのプール・メンバーとして、プール・リーダーPOOLLEADER19Cのリフレッシュ可能クローンPOOLMEMBER19C1を作成
・Elastic Poolのプール・メンバーとして、プール・リーダーPOOLLEADER19Cのリフレッシュ可能クローンPOOLMEMBER19C2を作成

![スクリーンショット 2025-03-06 19.32.06.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/f3add69b-42ef-4eda-a932-29cc7274f665.png)

# 3. 動作検証

adminユーザとして、プール・リーダーのAutonomous DatabaseにSQL*Plusから接続します。
```
[oracle@oracle23ai ~]$ sqlplus admin/Demo#1Demo#1@poolleader

SQL*Plus: Release 23.0.0.0.0 - Production on Fri Jan 17 15:55:39 2025
Version 23.4.0.24.05

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

Last Successful login time: Fri Jan 17 2025 15:07:06 +09:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.26.0.1.0

SQL> 
```
dba_pdbsビューのcloud_identity列からデータベース名を抽出します。
```
SQL> SELECT JSON_VALUE(cloud_identity, '$.DATABASE_NAME') database_name FROM dba_pdbs;

DATABASE_NAME
--------------------------------------------------------------------------------
POOLLEADER19C

SQL>
```
まだQuery Offloadを有効にしていないので、クエリはプール・リーダーで実行され、プール・リーダーのデータベース名が返ってきました。

DBMS_PROXY_SQL.ENABLE_READ_ONLY_OFFLOADプロシージャを実行し、Query Offloadを有効化します。
パラメータを指定せずにDBMS_PROXY_SQL.ENABLE_READ_ONLY_OFFLOADプロシージャを実行すると、全てのクエリ（SELECT文）がプール・メンバーにオフロードされます。
```
SQL> EXEC DBMS_PROXY_SQL.ENABLE_READ_ONLY_OFFLOAD();

PL/SQL procedure successfully completed.

SQL> 
```

再度、dba_pdbsビューのcloud_identity列からデータベース名を抽出してみます。
```
SQL> SELECT JSON_VALUE(cloud_identity, '$.DATABASE_NAME') database_name FROM dba_pdbs;

DATABASE_NAME
--------------------------------------------------------------------------------
POOLMEMBER19C1

SQL>
```

クエリがプール・メンバー（リフレッシュ可能クローン）にオフロードされ、プール・メンバーのデータベース名が返ってきました。

次に、特定のセッションに対してのみ、Query Offloadを有効化します。
パラメータとしてクエリをオフロードするセッションのモジュール名、アクション名を指定してDBMS_PROXY_SQL.ENABLE_READ_ONLY_OFFLOADプロシージャを実行すると、条件に合致したセッションからのクエリ（SELECT文）がプール・メンバーにオフロードされます。
ここでは、モジュール名がmodule1、アクション名がaction1となっているセッションからのクエリのみがプール・メンバーにオフロードされるように設定してみます。
```
SQL> EXEC DBMS_PROXY_SQL.ENABLE_READ_ONLY_OFFLOAD('["module1"]','["action1"]');

PL/SQL procedure successfully completed.

SQL>
```

まず、セッションのモジュール名、アクション名を指定せずにクエリを実行してみます。
```
SQL>  SELECT JSON_VALUE(cloud_identity, '$.DATABASE_NAME') database_name FROM dba_pdbs;

DATABASE_NAME
--------------------------------------------------------------------------------
POOLLEADER19C

SQL> 
```
セッションの モジュール名、アクション名がオフロードの条件と一致しないため、クエリがオフロードされず、Pool Leaderのデータベース名が返されました。

DBMS_APPLICATION_INFO.SET_MODULEプロシージャで、現行セッションのモジュール名をmodule1に、セッションのアクション名をaction1に設定します。
```
SQL> EXEC DBMS_APPLICATION_INFO.SET_MODULE('module1','action1');

PL/SQL procedure successfully completed.

SQL> 
```

再度、同じクエリを実行してみます。
```
SQL> SELECT JSON_VALUE(cloud_identity, '$.DATABASE_NAME') database_name FROM dba_pdbs;

DATABASE_NAME
--------------------------------------------------------------------------------
POOLMEMBER19C1

SQL>
```

セッションの モジュール名、アクション名がオフロードの条件と一致するため、クエリがオフロードされ、Pool Memberのデータベース名が返されました。

ここで、DDLおよびDMLを実行してみます。
DDL、DMLは全てプール・リーダーで実行されます。

```
SQL> CREATE TABLE test(id NUMBER);

Table created.

SQL> INSERT INTO test VALUES(1);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> 
```

ここで、作成した表testに対してクエリを実行してみます。
```
SQL> SELECT * FROM test;
SELECT * FROM test
              *
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-02063: preceding line from ADB$LINK
Help: https://docs.oracle.com/error-help/db/ora-00942/


SQL>
```

クエリがオフロードされますが、クローンのリフレッシュが実行されておらず、クローンにはオブジェクト（test表）が存在しないため、エラーになりました。

ここで、OCIコンソールからプール・リーダーの全てのリフレッシュ可能クローンをリフレッシュします。

プール・リーダーの全てのリフレッシュ可能クローンのリフレッシュには、こちらを使用しました。

https://qiita.com/500InternalServerError/items/d55f0c48e293be283e2e

ここで、作成した表testに対してクエリを実行してみます。
```
SQL> SELECT * FROM test;

        ID
----------
	     1

SQL>
```

クローンのリフレッシュ実行後は、クローンにもオブジェクトが存在するため、クエリがプール・メンバーにオフロードされてもエラーにならず、正しく結果が返ってきました。

DBMS_PROXY_SQL.DISABLE_READ_ONLY_OFFLOADプロシージャでQuery Offloadを無効化します。
```
SQL> EXEC DBMS_PROXY_SQL.DISABLE_READ_ONLY_OFFLOAD;

PL/SQL procedure successfully completed.

SQL> 
```

dba_pdbsビューのcloud_identity列からデータベース名を抽出します。
```
SQL> SELECT JSON_VALUE(cloud_identity, '$.DATABASE_NAME') database_name FROM dba_pdbs;

DATABASE_NAME
--------------------------------------------------------------------------------
POOLLEADER19C

SQL>
```
Query Offloadが無効化されたので、クエリはプール・リーダーで実行され、プール・リーダーのデータベース名が返ってきました。

# 参考情報
・[Offload Queries from Elastic Pool Leader to Member Refreshable Clones](https://docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/elastic-pools-offload-queries.html#GUID-E5704298-565A-407E-B6EE-0148924FCE85)
・[DBMS_PROXY_SQL.ENABLE_READ_ONLY_OFFLOADプロシージャ](https://docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/dbms-proxy-sql.html#GUID-A0BB5BEC-731F-4796-B1EB-4CBD6700932A)
・[DBMS_PROXY_SQL.DISABLE_READ_ONLY_OFFLOADプロシージャ](https://docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/dbms-proxy-sql.html#GUID-4962E6F3-B02D-4930-B334-A268A12B53C1)
・[DBMS_APPLICATION_INFO.SET_MODULEプロシージャ](https://docs.oracle.com/cd/G11854_01/arpls/DBMS_APPLICATION_INFO.html#GUID-B2E2BD20-D91D-40DB-A3F6-37A853384F30)
・[]()