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

はじめに

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

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を作成

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コンソールからプール・リーダーの全てのリフレッシュ可能クローンをリフレッシュします。

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

ここで、作成した表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
・DBMS_PROXY_SQL.ENABLE_READ_ONLY_OFFLOADプロシージャ
・DBMS_PROXY_SQL.DISABLE_READ_ONLY_OFFLOADプロシージャ
・DBMS_APPLICATION_INFO.SET_MODULEプロシージャ
・