• Oracle Database
• 2013.12.13
• 渡部 亮太

LatchProf (latchprof.sql) from TPT Scripts : Show which latch occupies a given memory address and its stats

このエントリは (全部俺) Oracle ACE Director Tanel Poder Advent Calendar 2013 13日目の記事です。

Advanced Oracle Troubleshooting Guide, Part 7：LatchProfを使用してラッチホルダの統計情報をサンプリング で使われていたLatchProf latchprof.sqlについて書きます。

LatchProf は V$LATCHHOLDERを非常に高い頻度でサンプリングし、セッション単位でラッチ統計情報をレポートしてくれるスクリプトです。典型的な使い方や機能については、 Advanced Oracle Troubleshooting Guide, Part 7：LatchProfを使用してラッチホルダの統計情報をサンプリングで説明されていますので、内部実装の観点で気になったところをメモ書きしておきます。

スクリプトの構造

LatchProfは、V$ビューを非常に高い頻度でサンプリングして統計情報のサマリをレポートしてくれるという点で、WaitProfに似ています。 スクリプトの構造という観点でもよく似ています。

以下にスクリプトのSELECT部分を抜粋します。

WITH 
    t1 AS (SELECT hsecs FROM v$timer),
    samples AS (
        SELECT /*+ ORDERED USE_NL(l) USE_NL(s) USE_NL(l.gv$latchholder.x$ksuprlat) NO_TRANSFORM_DISTINCT_AGG  */
          :
        FROM 
            (SELECT /*+ NO_MERGE */ 1 FROM DUAL CONNECT BY LEVEL <= &_lhp_samples) s, <== (*1)
            v$latchholder l, <== (*2)
            (SELECT
                :
             FROM v$session) s
        WHERE
          :
    ),
    t2 AS (SELECT hsecs FROM v$timer)
SELECT /*+ ORDERED */
    :
  FROM
    t1,
    samples s,
    t2
    :

WaitProfと同様に、以下の構造で処理を実装しています。

• サンプル数と等しい行を含む結果セット(*1)と、統計情報を取得したいV$ビュー(*2)をネステッドループ結合させ、繰り返しV$ビューより統計情報を取得する処理を実現
• 上記統計情報取得処理の前後をV$TIMERに対するSELECTで挟み、取得直前と直後の時間情報を得る。

バージョンに応じてクエリを変更する仕組み

V$ビューはOracle Databaseのバージョンにより、含まれる列が異なる場合がある点に注意が必要です。 今回LatchProfがアクセスするv$latchholderでは、Oracle Database 10.1 以降でのみ含まれる列があるため、Oracle Database 9.2以前と 10.1以降でクエリを修正しています。

クエリの修正には _IF_ORA_10_OR_HIGHER というSQL*plusの置換変数を使用しています(*1)。実行時にクエリの一部を参照させたくない場合は、_IF_ORA_10_OR_HIGHERに"--"という値を設定し、コメントアウト扱いにすることで、実現しています(*2)。逆に 参照させたい場合は _IF_ORA_10_OR_HIGHERに""(空文字)を設定します。

なお、今回は、Oracle Database 9.2以前と 10.1以降で実行されるクエリを修正したいので、 Oracle Database 9.2以前では、_IF_ORA_10_OR_HIGHERに"--"という値を設定し、Oracle Database 10.1以降でないと正常に実行できない箇所をコメントアウトします。逆にOracle Database 10.1以降では_IF_ORA_10_OR_HIGHERに""(空文字)を設定します。

DEF _IF_ORA_10_OR_HIGHER="--" <==(*1)

PROMPT
PROMPT -- LatchProf 2.00 by Tanel Poder ( http://www.tanelpoder.com )

COL latchprof_oraversion NOPRINT NEW_VALUE _IF_ORA_10_OR_HIGHER <== (*3)

SET TERMOUT OFF
SELECT DECODE(SUBSTR(BANNER, INSTR(BANNER, 'Release ')+8,1), 1, '', '--') latchprof_oraversion <== (*4) 
FROM v$version WHERE ROWNUM=1; <== (*4)
SET TERMOUT ON

WITH 
    t1 AS (SELECT hsecs FROM v$timer),
    samples AS (
        SELECT /*+ ORDERED USE_NL(l) USE_NL(s) USE_NL(l.gv$latchholder.x$ksuprlat) NO_TRANSFORM_DISTINCT_AGG  */
            &_lhp_what
          &_IF_ORA_10_OR_HIGHER , COUNT(DISTINCT gets)      dist_samples <==(*2)
          , COUNT(*)                    total_samples
          , COUNT(*) / &_lhp_samples    total_samples_pct
        FROM 
            (SELECT /*+ NO_MERGE */ 1 FROM DUAL CONNECT BY LEVEL <= &_lhp_samples) s,

Oracle Databaseのバージョンに応じて、_IF_ORA_10_OR_HIGHERの値を設定しているのが(*3),(*4)の箇所です。

(*3)の箇所で、latchprof_oraversionという名称の列の値を _IF_ORA_10_OR_HIGHERに設定するように宣言したうえで、(*4)ではv$versionのBANNER列をDECODEなどで評価し、latchprof_oraversionに''または'--'を返すことで実現しています。

SQL> select * from v$version;

BANNER
----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

V$VERSIONのBANNER列の1行目にはOracle Databaseのバージョン情報が表示されます。 そして"Release "という文字列の直後にバージョン番号が表示されますので、バージョン番号の1文字目が1の場合はlatchprof_oraversion、すなわち_IF_ORA_10_OR_HIGHERを''に、そうでない場合は'--'を設定しています。

ここで説明した、SQL*Plusの置換変数を使用して、バージョンに応じてスクリプトの一部をコメントアウトする仕組みは、TPT Scriptsの他のスクリプトでもよく使用されているテクニックです。