Autonomous Database機能有効化手順

Autonomous Databaseのチューニング機能の中でも、手動で有効化・無効化が可能な設定及び方法を以下まとめました。

・オプティマイザヒントの管理
・結果キャッシュ
・自動索引作成の管理
・自動パーティション化の管理
・SQLトレースの有効化
・コネクション・プールの設定
・リソース・マネージャの消費制限違反に基づく自動SQL隔離

---

・オプティマイザヒントの管理

参照：https://docs.oracle.com/ja-jp/iaas/autonomous-database/doc/manage-optimizer-statistics.html

分析およびデータ・ウェアハウス用のAutonomous Database: Autonomous Databaseとデータ・ウェアハウスの無視オプティマイザ・ヒントおよびSQL文のPARALLELヒントはデフォルトです。
アプリケーションがヒントに依存している場合、オプティマイザ・ヒントを有効にするには、ALTER SESSIONまたはALTER SYSTEMを使用して、セッション・レベルまたはシステム・レベルでパラメータOPTIMIZER_IGNORE_HINTSをFALSEに設定します。
たとえば、次のコマンドは、セッションでヒントを有効にします:

ALTER SESSION 
  SET OPTIMIZER_IGNORE_HINTS=FALSE;

また、SQL文内のPARALLELヒントを有効にするには、ALTER SESSIONまたはALTER SYSTEMを使用して、セッション・レベルまたはシステム・レベルでOPTIMIZER_IGNORE_PARALLEL_HINTSをFALSEに設定します。
たとえば、次のコマンドは、セッションでPARALLELヒントを有効にします:

ALTER SESSION 
  SET OPTIMIZER_IGNORE_PARALLEL_HINTS=FALSE;

---

・結果キャッシュ

参照：https://oracle-japan.github.io/ocidocs/faq/services/autonomous/autonomous-database-faq/

Autonomous Data Warehouse (ADW) はDWHの高速化機能の一つであるResult Cacheがデフォルトで有効化されており、あくまでもそのままお使いいただくことを推奨しています。（Autonomous Transaction Processing(ATP)はデフォルト無効です）

とは言え、性能試験にてSQLの処理時間を計測したい場合、Result Cacheが有効だとどんなに時間がかかるSQLでも2回目の計測以降は1秒未満の結果となるため、Result Cacheを無効化したいケースがあります。
Result Cacheを無効化したい場合は以下をお試しください。

ヒントで制御

  SELECT /*+ NO_RESULT_CACHE */ count(*) from ssb.customer ;

プロシージャで制御

      exec DBMS_RESULT_CACHE.BYPASS(TRUE);
      SELECT DBMS_RESULT_CACHE.STATUS FROM DUAL;  -- 確認用SQL
      SELECT count(*) from ssb.customer ;

ちなみに、性能試験としてResult Cacheをクリアしたい場合は以下を実施ください。

 sql execute dbms_result_cache.flush ; 

---

・自動索引作成の管理

参照：https://docs.oracle.com/cd//E83857_01/paas/autonomous-database/adbsa/autonomous-auto-index.html#GUID-D51A4F85-3AE5-4B7D-9B27-32CEDE8DBD79

自動索引作成を有効化するには:

DBMS_AUTO_INDEX.CONFIGUREプロシージャを使用して、自動索引作成を使用可能にします:

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');

これにより、データベースでの自動索引作成を有効にし、新しい自動索引を可視索引として作成することで、SQL文で使用できるようにします。
DBMS_AUTO_INDEXパッケージを使用して、自動タスクに関するレポートを作成し、自動索引作成プリファレンスを設定します。

ノート:自動索引作成を有効にすると、自動索引の索引圧縮がデフォルトで有効になります。

自動索引作成を無効化するには:

次の文は、データベースでの自動索引作成を無効にすることで、新しい自動索引が作成されないようにします(既存の自動索引は有効のまま)。

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');

---

・自動パーティション化の管理

参照：https://docs.oracle.com/cd/E83857_01/paas/autonomous-database/adbsa/mdw-managing-automatic-partitioning-autonomous-database.html#GUID-3BD[…]C2972821

自動索引付けとは異なり、自動パーティション化をバックグラウンド・タスクとして定期的に実行しません。 自動パーティション化が実行されるのは、DBMS_AUTO_PARTITION.RECOMMEND_PARTITION_METHOD関数を使用して起動した場合のみです。
自動パーティション化を起動すると、自動パーティション化候補の表が識別され、パーティション・スキームが評価され、パーティション化計画が実装されます。

自動パーティション化を起動すると、次のタスクが実行されます:

1. 選択した候補表のワークロードを分析して、自動パーティション化の候補表を識別します。
   デフォルトでは、自動パーティション化では、Autonomous Databaseで収集されたワークロード情報を分析に使用します。 ワークロードのサイズによっては、問合せのサンプルが考慮されます。

2.パフォーマンス上の利点のワークロード分析、定量化および検証に基づいてパーティション・スキームを評価します:

a.統計が合成された候補空のパーティション・スキームは内部で作成され、パフォーマンスのために分析されます。

b. IOの推定削減が最も高い候補スキームが最適なパーティション化戦略として選択され、パフォーマンスをテストおよび検証するために内部的に実装されます。

c.候補パーティション・スキームによって、指定されたパフォーマンスおよび回帰基準を超えるパフォーマンスが改善されない場合、自動パーティション化はお薦めしません。

3.自動パーティション化手順で分析された表に対して最適なパーティション化方法を実装します(構成されている場合)。

★自動パーティション化の構成

自動パーティション化オプションを構成するには、DBMS_AUTO_PARTITION.CONFIGUREプロシージャを使用します。

推奨事項の有効化と実装

EXEC DBMS_AUTO_PARTITION.CONFIGURE('AUTO_PARTITION_MODE','IMPLEMENT');

推奨事項を有効にするが、これらの推奨事項は実装しない

EXEC DBMS_AUTO_PARTITION.CONFIGURE('AUTO_PARTITION_MODE','REPORT ONLY');

新しい推奨事項と新しい推奨事項の実装を無効にします

EXEC DBMS_AUTO_PARTITION.CONFIGURE('AUTO_PARTITION_MODE','OFF');

---

・SQLトレースの有効化

参照：https://docs.oracle.com/cd/E83857_01/paas/autonomous-database/adbsa/application-tracing-enable.html#GUID-E0A048E2-59E9-48BA-B215-8B789633C089

SQLトレースを有効にするには、次の手順を実行します:
1.(オプション)アプリケーションのクライアント識別子を設定します。 このステップはオプションですが、推奨されています。 SQLトレースでは、トレース・ファイルがCloud Object Storeに書き込まれるときに、クライアント識別子をトレース・ファイル名のコンポーネントとして使用します。 次に例を示します:

BEGIN
  DBMS_SESSION.SET_IDENTIFIER('sqlt_test');
END;
/

2.(オプション)アプリケーションのモジュール名を設定します。 このステップはオプションですが、推奨されています。 SQLトレースでは、トレース・ファイルがCloud Object Storeに書き込まれるときに、モジュール名がトレース・ファイル名のコンポーネントとして使用されます。 次に例を示します:

BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE('modname', null);
END;
/

3.SQLトレース機能を有効にします。

ALTER SESSION SET SQL_TRACE = TRUE;

4.ワークロードを実行します。
このステップでは、アプリケーション全体またはアプリケーションの特定の部分を実行します。 データベース・セッションでワークロードを実行すると、SQLトレース・データが収集されます。

5.SQLトレースを無効にします。
SQLトレースを無効にすると、セッションの収集されたデータは、セッションの表、およびSQLトレースの設定時に構成するバケット内のトレース・ファイルに書き込まれます。 詳細は「Autonomous DatabaseでのSQLトレースの無効化」を参照してください。

---

・コネクション・プールの設定　Database Resident Connection Pool （DRCP：常駐接続プール）

（https://docs.oracle.com/cd//E83857_01/paas/autonomous-database/adbsa/connect-drcp.html#GUID-E1337EC6-4A78-4199-84F0-A2739055F3FA）

Autonomous Databaseの場合は、データベース常駐接続プール(DRCP)を使用する際に次の点に注意してください:

・DRCPはデフォルトで有効になっていますが、DRCPの使用はオプションです。
プールされた接続を選択するには、tnsnames.oraでSERVER=POOLEDを指定します。 SERVER=POOLEDを指定しない場合は、専用の接続を使用して接続します。

・DRCPを起動または停止できません。

---

・リソース・マネージャの消費制限違反に基づく自動SQL隔離

参照：https://docs.oracle.com/cd//E83857_01/paas/autonomous-database/adbsa/autonomous-experienced-database-features.html#GUID-D7CBE041-8[…]F4-D7620A278358

Oracle Database内の過剰なシステム・リソースを使用するためにリソース・マネージャによって終了される文のようなSQL文の実行計画を隔離します。 リソース・マネージャの消費制限違反に基づく自動SQL隔離はデフォルトで無効になっていますが、手動で隔離されたSQL文はすべて適用されます。

詳細は、「過剰なシステム・リソースを使用するSQL文の実行計画の隔離」を参照してください。
（https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/diagnosing-and-resolving-problems.html#GUID-138E9175-A847-441E-9C3C-DB919BE594）
↓　↓　↓

DBMS_SQLQ.ALTER_QUARANTINEのプロシージャを利用して隔離設定を有効化もしくは無効化できます。DBMS_SQLQ.ALTER_QUARANTINEプロシージャを作成すると隔離設定はデフォルトで有効になります.

・無効化の例
（The following example disables the quarantine configuration having the name SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4:）

 BEGIN
    DBMS_SQLQ.ALTER_QUARANTINE(
       QUARANTINE_NAME => 'SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4',
       PARAMETER_NAME  => 'ENABLED',
       PARAMETER_VALUE => 'NO');
END;
/ 

・有効化の例
（The following example enables the quarantine configuration having the name SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4:）

BEGIN
    DBMS_SQLQ.ALTER_QUARANTINE(
       QUARANTINE_NAME => 'SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4',
       PARAMETER_NAME  => 'ENABLED',
       PARAMETER_VALUE => 'YES');
END;
/

---