ロックをつぶせ！最初に疑うべき原因：Oracleパフォーマンス障害の克服（2）（3/3 ページ）

» 2004年07月28日 00時00分公開

V$SQLAREA

　次は「どのようなSQL文が問題となっているのか」を確認してみましょう。つまりロックをかけている原因となっているSQL文です。このV$SQLAREA動的パフォーマンスビューもかなりの情報を保持しています。しかし、ここで知りたい情報は問題となっているSQL文の内容ですので、SQL_TEXT列の説明とします。

SQL> DESC V$SQLAREA

V$SQLAREA動的パフォーマンスビューの概要を取得する
（内容の抜粋は下記の表4参照）


列名	データ型	格納されているデータの内容
SQL_TEXT	VARCHAR2(1000)	現在カーソル内に保持されているSQL文の内容
ADDRESS	RAW(4)	本カーソルの親に対するハンドルのアドレス。 V$SESSION動的パフォーマンスビューのSQL_ADDRESS列に対応
表4　V$SQLAREA動的パフォーマンスビュー（抜粋）

問題となっているSQLを特定するSQL文

SQL> SELECT A.SQL_TEXT,A.ADDRESS FROM V$SQLAREA A,V$SESSION B WHERE A.ADDRESS = B.SQL_ADDRESS AND B.SID = ANY(SELECT SID FROM V$LOCK WHERE TYPE IN ('TX','TM'));

図3　V$SQLAREAとV$SESSIONから、カーソル内のSQL文と、カーソルの親ハンドルのアドレスを取得する（クリックで拡大します）

V$LOCKED_OBJECT

　さあ問題となっているユーザー、プログラム、SQL文が分かってしまえば、それぞれ処理要求を出しているプログラムなどに適宜修正を行うことは容易でしょう。しかし、ロックに対するオブジェクトまで確定できていれば、SQL文の修正にさらに役に立つのではないでしょうか。「どのオブジェクトがロックされているのか」「オブジェクトに対するロックをかけているのはどのプログラムなのか」まで踏み込んで確認していきましょう。V$LOCKED_OBJECT動的パフォーマンスビューで確認したオブジェクトIDを利用して、DBA_OBJECTSというデータディクショナリビュー^注2でその名前を解決します。V$LOCKED_OBJECT^注3ではロックの対象となっているオブジェクトのみが格納されています。

注2：データディクショナリビュー
サーバが保有しているオブジェクトに対する定義などの情報を保持する表に対するアクセスを可能にするビュー。

注3：V$LOCKED_OBJECTのサンプルコード
V$LOCKED_OBJECTに対する情報取得のサンプルコードがOracleTechnology Network（OTN）で公開されていますので、確認してみてください。

SQL> DESC V$LOCKED_OBJECT

V$SQLAREA動的パフォーマンスビューの概要を取得する
（内容の抜粋は下記の表5参照）


列名	データ型	格納されているデータの内容
OBJECT_ID	NUMBER	ロックされているオブジェクトID
SESSION_ID	NUMBER	ロックしているセッションID
ORACLE_USERNAME	VARCHAR2(30)	ロックしているオラクユーザー名
LOCKED_MODE	NUMBER	ロックモード
表5　V$LOCKED_OBJECT動的パフォーマンスビュー（抜粋）

ロックしている、されているオブジェクトを特定するSQL文

SQL> SELECT B.OBJECT_NAME,A.ORACLE_USERNAME FROM V$LOCKED_OBJECT A,DBA_OBJECTS B WHERE A.OBJECT_ID = B.OBJECT_ID;

図4　V$LOCKED_OBJECTとDBA_OBJECTSからOBJECT_NAME（ロックされているオブジェクトID）、ORACLE_USERNAME（ロックしているユーザー名）を取得する（クリックで拡大します）

実践で使用できるサンプルSQL文

　以上でオブジェクトのロック関連の確認は終了です。各動的パフォーマンスビューの意味や役割を理解するために詳細に説明してきましたが、それぞれの役割が分かっていれば現場での確認も迅速に行えるでしょう。

　最後にこれまで説明してきた内容を基に作成した、現場で使いやすいSQL文を実行し、再度確認していきましょう。

SQL1
ロックをかけているセッションID、ユーザー名、プログラム名、ロックしている時間を取得

SQL> SELECT a.SID sid,
             a.USERNAME username,
             a.SERIAL# serialno,
             b.TYPE type, 
            a.PROGRAM program,
             TO_CHAR(b.CTIME/60,'999990.9') lock_time,
             c.SQL_TEXT SQL
      FROM V$SESSION a,
           V$LOCK b,
           V$SQLAREA c
      WHERE a.SID = b.SID
        AND b.TYPE IN ('TX','TM')
        AND a.SQL_ADDRESS = c. ADDRESS;

SQL2
ロックのため待ちが発生しているセッションID、ユーザー名、プログラム名、待たされている時間

SQL> SELECT a.USERNAME username,
             a.PROGRAM program,
             a.SERIAL# serialno,
             a.SID sid,
             b.TYPE type,
             TO_CHAR(b.CTIME/60,'999990.9') lock_time
      FROM V$SESSION a,
           V$LOCK b
      WHERE a.SID = b.SID
        AND b.TYPE = 'TM'
        AND b.SID = (SELECT SID FROM V$LOCK c WHERE c.TYPE = 'TX' AND c.REQUEST > 0);

　今回は動的パフォーマンスビューを使って問題を引き起こしているロックを見つけ出す作業について解説しました。次回はSQLに関する確認の続編として、インデックスを解説します。

「次回」へ

「Oracleパフォーマンス障害の克服」バックナンバー