【Oracle Database】メモリを使用しているセッションを調べる方法

メモリを使用しているセッションの確認方法

「○○しているセッションを調べる方法を教えて欲しい」というお問い合わせをいただくことがあります。○○の部分は「CPUを使用」、「メモリを使用」、「REDOを多く生成」など様々ですが、これらの確認方法を知っておくことは、データベースの管理を行う上で重要です。

今回はメモリを使用しているセッションの確認方法を2つ紹介します。

データベースサーバのメモリ使用率が高騰したら…

データベースサーバのメモリ使用率が高騰した際には、まず、OS側(Windows：パフォーマンスモニタ、Unix系：psやtopなど)からOracle Databaseのプロセスがメモリを使用しているのかの確認を行います。

もしメモリを使用しているのがOracle Databaseのプロセスだった場合は、そのプロセスはどのセッションに紐付いていて、どのような処理でメモリを使用しているのか確認します。

V$ビューを使用してメモリを使用しているセッションを特定

メモリを使用しているセッションの特定には動的パフォーマンスビュー（V$ビュー）のV$PROCESSとV$SESSIONを使用します。

V$PROCESSにはPGA_ALLOC_MEMという列があります。この列はプロセスによって現在割り当てられており、OSには解放されていないPGAをバイト単位で表示します。

V$PROCESSだけではセッションの情報（DBユーザ名やセッションの状態）を確認することができないため、V$SESSIONの情報も確認します。

V$PROCESSのADDR列とV$SESSIONのPADDR列の値は同じ値を持ちますので、この値を使用して結合します。既にpsやtopなどのOSコマンドによりメモリを使用しているプロセスのプロセスIDが判明しているのであれば、V$PROCESS.SPID列の値をWHERE句の条件に指定します。以下はOS側でプロセスID4880のプロセスがメモリを使用していた場合の実行例です。

　
SQL> ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS';
SQL> SELECT
  2      to_char(sysdate)
  3      ,s.sid
  4      ,s.serial#
  5      ,s.username
  6      ,s.machine
  7      ,s.event
  8      ,s.state
  9      ,s.status
 10      ,s.sql_address
 11      ,s.sql_hash_value
 12      ,p.program
 13      ,p.pga_alloc_mem
 14  FROM v$session s,v$process p
 15  WHERE s.paddr = p.addr
 16  AND p.spid = 4880;
　
TO_CHAR(SYSDATE)           SID    SERIAL# USERNAME MACHINE                   EVENT                     STATE             STATUS   SQL_ADDRESS      SQL_HASH_VALUE PROGRAM           PGA_ALLOC_MEM
------------------- ---------- ---------- -------- ------------------------- ------------------------- ----------------- -------- ---------------- -------------- ----------------- -------------
2017-03-09 14:08:39        124      30506 TESTUSR  WORKGROUP\WIN-L3UL0ER9R6K SQL*Net message to client WAITED SHORT TIME ACTIVE   000007FF1C7273C0      405230308 ORACLE.EXE (SHAD)     122261759

この例ではSID：124、ユーザ名：TESTUSRがSQL_ADDRESS：000007FF1C7273C0／SQL_HASH_VALUE：405230308を実行中(STATUS=ACTIVE／STATE=WAITED SHORT TIME)でPGAを約116MB獲得していることがわかります。また、このセッションの接続元クライアントマシン名はWORKGROUP\WIN-L3UL0ER9R6Kです。

WHERE句の条件を"p.pga_alloc_mem >= 104857600"のように変えれば、指定したサイズ以上にPGAを獲得しているセッションを調べることもできます。

なお、今回はわかりやすいようにV$SESSION、V$PROCESSのまま記載していますが、マニュアルに記載のとおり V$ビューの並べ替えや結合はサポートされていません。CREATE TABLE AS SELECTなどで別の表に書き出した上でご実行ください。

処理内容はV$SQLTEXTからSQL_ADDRESSとSQL_HASH_VALUE、もしくはSQL_IDを使用して検索することで確認できます。

　
SQL> SELECT
  2      sql_text
  3  FROM
  4      v$sqltext
  5  WHERE
  6      address = '000007FF1C7273C0'
  7  AND hash_value = 405230308
  8  ORDER BY piece;
　
SQL_TEXT
----------------------------------------------------------------
select count(*) from (select * from dba_source a, dba_source b,
dba_source c, dba_source d, dba_source e, dba_source f, dba_sour
ce g, dba_source h, dba_source i, dba_source j, dba_source k ord
er by 1)

もしこのセッションの処理が意図しないものであれば、クライアントから処理を停止させるか、ALTER SYSTEM KILL SESSIONでKILLするなどで対応することになるでしょう。

　
SQL> ALTER SYSTEM KILL SESSION '&SID,&SERIAL#' IMMEDIATE;

ASHを使用してメモリを使用しているセッションを特定

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

V$ビューではSELECT実行時に存在しているセッションやプロセスの情報しか確認ができませんが、ASHが利用可能であればDBA_HIST_ACTIVE_SESS_HISTORYから過去のセッション情報を確認できます。そのため、問題の対処優先でデータベースに接続しているセッションのKILLなどを行ってしまった後でも遡って調査を行うことができます。

DBA_HIST_ACTIVE_SESS_HISTORYではアクティブなセッションの情報の履歴が10秒間隔で保存されています。たとえば以下のようなSQLを使用することで過去のある時間帯に絞ってPGAを獲得していたセッションを確認するといったことも可能です。

　
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      ,pga_allocated
 14  FROM
 15      dba_hist_active_sess_history
 16  WHERE
 17      sample_time BETWEEN to_timestamp ('17-03-09 14:05:00', 'YY-MM-DD HH24:MI:SS')
 18                  AND to_timestamp ('17-03-09 14:10:00', 'YY-MM-DD HH24:MI:SS')
 19  ORDER BY 1
 20  ;
　
SAMPLE_TIME           SESSION_ID SESSION_SERIAL#       SEQ#    USER_ID SQL_ID        TOP_LEVEL_SQL EVENT      SESSION PROGRAM            MACHINE                   PGA_ALLOCATED
--------------------- ---------- --------------- ---------- ---------- ------------- ------------- ---------- ------- ------------------ ------------------------- -------------
17-03-09 14:06:09.211        228           50729        232        111 46qnc2fc3y614 46qnc2fc3y614            ON CPU  sqlplus.exe        WORKGROUP\WIN-L3UL0ER9R6K      43843584
17-03-09 14:06:19.351        228           50729        232        111 46qnc2fc3y614 46qnc2fc3y614            ON CPU  sqlplus.exe        WORKGROUP\WIN-L3UL0ER9R6K      43843584
17-03-09 14:07:30.269        124           30506         35        111 budghy0c2fnr4 budghy0c2fnr4            ON CPU  sqlplus.exe        WORKGROUP\WIN-L3UL0ER9R6K     117309440
17-03-09 14:07:40.409        124           30506         35        111 budghy0c2fnr4 budghy0c2fnr4            ON CPU  sqlplus.exe        WORKGROUP\WIN-L3UL0ER9R6K     118947840
17-03-09 14:07:50.549        124           30506         35        111 budghy0c2fnr4 budghy0c2fnr4            ON CPU  sqlplus.exe        WORKGROUP\WIN-L3UL0ER9R6K     118947840
17-03-09 14:08:00.686        124           30506         35        111 budghy0c2fnr4 budghy0c2fnr4            ON CPU  sqlplus.exe        WORKGROUP\WIN-L3UL0ER9R6K      99418112
17-03-09 14:08:10.826        124           30506         35        111 budghy0c2fnr4 budghy0c2fnr4            ON CPU  sqlplus.exe        WORKGROUP\WIN-L3UL0ER9R6K      99418112
17-03-09 14:08:20.966        124           30506         35        111 budghy0c2fnr4 budghy0c2fnr4            ON CPU  sqlplus.exe        WORKGROUP\WIN-L3UL0ER9R6K      99418112
17-03-09 14:08:20.966        356            9006       7390          0                                        ON CPU  ORACLE.EXE (PSP0)  WIN-L3UL0ER9R6K                  721920
17-03-09 14:08:31.106        124           30506         35        111 budghy0c2fnr4 budghy0c2fnr4            ON CPU  sqlplus.exe        WORKGROUP\WIN-L3UL0ER9R6K      99418112
17-03-09 14:08:41.246        124           30506         35        111 budghy0c2fnr4 budghy0c2fnr4            ON CPU  sqlplus.exe        WORKGROUP\WIN-L3UL0ER9R6K      99418112

この例では先程のSID：124のセッションが14:07:30からPGAを獲得していたことがわかります。DBA_HIST_ACTIVE_SESS_HISTORYにはUSERNAME列はありませんが、USER_ID列はALL_USERSのUSER_ID列と同じ値ですので、こちらからユーザ名を確認できます。

実行されていたSQL文はV$SQLTEXTからSQL_IDを用いて確認できますが、共有プール上からフラッシュされてしまった場合には確認することができません。定期的にAWRによってDBA_HIST_SQLTEXTにSQL文が挿入されているため、V$SQLTEXTから確認できない場合でもAWRから確認できる可能性があります。

以下は、AWRからSQL_ID:budghy0c2fnr4のSQL文と実行計画を表示する例です。

　
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('budghy0c2fnr4',null,null,'ALL'));
　
PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
SQL_ID budghy0c2fnr4                                                            
--------------------                                                            
select count(*) from (select * from dba_source a, dba_source b,                 
dba_source c, dba_source d, dba_source e, dba_source f, dba_source g,           
dba_source h, dba_source i, dba_source j, dba_source k order by 1)         
：
略

まとめ

今回はV$SESSION、V$PROCESS、DBA_HIST_ACTIVE_SESS_HISTORYを使用したメモリを使用しているセッションの調査方法をご紹介しました。これらのビューの列の意味はリファレンスマニュアルに記載されており、今回例示した列以外からも様々な情報を得ることができます。

V$ビューやASHを確認しても特定のセッションが多くのメモリを使用している状況でない場合は、セッション数が普段より増加していないかといった点も確認のポイントになります。日頃からどのアプリケーションからどの程度の接続があるのかを把握しておくことで、問題発生箇所の早期切り分けに繋がります。

なお、Oracle Database 12.1 以降では初期化パラメータPGA_AGGREGATE_LIMITによりインスタンスで消費できる総PGAのハードリミットを設定できます。設定値を超えるPGAの獲得が行われた場合にはORA-04036が発生し、その処理は失敗します。

筆者情報

大野高志

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

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

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

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