【Oracle】OSプロセスID(PID)とoracleのSID、SQL_IDの紐付け方法【Linux】

はじめに
TOPコマンドでOSプロセスID(PID)を確認する
1. TOPコマンドの実行
2. TOPコマンドの結果イメージ
SQLでPIDからSIDを特定する
1. SIDを特定するSQLの実行
2. SIDを特定するSQLの結果イメージ
SIDからSQL_IDを特定する
1. SQL_IDを特定するSQLの実行
2. SQL_IDを特定するSQLの結果イメージ
PIDとSIDの関係性
まとめ

はじめに

TOPコマンドで長時間実行されているoracleプロセスを見つけた場合、どのSQLが長時間実行されているか調査することがあります。

TOPコマンドのPIDからSIDを引き当て、SIDからSQL_IDを引き当てる方法を記載します。

TOPコマンドでOSプロセスID(PID)を確認する

TOPコマンドの実行

OSプロセスID(PID)を確認するには以下のTOPコマンドを実行します。

Linux上でoracle実行ユーザーにて実行します。

top | grep ora

ユーザーやコマンドでgrepすると絞り込みが出来ます。

TOPコマンドの結果イメージ

私が個人で運営している販売管理DBのTOPコマンド実行結果(一部モザイク無しにしていますのでご参考までにご参照ください)

画像下部に項目タイトルを追加しています。左から
PID | USER | PR | NI | VIRT | RES | SHR | S | %CPU | %MEM | TIME+ | COMMAND
の順に出力されます。

TIME+が長時間になっているSQLの調査などをする際に本方法にてPIDを特定し、最終的にSQLを特定しています。

SQLでPIDからSIDを特定する

SIDを特定するSQLの実行

sysユーザーにて以下SQLを実行します。

以下SQLの[LinuxのPID]部分へ上記で取得したPIDを設定します。

SET LINESIZE 250

select sid, pid, spid, s.username, osuser, s.program
  from v$process p, v$session s
 where p.addr=s.paddr
   and spid = '[LinuxのPID]' ;

「SET LINESIZE。。。」の部分は、画面いっぱいに表示したTeraTermで実行して丁度良いサイズにしています(お好みでカスタマイズしてください)。

SIDを特定するSQLの結果イメージ

私が個人で運営している販売管理DBのSIDを特定するSQLの実行結果(一部モザイク無しにしていますのでご参考までにご参照ください)

SIDからSQL_IDを特定する

SQL_IDを特定するSQLの実行

sysユーザーにて以下SQLを実行します。

以下SQLの[sid]部分へ上記で取得したSIDを設定します。

SET LINESIZE 250
COLUMN SPID FORMAT A6
COLUMN USERNAME FORMAT A30
COLUMN OSUSER   FORMAT A30
COLUMN MACHINE  FORMAT A30
COLUMN PROGRAM  FORMAT A30

select ses.sid
      ,proc.spid
      ,ses.username
      ,ses.osuser
      ,ses.machine
      ,ses.program
      ,ses.sql_id
  from v$session ses
  left join v$process proc on(ses.paddr = proc.addr)
 where ses.SID='[sid]' ;

「SET LINESIZE。。。」の部分は、画面いっぱいに表示したTeraTermで実行して丁度良いサイズにしています(お好みでカスタマイズしてください)。

SQL_IDを特定するSQLの結果イメージ

私が個人で運営している販売管理DBのSQL_IDを特定するSQLの実行結果(一部モザイク無しにしていますのでご参考までにご参照ください)

一番右側にSQL_IDが出力されています。
SQL_IDはアルファベットと数字の羅列です。

PIDとSIDの関係性

Oracleデータベースにおいて、PID（プロセスID）とSID（セッションIDまたはシステムID）は異なる概念であり、それぞれ異なる役割を果たします。
以下に、OracleのPIDとSIDの関係性について説明します。

PID（プロセスID）:
- PIDは、オペレーティングシステム（OS）でプロセスを一意に識別するために使用される番号です。
- Oracleデータベース内では、各バックグラウンドプロセスやユーザーセッションなど、異なるプロセスが異なるPIDを持ちます。
- 各Oracleプロセス（バックグラウンドプロセス、クライアント接続など）は、OSのプロセスとして実行されます。したがって、PIDはOSレベルの識別子であり、Oracleデータベースの内部で発生するものです。
SID（セッションIDまたはシステムID）:
- SIDはOracleデータベース内でセッションを一意に識別するために使用される番号です。
- セッションは、ユーザーまたはアプリケーションがデータベースに接続し、SQLクエリなどの操作を実行する際に確立されます。
- 各セッションには一意のSIDが割り当てられ、そのセッションがデータベース内で一意に識別されます。SIDはデータベース内でのセッションの識別子です。

PIDとSIDの関係性は次のようになります：

一つのPIDは、一つまたは複数のSID（セッション）をサポートできます。これは、複数のクライアントセッションが同じOracleプロセス内で実行される場合に起こります。この場合、セッションごとにユニークなSIDがありますが、すべてのセッションが同じPIDを共有します。
各セッション（SID）は、データベース内で独自のSQLステートメントを実行するために割り当てられます。セッションごとに異なるデータベースの操作を実行できます。

要するに、PIDはOSレベルでのプロセス識別子であり、SIDはデータベース内でのセッション識別子です。同じプロセス（PID）内で複数のセッション（SID）が実行されることが一般的であり、PIDはセッション管理のために使用される重要な要素の一つです。