Oracle のセッション ID と OSPID を紐づける方法

Oracle の v$session で出力される情報は SID(セッション ID)をベースとしてます。ただし、各プロセスの CPU 使用率を追う場合には、SID と OSPID を紐づける必要があります。

紐づけたい場合は以下の SQL で可能です。SPID 列が OSPID に相当します。

select sid, pid, spid, s.username, osuser, s.program 
  from v$process p, v$session s 
 where p.addr=s.paddr;

output

 SID PID SPID  USERNAME OSUSER   PROGRAM
---- --- ----- -------- -------- -------------------------------
   2   2 5537           ora11204 oracle@ol66.local (PMON)
   3   3 5539           ora11204 oracle@ol66.local (PSP0)
   4   4 5541           ora11204 oracle@ol66.local (VKTM)
   5   5 5545           ora11204 oracle@ol66.local (GEN0)
   6   6 5547           ora11204 oracle@ol66.local (DIAG)
   7   7 5549           ora11204 oracle@ol66.local (DBRM)
   8   8 5551           ora11204 oracle@ol66.local (DIA0)
   9   9 5553           ora11204 oracle@ol66.local (MMAN)
  10  10 5555           ora11204 oracle@ol66.local (DBW0)
  11  11 5557           ora11204 oracle@ol66.local (LGWR)
  12  12 5559           ora11204 oracle@ol66.local (CKPT)
  13  13 5561           ora11204 oracle@ol66.local (SMON)
  14  14 5563           ora11204 oracle@ol66.local (RECO)
  15  15 5565           ora11204 oracle@ol66.local (MMON)
  16  16 5567           ora11204 oracle@ol66.local (MMNL)
  29  19 13195          ora11204 oracle@ol66.local (Q002)
  18  20 5579           ora11204 oracle@ol66.local (ARC0)
  20  21 5581           ora11204 oracle@ol66.local (ARC1)
  21  22 5583           ora11204 oracle@ol66.local (ARC2)
  23  23 5585           ora11204 oracle@ol66.local (ARC3)
  28  24 5587           ora11204 oracle@ol66.local (QMNC)
  38  25 5648           ora11204 oracle@ol66.local (SMCO)
  50  26 13986 SYS      ora11204 sqlplus@ol66.local (TNS V1-V3)
  45  28 13715          ora11204 oracle@ol66.local (W001)
  37  30 5613           ora11204 oracle@ol66.local (Q001)
  31  31 6002           ora11204 oracle@ol66.local (CJQ0)

念のため正しいことを確認。

$ ps -ef | grep 5537 | grep -v grep
ora11204  5537     1  0 00:01 ?        00:00:05 ora_pmon_orcl

セッション ID の確認

また、接続しているセッションの SID を確認した場合は以下で可能です。

select distinct sid from v$mystat;

output

       SID
----------
        50

深堀

取得する機会は少ないかもしれませんが、system state を取得することでも SID と OSPID を紐づけることが可能です。

systemstate

SQL> alter session set events 'immediate trace name systemstate level 266';

orcl_ora_14000.trc

  :
PROCESS 2: PMON
  ----------------------------------------
  SO: 0x784a6868, type: 2, owner: (nil), flag: INIT/-/-/0x00 if: 0x3 c: 0x3
   proc=0x784a6868, name=process, file=ksu.h LINE:12721, pg=0
  (process) Oracle pid:2, ser:1, calls cur/top: 0x780c5818/0x780c5818
            flags : (0xe) SYSTEM
            flags2: (0x0),  flags3: (0x10)
            intr error: 0, call error: 0, sess error: 0, txn error 0
            intr queue: empty
    ksudlp FALSE at location: 0
  (post info) last post received: 0 0 281
              last post received-location: kmmcts.h LINE:2898 ID:kmmreg()
              last process to post me: 0x784b84a0 53 2
              last post sent: 0 0 26
              last post sent-location: ksa2.h LINE:285 ID:ksasnd
              last process posted by me: 0x784a7920 1 6
    (latch info) wait_event=0 bits=0x0
    Process Group: DEFAULT, pseudo proc: 0x7854deb0
    O/S info: user: ora11204, term: UNKNOWN, ospid: 5537 <--★ OSPID=5537
    OSD pid info: Unix process pid: 5537, image: oracle@ol66.local (PMON)
  :
    SO: 0x78080600, type: 4, owner: 0x784a6868, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
     proc=0x784a6868, name=session, file=ksu.h LINE:12729, pg=0
    (session) sid: 2 ser: 1 trans: (nil), creator: 0x784a6868 <--★ SID=2
              flags: (0x51) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
              flags2: (0x409) -/-/INC
              DID: 0002-, short-term DID:
              txn branch: (nil)
              edition#: 0              oct: 0, prv: 0, sql: (nil), psql: (nil), user: 0/SYS
    ksuxds FALSE at location: 0
    service name: SYS$BACKGROUND
  :