Database Support Blog

  • Oracle Database
2016.11.16

【Oracle Database】パフォーマンスダウンの原因追求に必要な情報取得サンプルスクリプト

【Oracle Database】パフォーマンスダウンの原因追求に必要な情報取得サンプルスクリプト

特定処理のパフォーマンスダウンや、CPU/メモリ負荷高騰が発生した際には、セッション/プロセス単位で情報を確認する必要があります。

Oracle Database Enterprise Edition + Diagnostics Packのライセンスをお持ちの環境であれば、セッションのKILLやデータベースの再起動で対処を行った後でも、DBA_ACTIVE_SESSION_HISTORYなどから処理は進んでいたか、PGAとしてどの程度メモリを獲得していたかといったV$SESSION+V$PROCESSの履歴に近い情報を確認できます。

Enterprise Edition単体やStandard Edition 2の場合では、管理者がこれらの情報を明示的に取得していないと、事象発生後に調査を行うことは難しくなります。

昨年末に『 パフォーマンスダウンを「再現待ち」にしないための準備 』という記事で定期的なV$ビューの取得を推奨しましたが、今回は具体的な情報取得方法を紹介します。

サンプルバッチ/シェルスクリプト

WindowsとLinuxの各環境用にV$SESSION/V$PROCESS/V$LOCKを10秒置きにログ出力するサンプルスクリプトをご用意しました。各V$ビューの末尾に情報取得日時の列を追加した結果をCSV形式で出力します。Oracle Database 12.1.0.2を想定していますが、ビューの列を対象バージョン用に変更すれば他のバージョンでも利用できます。


なお、取得した情報をどの程度の期間残す必要があるかは環境に依存するため、本サンプルスクリプトでは自動でファイルの削除は行いません。ディスク領域を圧迫する可能性がありますので必要に応じて定期的に削除を行ってください。

  • 本サンプルスクリプトについてのお問合わせは弊社サポートセンターではお受けしかねます。個人の責任の範囲内でご利用ください。

サンプルバッチ/シェルスクリプトで取得されたデータの利用方法

出力された<VIEWNAME>_<SID>_<DATE>.csvをデータベースにロードすることで、ASHのように過去のセッション情報を確認することができます。Oracle Database 12gR1以降であればSQL*Loader のExpress Modeの利用が最も容易です。

次の例ではサンプルスクリプトで定期的に取得したV$SESSIONのデータをDBにロードします。調査用ユーザに接続後、V$SESSIONと同じ定義の表を作成し、日付列を追加します。ロード用の表ができたらSQL*Loaderを使用してデータをロードします。

-- ロード用の表を作成
SQL> conn user/pwd
 
SQL> CREATE TABLE session_20161116 AS SELECT * FROM v$session
  2  WHERE 1=2;
 
SQL> ALTER TABLE session_20161116 ADD (snap_date VARCHAR2(30));
 
-- データのロード
% sqlldr user/pwd table=session_20161116 data=/tmp/vsession_v12102_20161116.log
SQL*Loader: Release 12.1.0.2.0 - Production on 水 11月 16 15:29:46 2016
 
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
 
エクスプレス・モード・ロードの表: SESSION_20161116
使用パス:      外部表, DEGREE_OF_PARALLELISM=AUTO
 
表SESSION_20161116:
  1298行のロードに成功しました。
 
確認するログ・ファイル:
  session_20161116.log
  session_20161116_%p.log_xt
ロードの詳細を参照してください。


Linux環境で取得したCSVデータをWindows環境上にロードする場合などは、改行コードの違いに注意する必要があります。ロードする環境に合わせて変更しましょう。

調査用のサンプルSQL

ロードしたV$ビューの情報を使用した調査を行う際に利用するサンプルSQLをいくつか紹介します。サンプルSQLを参考に検索列やWHERE句の条件を変えることで、調査に適した情報を得ることができます。

同一待機イベントで30秒以上待機し続けているACTIVEセッションを確認(V$SESSION)

処理長時間化の原因がロックやラッチでの競合に起因している可能性がある場合には、次のSQLで調査対象のセッションを特定します。

SELECT
     snap_date                                              -- 情報取得時刻
    ,sid                                                    -- serial#と合わせてセッションを一意に判別
    ,serial#                                                -- sidと合わせてセッションを一意に判別
    ,seq#                                                   -- 待機ごとに増分される値(ハングの判断)
    ,program                                                -- 接続元プログラム名
    ,osuser                                                 -- 接続元OSユーザ名
    ,machine                                                -- 接続元マシン名
    ,event                                                  -- 待機イベント
    ,state                                                  -- 待機状態:WAITING = 待機中
    ,status                                                 -- セッションの状態:ACTIVE=処理を実行中
    ,sql_id                                                 -- SQL識別子:V$SQLでSQL文を確認
    ,round(wait_time_micro/1000000,0) as wait_time_sec      -- STATE=WAITINGの場合、待機時間
    ,row_wait_obj#                                          -- 行ロック競合の場合、対象オブジェクトID
    ,blocking_session                                       -- ロック競合の場合、ブロッカーのSID
    ,final_blocking_session                                 -- 最終ブロッカー(ロック保持者)のSID
FROM session_20161116                                       -- 定期取得したV$SESSIONをロードした表
WHERE to_date (snap_date, 'YYYY-MM-DD hh24:mi:ss')
    BETWEEN  to_date ('2016-11-16 14:30:00', 'YYYY-MM-DD hh24:mi:ss')  -- 調査対象時刻:開始
    AND to_date ('2016-11-16 15:00:00', 'YYYY-MM-DD hh24:mi:ss')       -- 調査対象時刻:終了
AND USERNAME = 'APPUSER'                                    -- 調査対象DBユーザ名
AND wait_time_micro >= 30000000                             -- 30秒以上
ORDER BY snap_date,sid;

PGAを100MB以上獲得しているプロセスを確認(V$PROCESS)

V$PROCESS.ADDRはV$SESSION.PADDRと同じ値です。プロセスが特定できたらV$SESSIONをロードした表からWHERE PADDR=<V$PROCESS.ADDR>で確認を行い、該当セッションがどのクライアントから接続されて何の処理を実行していたのか確認します。

SELECT 
    snap_date                                 
    ,addr                                                          -- V$SESSION.PADDRと同値
    ,program                                                       -- プログラム名
    ,pga_alloc_mem/1024/1024 AS pga_alloc_MB                       -- プロセスに割当てられた現在のPGA(MB)
FROM process_20161116
WHERE to_date (sys_date, 'YYYY-MM-DD hh24:mi:ss')
    BETWEEN  to_date ('2016-11-16 14:30:00', 'YYYY-MM-DD hh24:mi:ss')   -- 調査対象時刻:開始
    AND to_date ('2016-11-16 15:00:00', 'YYYY-MM-DD hh24:mi:ss')        -- 調査対象時刻:終了
AND pga_alloc_mem >= 104857600                                     -- PGA割り当てが100MB以上
ORDER BY snap_date;

いつからロックを掛けていたのかを確認(V$LOCK)

LMODE列に値がある場合はそのロックを獲得してからCTIMEの秒数が経過しています。V$LOCK.TYPEがTM(表ロック)の場合、ID1はDBA_OBJECTS.OBJECT_IDと同じ値です。

WHERE OBJECT_ID = <V$LOCK.ID1>でDBA_OBJECTSを検索することで、SNAP_TIME - CTIME(秒)から対象のオブジェクトにロックをかけていたことがわかります。

SELECT
    snap_date
    ,sid                                          -- SID番号
    ,type                                         -- ロックの種類: TM(表)、TX(行)
    ,id1                                          -- ロック識別子1(意味はロックの種類で異なる)
    ,id2                                          -- ロック識別子2(意味はロックの種類で異なる)
    ,lmode                                        -- ロックの保持モード
    ,request                                      -- ロックの要求モード
    ,ctime                                        -- ロックの保持時間/待機時間
FROM lock_20161116                                -- V$LOCKをロードした表
WHERE to_date (snap_date, 'YYYY-MM-DD hh24:mi:ss')
    BETWEEN  to_date ('2016-11-16 14:30:00', 'YYYY-MM-DD hh24:mi:ss')   -- 調査対象時刻:開始
    AND to_date ('2016-11-16 15:00:00', 'YYYY-MM-DD hh24:mi:ss')        -- 調査対象時刻:終了
AND SID = 260                                     -- SIDがわかる場合は追加
AND type in ('TM','TX')                           -- 表ロックはTM、行ロックはTX
ORDER BY snap_date;

まとめ

Diagnostics Packのライセンスをお持ちの環境では、AWRやASHだけでなくパフォーマンス問題を発見/解決するための様々な強力な機能を利用することができますので、こちらをご利用いただくのがベストです。

Diagnostics Packの機能を利用いただけない環境の場合には、問題発生後に「何が起きていたのかまったくわからない」ということにもなりかねないため、今回紹介しているような方法で情報を取得しておきましょう。

また、V$ビューだけでなくOS側からもWindowsであればパフォーマンスモニタやpslist、Linux環境であればtopやpsなどを利用して情報を取得しておきましょう。

筆者情報

大野 高志

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

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


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

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

関連している記事

  • Oracle Cloud
  • Oracle Database
2024.02.02

OCIにおけるOracle Database 11g R2、12g R1、12g R2の新規プロビジョニング終了とその影響

Oracle Databaseのバージョン11g R2、12g.R1、12g.R2は既にすべてのメーカーサポートが終了しています。OCIのBase Database Serviceでも2024年1月中旬ころから11g R2、12g R1、12g R2での新規プロビジョニングができなくなりました。

  • Oracle Database
  • その他
2023.12.21

【Oracle Database】サポートセンターでの生成AI(Glean)活用

アシストでは全社員にAIアシスタントGleanを導入しました。サポートセンターで2ヶ月間使ってみて感じた効果やメリットをお伝えします。

  • Oracle Database
  • Oracle Cloud
2023.12.15

ライセンスの観点から考えるOracle Cloudのススメ

オラクル社が提供しているクラウドサービス「Oracle Cloud」は、Oracle Databaseライセンス観点でも様々な効果があることはご存じでしょうか? ここでは「ライセンス」に焦点をあて、Oracle Cloudがおススメできるポイントを説明します。

ページの先頭へ戻る