※ 本記事は、Ulrike Schwinnによる”SQL statement tracking in 23c with SQL History“を翻訳したものです。
2024年2月1日
セッションで実行された文のリストが必要ですか? もしSQL文のテキストだけでよければ、SQL Developer、SQLcl、SQL*PlusなどのOracleデータベース・ツールを使用できます。これらのすべてのユーティリティでは、履歴機能が利用可能です。一方、実行パフォーマンスなど、さらに詳細な情報を取得する場合は、もちろんSQLチューニング・セットが選択できます。SQLチューニング・セットの詳細は、『Oracle SQLチューニング・セット(STS) – SQLチューニングの基礎』の投稿を参照してください。
23cでは、SQL文に関する情報(SQL履歴と呼ばれる)を受け取る選択肢もあります。SQL履歴を使用すると、データベースは現在のセッションでの問合せのSQL履歴の詳細を追跡およびレポートします。メモリー容量に応じて、各ユーザー・セッションでユーザーが発行したSQL文をベスト・エフォートで監視します。DDL文、DML文および問合せ文を監視します。表示されたエラー・メッセージも含めてです。ただし、バックグラウンドで発行されたSQL文で、SYSスキーマの文や再帰的SQL文は除外します。監視対象情報は、V$SQL_HISTORY動的パフォーマンス・ビューによって確認でき、新しく導入された初期化パラメータを使用して有効化および無効化できます。
これを使用する場合は、ALTER SYTEM権限を持つユーザーが、最初に初期化パラメータSQL_HISTORY_ENABLEDをインスタンス全体でTRUEに変更する必要があります(デフォルトはFALSEです)。SQL_HISTORY_ENABLEDは、PDBが変更可能です。
どのように動作するかを把握するために、いずれかのPDBでV$PARAMETERを確認してみます。私の場合は、OCI Oracle Base Database Serviceで23cを使用しました
SQL> alter session set container=DB233_PDB1; Session altered. SQL> col name format a15 SQL> col value format a10 SQL> col isdefault format a10 SQL> select name, value, default_value, isdefault from v$parameter where name like 'sql_hist%'; NAME VALUE DEFAULT_VALUE ISDEFAULT -------------------- ---------- ------------------------- ---------- sql_history_enabled FALSE FALSE TRUE
ALTER SYSTEM権限を持つユーザーとして、PDBのDB233_PDB1に接続し、パラメータを変更します。
SQL> alter system set sql_history_enabled=true scope=both; System altered.
これで、データベース・ユーザー(sysではない)はPDBに接続し、SQLデータベース・アプリケーションを実行して、実行された文をV$SQL_MONITORから監視できるようになります。
SQL> connect scott/yyyy@DB23 Connected. -- check the parameter SQL> sho parameter sql_history_enabled NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sql_history_enabled boolean TRUE
ここでは、2つの単純な文を使用してこの機能を試してみましょう。2番目の文にはエラーを含みます。
ノート: ご存知のように、23cではFROM句は不要になりました。したがって、最初の問合せは正しいです 😉
SQL> select sysdate; SYSDATE --------- 08-DEC-23 1 row selected. SQL> select xxx from dual; select xxx from dual * ERROR at line 1: ORA-00904: "XXX": invalid identifier Help: https://docs.oracle.com/error-help/db/ora-00904/
これで、V$SQL_MONITORを問い合せし、文履歴をリストできます(例えば、列名を含むSQLテキストやエラー番号など)。
SQL> select sql_text, error_number from v$sql_history;
SQL_TEXT
--------------------------------------------------------------------------------
ERROR_NUMBER
------------
SELECT DECODE(USER, 'XS$NULL', XS_SYS_CONTEXT('XS$SESSION','USERNAME'), USER) F
ROM SYS.DUAL
0
select sysdate
0
select xxx from dual
904
より広範な例でこの機能を理解するために、別のデータベース・ユーザーに接続し、サンプルSQLアプリケーションを実行します。
SQL> connect dwh_data/xxxx@DB23
SQL> start TEST_SQLS.sql ....
SQL履歴は、メモリー容量に応じてベスト・エフォートで動作することに注意してください。内部パラメータによって文の最小数が決まります。
私の場合は、約60個の文がスクリプトの完了後にV$SQL_HISTORYで収集されました。では、セッションを接続しているうちに、それらをリストしてみます。もし、文のリストを保持してさらに調査したい場合は、永続的な表を作成して対応しましょう。
V$SQL_HISTORYで確認可能な列には、SQL_TEXTおよびERROR_NUMBER以外に何があるか確認してみましょう。
SQL> desc v$sql_history Name Null? Type ----------------------------------------- -------- ---------------------------- KEY NUMBER SQL_ID VARCHAR2(13) ELAPSED_TIME NUMBER CPU_TIME NUMBER BUFFER_GETS NUMBER IO_INTERCONNECT_BYTES NUMBER PHYSICAL_READ_REQUESTS NUMBER PHYSICAL_READ_BYTES NUMBER PHYSICAL_WRITE_REQUESTS NUMBER PHYSICAL_WRITE_BYTES NUMBER PLSQL_EXEC_TIME NUMBER JAVA_EXEC_TIME NUMBER CLUSTER_WAIT_TIME NUMBER CONCURRENCY_WAIT_TIME NUMBER APPLICATION_WAIT_TIME NUMBER USER_IO_WAIT_TIME NUMBER IO_CELL_UNCOMPRESSED_BYTES NUMBER IO_CELL_OFFLOAD_ELIGIBLE_BYTES NUMBER SQL_TEXT VARCHAR2(100) PLAN_HASH_VALUE NUMBER SQL_EXEC_ID NUMBER SQL_EXEC_START DATE LAST_ACTIVE_TIME DATE SESSION_USER# NUMBER CURRENT_USER# NUMBER CHILD_NUMBER NUMBER SID NUMBER SESSION_SERIAL# NUMBER MODULE_HASH NUMBER ACTION_HASH NUMBER SERVICE_HASH NUMBER IS_FULL_SQLTEXT VARCHAR2(1) ERROR_SIGNALLED VARCHAR2(1) ERROR_NUMBER NUMBER ERROR_FACILITY VARCHAR2(4) STATEMENT_TYPE VARCHAR2(5) IS_PARALLEL VARCHAR2(1) CON_ID NUMBER
ここでは、CPU時間やELAPSED時間など実行統計の一部や、文がパラレル実行されたか、確認したくなりました。
SQL> select sql_text, elapsed_time, cpu_time, buffer_gets, is_parallel from v$sql_history; SQL_TEXT -------------------------------------------------------------------------------- ELAPSED_TIME CPU_TIME BUFFER_GETS I ------------ ---------- ----------- - SELECT 'B' || tt1.PG_FeatureValue_07_1 PG_FeatureValue_07_1, 'B' || 1010415 repchannel_1, 35 0 0 N SELECT 'B' || tt1.PG_FeatureValue_07_1 PG_FeatureValue_07_1, 'B' || 1010415 repchannel_1, 1229896 1229644 148181 N ...
注意: SQL_TEXT列のSQLテキストは、SQL文のテキストの最初の100文字まで表示されます。
使いやすく非常に便利なので、ちょっとしたアドホック・テストを行い、各自のセッション内で発行されたSQL文とその特性について詳しく知りたい場合に、ぜひ使ってみましょう。Oracle 23c free editionおよびOCI Oracle Base Database Serviceでも利用できます。
続きを読む
- データベース・リファレンス: V$SQL_HISTORY
- データベース・リファレンス: SQl_HISTORY_ENABLED