Oracle AI Database 26ai 新機能の「SQL履歴」について

この記事では、Oracle AI Database 26aiの新機能であるSQL履歴について紹介します。Oracle AI Database 26aiは、Oracle Databaseの最新バージョンです。

Oracle Database 23ai は Oracle AI Database 26ai に置き換えられました。
この記事は旧Oracle Database 23aiで公開されていた内容を含みます。
この記事では、Oracle AI Database 26aiとして紹介します。

SQLの実行情報取得は煩雑？

Oracle Databaseで、「このSQLのSQL_IDは？」「今どの実行計画を使ったのか？」を確認するのは案外面倒です。
私の場合、19c以前では「select * from table(DBMS_XPLAN.DISPLAY_CURSOR());」のように検索することで確認していました。これは直前に実施した1つのSQL分しか確認できないのがネックです。
ELAPSED_TIMEなどのパフォーマンス情報に関しても、Oracleの機能で確認しようと思うとAWRやStatspackの機能を使うことが考えられますが、「一回ちょっと確認したいな」程度の軽い気持ちで使うには手間がかかります。

SQL履歴とは？

SQL履歴は、Oracle AI Database 26aiで実装された機能です。
メモリー容量に応じて、各ユーザー・セッションでユーザーが発行したSQL文をベスト・エフォートで監視する機能です。
これによって、過去に実行したSQLの情報を気軽に確認できるようになります。

監視項目には、SQL_IDやSQL_TEXTに加えて、ELAPSED_TIMEなどのパフォーマンス情報があります。
各ユーザー・セッションが切断されるまでは、SQLに関する情報を遡って確認できます。19c以前で同じような情報を確認するための方法と比べると、「手軽さ」がこの機能の特徴ではないでしょうか。

SQL履歴は、デフォルトでは無効になっていますが、初期化パラメータSQL_HISTORY_ENABLEDをTRUEに設定することで使用できるようになります。
SQL履歴の確認には、V$SQL_HISTORY動的パフォーマンス・ビューを参照します。

V$SQL_HISTORYの列

V\$SQL_HISTORYの列を見てみます。
ここでは、気になった一部の列項目を抜粋しています。V$SQL_HISTORYにはもっと多くの項目がありますので、完全版はマニュアルを確認してみてください。
先ほども記載しましたが、SQLを1回実行した分のパフォーマンス情報を確認しようと思うと、案外手間がかかります。その点、コマンドだけで履歴として出力されるのはとても手軽です。
仮に、SQL実行時にエラーが発生した場合は、エラー番号なども出力されますね。

列	データ型	説明
SQL_ID	VARCHAR2(13)	SQL文のSQL識別子
ELAPSED_TIME	NUMBER	SQL文の経過時間(マイクロ秒単位)
CPU_TIME	NUMBER	SQL文のCPU時間(マイクロ秒単位)
BUFFER_GETS	NUMBER	SQL文のバッファ取得操作数
PHYSICAL_READ_BYTES	NUMBER	SQL文によってディスクから読み取られたバイト数
PHYSICAL_WRITE_BYTES	NUMBER	SQL文によってディスクに書き込まれたバイト数
SQL_TEXT	VARCHAR2(100)	SQL文のテキストの最初の100文字(最大)
PLAN_HASH_VALUE	NUMBER	SQLプランのハッシュ値
SESSION_USER#	NUMBER	SQL文を発行したセッション・ユーザーの一意のID番号
ERROR_SIGNALLED	VARCHAR2(1)	SQL文がエラーを生成したか(Y)しないか(N)
ERROR_NUMBER	NUMBER	SQL文の実行に失敗した場合のエラー番号(たとえば、エラーORA-00932の場合は932)
ERROR_FACILITY	VARCHAR2(4)	SQL文の実行に失敗した場合のエラー機構(たとえば、エラーORA-00932の場合はORA)
STATEMENT_TYPE	VARCHAR2(5)	SQL文のタイプ: DDL DML PLSQL QUERY OTHER
IS_PARALLEL	VARCHAR2(1)	SQLがパラレル実行として実行されたかどうか(Y |N)

参照：Oracle Database データベース・リファレンス 26ai

実機検証

実際にOracle AI Database 26aiに接続し、動作を確認してみます。
まず、SQL_HISTORY_ENABLEDはデフォルトではFALSEです。

SQL> show parameter SQL_HISTORY_ENABLED

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sql_history_enabled                  boolean     FALSE

機能を使用するために、SQL_HISTORY_ENABLEDをTRUEに変更します。

SQL> alter system set SQL_HISTORY_ENABLED=true scope = both;

System altered.

その後、新規セッションからv$sql_historyが使用可能になっていることが確認できます。

SQL> set linesize 1000
SQL> set pagesize 1000
SQL> select SQL_TEXT,SQL_ID from v$sql_history;

SQL_TEXT                                                                                             SQL_ID
---------------------------------------------------------------------------------------------------- -------------
SELECT DECODE(USER, 'XS$NULL',  XS_SYS_CONTEXT('XS$SESSION','USERNAME'), USER) FROM SYS.DUAL         6u5zqzz2nm55c

SQLの履歴が出力されていることが確認できます。
ヒント句を付与した場合、ヒント句も出力されます。

SQL> select SQL_TEXT,SQL_ID from v$sql_history;

SQL_TEXT                                                                                             SQL_ID
---------------------------------------------------------------------------------------------------- -------------
SELECT DECODE(USER, 'XS$NULL',  XS_SYS_CONTEXT('XS$SESSION','USERNAME'), USER) FROM SYS.DUAL         6u5zqzz2nm55c
select SQL_TEXT,SQL_ID from v$sql_history                                                            4564w3wzs4tkp

SQL> select /*test1*/ SQL_TEXT,SQL_ID from v$sql_history;

SQL_TEXT                                                                                             SQL_ID
---------------------------------------------------------------------------------------------------- -------------
SELECT DECODE(USER, 'XS$NULL',  XS_SYS_CONTEXT('XS$SESSION','USERNAME'), USER) FROM SYS.DUAL         6u5zqzz2nm55c
select SQL_TEXT,SQL_ID from v$sql_history                                                            4564w3wzs4tkp
select SQL_TEXT,SQL_ID from v$sql_history                                                            4564w3wzs4tkp

SQL> select /*test1*/ SQL_TEXT,SQL_ID from v$sql_history;

SQL_TEXT                                                                                             SQL_ID
---------------------------------------------------------------------------------------------------- -------------
SELECT DECODE(USER, 'XS$NULL',  XS_SYS_CONTEXT('XS$SESSION','USERNAME'), USER) FROM SYS.DUAL         6u5zqzz2nm55c
select SQL_TEXT,SQL_ID from v$sql_history                                                            4564w3wzs4tkp
select SQL_TEXT,SQL_ID from v$sql_history                                                            4564w3wzs4tkp
select /*test1*/ SQL_TEXT,SQL_ID from v$sql_history                                                  2jjv41tuyf58f

注意点

• セッション継続中のみ確認可能
  セッションが切断されると、履歴は消えてしまいます。監査のような目的には使用できないですね。
  この機能は、他のユーザーが過去に何を実行したのかを確認する機能ではなく、開発中にユーザー自身がSQLの実行情報を確認する機能と思った方がよさそうです。

• メモリー容量に応じたベスト・エフォートで監視する
  あくまでメモリー容量の範囲内で、ベスト・エフォートで監視する機能です。
  万が一にも履歴が消えてしまうと困る、といった用途には使用できないですね。

まとめ

SQL履歴は、開発中の環境などで有効な機能ではないかと思います。
手軽に使用できるため、Oracle AI Database 26aiでの開発時にはお試しください。

We Are Hiring!