【Oracle Database】一時表領域を使用していたセッションを調べる方法

一時表領域を使用していたセッションを調べる方法

索引の作成、ハッシュ結合、ORDER BY/GROUP BYを含むSELECTやソート・マージ結合などのソートを伴う処理がPGAで完結できない場合、一時表領域内の一時セグメントを使用します。

一時セグメントのサイズが不足すると拡張を行いますが、データファイルの最大サイズに達していた場合は拡張が行えず「ORA-01652：一時セグメントを拡張できません」のエラーが発生します。

サポートセンターにはORA-01652の原因究明のために「一時表領域を使用していたセッションの調べ方」のお問い合わせをいただくことがあります。そこで今回は一時表領域を使用していたセッションを調べる方法を3つ紹介します。

ORA-01652で失敗した処理が一時表領域を使用していたとは限らない

一時表領域内の一時セグメントは複数のセッションで共有利用します。エラーは拡張のタイミングで発生しますので、たとえば、データファイルの自動拡張をOFFにした100MBの一時表領域のうち、先行するセッションが99MBを使用している状態で、後発のセッションが2MBの領域が必要な処理を実行した場合、エラーを受けるのは後発のセッションです。

ORA-01652を受けたプロセスのTRACEファイルにはエラーを受けて失敗したSQL文が出力されますが「エラーを受けたSQL = 一時表領域を大量に使用していたSQL」とは限りません。

そのため、原因究明を行うにはエラーが発生する前の一時表領域の使用状況を調べる必要があります。

動的パフォーマンスビュー（V$ビュー）で調べる

一時表領域を使用しているセッションの確認には次の動的パフォーマンスビュー（V$ビュー）とディクショナリを使用します。

* V$SESSION
* V$TEMPSEG_USAGE
* DBA_TABLESPACES
* V$SQL

V$TEMPSEG_USAGEのBLOCKS列からはセッションごとに割り当てられた一時表領域のブロック数を確認できます。1ブロックあたりのサイズはDBA_TABLESPACESのBLOCK_SIZE列で確認できますので、「V$SORT_USAGE.BLOCKS * DBA_TABLESPACES.BLOCK_SIZE」でセッションが使用している一時表領域のサイズをバイト単位で求められます。

V$SESSIONのSADDR列とV$TEMPSEG_USAGEのSESSION_ADDRは同じ値を持ちますので、この値を使用して一時表領域を使用しているセッションのSID、SERIAL#、USERNAME、MACHINEといったセッションの情報を確認できます。

V$SESSIONのSQL_IDとV$SQLのSQL_IDは同じ値を持ちますので、V$SQLのSQL_TEXT列からセッションが実行中のSQL文を確認できます。

　
 SQL> SELECT
   2      ss.sid
   3      ,ss.serial#
   4      ,ss.username
   5      ,tu.tablespace
   6      ,SUM(tu.blocks) * dts.block_size / 1024 / 1024 used_mb
   7      ,sq.sql_text
   8  FROM
   9      v$tempseg_usage tu
  10      ,v$session ss
  11      ,dba_tablespaces dts
  12      ,v$sql sq
  13  WHERE
  14      tu.session_addr = ss.saddr
  15  AND ss.sql_id = sq.sql_id
  16  AND tu.tablespace = dts.tablespace_name
  17  GROUP BY
  18      ss.sid
  19      ,ss.serial#
  21      ,ss.username
  22      ,dts.block_size
  23      ,tu.tablespace
  24      ,sq.sql_text
  25  ORDER BY
  26      ss.sid;
　
        SID    SERIAL# USERNAME TABLESPACE USED_MB SQL_TEXT
 ---------- ---------- -------- ---------- ------- ----------------------------------------------------------------------------------------------------------
         10      16577 SCOTT    TEMP          2918 select d1.text from dba_source d1, dba_source d2 order by d1.line
         19      33326 TAKASHI  TEMP           229 select s.text,s.line,s.owner,u.user_id from dba_source s, dba_users u where s.owner != 'SYSMAN' order by 1

V$ビューを使用した確認ではSQL実行タイミングの情報しか確認できませんので、領域不足が発生した後（ORA-01652発生後）にこのSQLを実行しても有効な情報が得られない可能性があります。ORA-01652の発生に備え、原因を調べることができるようにするには、このようなSQLを定期的に実行する必要があります。

Oracle Enterprise Managerで調べる

Enterprise Edition + Oracle Diagnostics Pack + Oracle Tuning Packのライセンスをお持ちの環境であれば、Oracle Enterprise Manager（OEM）でリアルタイムSQL監視の機能が利用できます。

ORA-01652が発生するということは、一時表領域のデータファイルに対して拡張ができなくなるほどの大量の書き込み（物理I/O）が発生しています。リアルタイムSQL監視ではI/Oの多いSQLを確認可能ですので、この機能を利用することでORA-01652の原因となったセッションと処理を確認できます。

OEMトップ→パフォーマンス/パフォーマンス・ハブ→監視対象SQLよりI/Oリクエストの多いセッションとSQLを確認します。メモリーの"一時使用量"タブから一時表領域を大量に使用していた様子が確認できれば、そのSQLが原因と判断できます。

OEMパフォーマンス・ハブ

リアルタイム監視

なお、本画面に限らず、OEMのパフォーマンスに関連する画面の表示には基本的に EE+オプションライセンスが必要です。意図せずライセンス違反を行わないよう、ご利用のシステムに合わせて初期化パラメータ CONTROL_MANAGEMENT_PACK_ACCESS を設定いただくことを推奨します。

Active Session Historyで調べる

Enterprise Edition + Diagnostics Packの追加オプションライセンスをお持ちの環境であれば、Active Session History(ASH)を利用できます。

ASHが利用可能であればDBA_HIST_ACTIVE_SESS_HISTORYから過去のセッション情報を確認できます。DBA_HIST_ACTIVE_SESS_HISTORYのTEMP_SPACE_ALLOCATED列からはセッションに割り当てられた一時メモリのサイズを確認できますので、ORA-01652が発生した後でも遡って調査を行うことができます。

以下の例では17-04-05 20:56:38～17-04-05 20:58:08にかけてSID：140のセッションがSQL_ID：6x6uf7ydy8gpaの処理で一時メモリを急激に獲得していた様子が確認できます。

　
 SQL> SELECT
   2      sample_time
   3      ,session_id
   4      ,session_serial#
   5      ,seq#
   6      ,user_id
   7      ,sql_id
   8      ,top_level_sql_id
   9      ,event
  10      ,session_state
  11      ,program
  12      ,machine
  13      ,temp_space_allocated
  14  FROM
  15      dba_hist_active_sess_history
  16  WHERE
  17      sample_time BETWEEN to_timestamp ('17-04-05 20:55:00', 'YY-MM-DD HH24:MI:SS')
  18                  AND to_timestamp ('17-04-05 21:00:00', 'YY-MM-DD HH24:MI:SS')
  19  AND temp_space_allocated > 104857600
  20  ORDER BY 1
  21  ;
　
 SAMPLE_TIME           SESSION_ID SESSION_SERIAL#       SEQ#    USER_ID SQL_ID        TOP_LEVEL_SQL EVENT                        SESSION PROGRAM     MACHINE                   TEMP_SPACE_ALLOCATED
 --------------------- ---------- --------------- ---------- ---------- ------------- ------------- ---------------------------- ------- ----------- ------------------------- --------------------
 17-04-05 20:56:38.428        140           56962        579        135 6x6uf7ydy8gpa 6x6uf7ydy8gpa direct path write temp       WAITING sqlplus.exe WORKGROUP\WIN-L3UL0ER9R6K            176160768
 17-04-05 20:56:48.459        140           56962       1077        135 6x6uf7ydy8gpa 6x6uf7ydy8gpa direct path write temp       WAITING sqlplus.exe WORKGROUP\WIN-L3UL0ER9R6K            610271232
 17-04-05 20:56:58.486        140           56962       1681        135 6x6uf7ydy8gpa 6x6uf7ydy8gpa direct path write temp       WAITING sqlplus.exe WORKGROUP\WIN-L3UL0ER9R6K           1022361600
 17-04-05 20:57:08.517        140           56962       2355        135 6x6uf7ydy8gpa 6x6uf7ydy8gpa direct path write temp       WAITING sqlplus.exe WORKGROUP\WIN-L3UL0ER9R6K           1460666368
 17-04-05 20:57:18.579        140           56962       2769        135 6x6uf7ydy8gpa 6x6uf7ydy8gpa                              ON CPU  sqlplus.exe WORKGROUP\WIN-L3UL0ER9R6K           1741684736
 17-04-05 20:57:28.595        140           56962       3683        135 6x6uf7ydy8gpa 6x6uf7ydy8gpa                              ON CPU  sqlplus.exe WORKGROUP\WIN-L3UL0ER9R6K           2395996160
 17-04-05 20:57:38.625        140           56962       4264        135 6x6uf7ydy8gpa 6x6uf7ydy8gpa                              ON CPU  sqlplus.exe WORKGROUP\WIN-L3UL0ER9R6K           3219128320
 17-04-05 20:57:48.656        140           56962       5192        135 6x6uf7ydy8gpa 6x6uf7ydy8gpa Disk file operations I/O     WAITING sqlplus.exe WORKGROUP\WIN-L3UL0ER9R6K           3526361088
 17-04-05 20:57:58.703        140           56962       6528        135 6x6uf7ydy8gpa 6x6uf7ydy8gpa Disk file operations I/O     WAITING sqlplus.exe WORKGROUP\WIN-L3UL0ER9R6K           3589275648
 17-04-05 20:58:08.718        140           56962       7594        135 6x6uf7ydy8gpa 6x6uf7ydy8gpa control file sequential read WAITING sqlplus.exe WORKGROUP\WIN-L3UL0ER9R6K           3638558720

実行されていたSQL文はV$SQL、もしくはAWRに情報が保持されていれば確認できますが、レコードが返らないこともあります。そのような場合はSAMPLE_TIMEの時間を元に、MACHINE列のクライアントからPROGRAM列のプログラムで実行された処理をアプリケーション側から確認します。

　
  --V$SQLから確認
  SQL> SELECT
   2      sql_text
   3  FROM
   4      v$sqltext
   5  WHERE
   6      sql_id = '&SQL_ID'
   7  ORDER BY piece;

　
  --AWRから確認
　SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('&SQL_ID',null,null,'ALL'));

まとめ

メモリを使用しているセッションを調べる方法と同様、過去のセッションの情報を調べるためにはEnterprise Edition + オプションライセンスが必要です。

Standard EditionやEnterprise Editionのみの環境を管理される場合には、ご利用のシステムで運用上発生し得るトラブルを想定し、定期的な情報取得を行うなど、有事の際に調査を行える準備をしておくことが重要です。

筆者情報

大野高志

サービス事業部サポートセンター

2007年にアシスト入社後、Oracle Databaseのサポート業務に従事。現在はサポート業務の傍ら、未解決のトラブルを一つでも多く減らせるよう、サポートセンターに蓄積されているノウハウを社内外に伝える活動を行っている。

■本記事の内容について
　本記事に示した定義及び条件は変更される場合があります。あらかじめご了承ください。

■商標に関して
　・Oracle®、Java、MySQL及びNetSuiteは、Oracle、その子会社及び関連会社の米国及びその他の国における登録商標です。
　・Amazon Web Services、AWS、Powered by AWS ロゴ、[およびかかる資料で使用されるその他の AWS 商標] は、Amazon.com, Inc. またはその関連会社の商標です。
　　文中の社名、商品名等は各社の商標または登録商標である場合があります。