ホームに戻るDBリンクを経由するSELECT処理でUNDOセグメントが割り当てられてしまう
【Question】
DBリンクを経由してデータをSELECTで参照しています。ところが、SQLを実行するとUNDOセグメントを割り当ててセッションが終了するまでずっと残っています。なぜでしょうか。
【Answer】
これはOracleの内部的な動きです。リモートクエリーの場合、SELECTでも無条件でTXロック獲得するようになっています。
これはSELECT文がローカルで実行中にリモートでTXロックでその状態を保護する仕組みです。
この現象は、セッションの終了やDML実行後にコミットが発生するため、自然となくなり殆どの場合問題にはなりません。
しかし、トランザクションの数が多い状態で無駄なトランザクションを発生させたくない場合は以下の二つの方法で解消することができます。
① SELECT後にコミットを実行する(最も現実的で良く使われる方法です)
②「Read Only Transaction」を使う
以下のテスト例で解消方法を確認してください。
SQL> select count(*) from sales@ora102 where rownum <= 100 ;
COUNT(*)
----------
100
1行が選択されました。
SQL> exec print_table(' -
> select * from v$transaction -
> where addr = ( -
> select taddr from v$session -
> where sid in (select sid from v$mystat))');
ADDR : 333B2B64
XIDUSN : 7 <--- UNDOセグメントが割り当てられている
XIDSLOT : 38
XIDSQN : 2241
UBAFIL : 0
UBABLK : 0
UBASQN : 0
UBAREC : 0
STATUS : ACTIVE
START_TIME : 07/10/09 10:12:50
START_SCNB : 40232216
START_SCNW : 0
START_UEXT : 39
START_UBAFIL : 2
START_UBABLK : 41936
START_UBASQN : 2615
START_UBAREC : 42
SES_ADDR : 3432D86C
FLAG : 4199939
SPACE : NO
RECURSIVE : NO
NOUNDO : NO
PTX : NO
NAME :
PRV_XIDUSN : 0
PRV_XIDSLT : 0
PRV_XIDSQN : 0
PTX_XIDUSN : 0
PTX_XIDSLT : 0
PTX_XIDSQN : 0
DSCN-B : 0
DSCN-W : 0
USED_UBLK : 1
USED_UREC : 1
LOG_IO : 12
PHY_IO : 0
CR_GET : 3
CR_CHANGE : 0
START_DATE : 09-07-10
DSCN_BASE : 0
DSCN_WRAP : 0
START_SCN : 40232216
DEPENDENT_SCN : 0
XID : 07002600C1080000
PRV_XID : 0000000000000000
PTX_XID : 0000000000000000
-----------------
PL/SQLプロシージャが正常に完了しました。
SQL> exec print_table('select * from v$lock
2 where sid in (select sid from v$mystat)');
ADDR : 33834450
KADDR : 33834464
SID : 144
TYPE : DX
ID1 : 10
ID2 : 0
LMODE : 6
REQUEST : 0
CTIME : 0
BLOCK : 0
-----------------
ADDR : 333B2B64
KADDR : 333B2C80
SID : 144
TYPE : TX <--- TXロックで保護されている
ID1 : 458790
ID2 : 2241
LMODE : 6
REQUEST : 0
CTIME : 0
BLOCK : 0
-----------------
PL/SQLプロシージャが正常に完了しました。
SQL>
SQL> commit ;
コミットが完了しました。
SQL>
SQL> select count(*) from v$transaction
2 where addr = ( select taddr from v$session
3 where sid in (select sid from v$mystat)) ;
COUNT(*)
----------
0 <--- コミット後にトランザクションがなくなった
<--- 割り当てられたUNDOセグメントもリリースされた）
1行が選択されました。
SQL> select * from v$lock where sid in (select sid from v$mystat) ;
レコードが選択されませんでした。 <--- コミット後にTXロックもなくなった
SQL> set transaction read only;
トランザクションが設定されました。
SQL> select count(*) from sales@ora102 where rownum <= 100 ;
COUNT(*)
----------
100
1行が選択されました。
SQL> select count(*) from v$transaction
2 where addr = ( select taddr from v$session
3 where sid in (select sid from v$mystat)) ;
COUNT(*)
----------
0 <---トランザクションが作成されない
<---（UNDOセグメントの割り当てが発生しない）
1行が選択されました。
SQL> select * from v$lock where sid in (select sid from v$mystat) ;
レコードが選択されませんでした。 <---TXロックも発せしない
※「print_table」プロシージャは asktom.oracle.com サイトの掲載物を使用しています。
【注意】本記事の検証結果は環境、バージョン毎に異なる可能性がありますので、内容の理解と充分な検証の上自己責任で適用を実施してください。
Oracleの事なら日本エクセムホームへ | 会社情報 | 製品情報 | お問い合わせ | プライバシーポリシー
Copyright (C) EXEM JAPAN Corp. All Rights Reserved.