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
: