Oracle10g 動的ビューV$SESSIONのおさらいの巻

＜Oracle10g 動的ビューV$SESSIONのおさらいの巻＞
ペンネーム：アイスケーキ

ＧＷも終わってホット一息。。。でも安心は禁物、気がつけば五月病。
今年はＧＷが長かったので、そろそろ反動が来そうな予感がありますが頑張っ
ていきましょう。

今週は「動的ビューV$SESSION」についておさらいをしていきます。

★☆★
Oracle 10gよりV$SESSION動的ビューに
BLOCKING_SESSION, BLOCKING_SESSION_STATUSの2つの列が追加されていてロッ
クされているオブジェクトの確認を行うことが可能となっています。場合によ
っては以前ようにX$BHから追いかける手間もなく、とても楽です。

取得可能ものは
○ブロックしている／されているユーザ名
○ブロックしている／されているユーザのWAITイベント名
○ブロックされているオブジェクトの番号
○ブロックされているオブジェクトが属するファイル番号
○ブロックされているオブジェクトのブロック番号
○ブロックされているオブジェクトの行番号
等です。

v$session に追加された列の詳細は、マニュアル
「リファレンスリリース1(10.x)」を参照下さい。

☆★☆★☆★
早速みてみましょう。

◆環境
Microsoft Windows XP Pro
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 – Production
With the Partitioning, OLAP and Data Mining options

◆設定
最初にテスト用のワークテーブルを作成します。
ブロックしているユーザ(USER1)とブロックされているユーザ(USER2)のWAIT
EVENTを参照します。

「USER1」

SQL> create table wk_tbl(item1 number,item2 number);
     表が作成されました。
SQL> insert into wk_tbl values(100,100);
     1行が作成されました。
SQL> insert into wk_tbl values(200,200);
     1行が作成されました。
SQL> commit;
     コミットが完了しました。
SQL> update user1.wk_tbl set item2=300;
     2行が更新されました。

「USER2」

SQL> update user1.wk_tbl set item2=400;

◆確認
○ブロックしている／されているユーザのWAITイベント名を参照
「other」

SQL> select
         x.username "Blocking User",
         x.event "Wait Event",
         y.username "Blocked User",
         y.event "Wait Event"
        from v$session x, v$session y
        where y.blocking_session=x.sid
        and   y.blocking_session_status='VALID';

Blocking User Wait Event
------------- ------------------------------
USER1         SQL*Net message from client

Blocked User  Wait Event
------------- ------------------------------
USER2         enq: TX - row lock contention

USER2がTXロックイベントで待たされているのが分かります。

○ブロックされているオブジェクトについて参照

SQL> select
  2            x.username "Blocking User",
  3            y.username "Blocked User",
  4            y.row_wait_obj# "Object#",
  5            y.row_wait_file# "File#",
  6            y.row_wait_block# "Block#",
  7            y.row_wait_row# "Row#"
  8           from v$session x, v$session y
  9           where y.blocking_session=x.sid;

Blocking User Blocked User    Object#      File#     Block#       Row#
------------- ------------ ---------- ---------- ---------- ----------
USER1         USER2             47114          5         10         0

USER2の待たされているオブジェクトID、ファイルID、ブロックIDが分かります。

○オブジェクト名を参照するには

SQL> select OWNER,OBJECT_NAME from dba_objects where OBJECT_ID = 47114;

OWNER     OBJECT_NAME
--------- -----------
USER1     WK_TBL

○ファイル関連情報を参照するには

SQL> select TABLESPACE_NAME,BYTES,FILE_NAME
 2   from dba_data_files where FILE_ID = 5;

TABLESPACE_NAME                 BYTES
------------------------------ ----------
USERS_CH                        10485760

FILE_NAME
----------------------------------------------------------------
C:ORACLEPRODUCT10.1.0ORADATAORCLDATAFILEO1_MF_USERS_1.DBF

○エクステント関連を参照するには

SQL> SELECT OWNER,SEGMENT_NAME,EXTENT_ID FROM dba_extents
 2   WHERE FILE_ID = 5 AND block_id = 10;

OWNER         SEGMENT_NAME  EXTENT_ID
------------- ------------- ---------
USER1         WK_TBL           0

○レコード位置を参照するには

SQL> select rowid,item1,(row_number() over (order by rowid))-1 "Row"
 2   from user1.wk_tbl;

ROWID                   ITEM1       Row
------------------ ---------- ----------
AAALgLAAFAAAAAKAAA        100          0 <<<
AAALgLAAFAAAAAKAAB        200          1

■おさらい
Oracle 10gよりV$SESSION動的ビューに追加されたブロック元・先の関連情報
が追加されています。
結構つかえるので、10gユーザとなった際は使ってみてください。

今週はここまで。

初夏の茅ヶ崎より

Oracle10g 動的ビューV$SESSIONのおさらい の巻

Oracle10g 動的ビューV$SESSIONのおさらいの巻