Oracle 運用術 : 運用監視でよく使う小ネタ SQL

前回ご紹介した Oracle 運用術でご紹介した ora_perf_report.sql でチューニング情報はだいたい取得可能なのですが、それ以外でよく使っている小ネタ SQL も紹介したいと思います。紹介する SQL の多くはネット上で探せば同じような用途の SQL はすぐに出てくると思いますが、いちいち探すのも面倒だし、どれが使える SQL かも検証が面倒だと思います。ってことで小ネタ SQL まとめです。

よく使う小技 SQL をファイルにして @ファイル名 で実行するスタイルが定着すると、障害時やチューニング時に迅速に現状を把握することが可能となります。一方で急激に手打ちできなくなってしまってこねた SQL ファイルが存在しない環境では何もできなくなる恐れもあります。コレやばい。

- スポンサーリンク -

ですがそんなのんきなことは言ってられない場合が多いので、僕が触る Oracle 環境にはインストールと同時に小ネタ SQL ファイルも設置しています。


ロックがかかった場合に現状把握する SQL

show_lock.sql

実行結果サンプル

SQL> @show_lock.sql

BLOCK/REQ |USERNAME  |SID/SERIAL |OSUSER |MACHINE |TERMINAL |PROGRAM                   |STATUS  |TYPE|CTIME |HELD |REQUESTED|OWNER |OBJECT_NAME |SQL_TEXT
----------|----------|-----------|-------|--------|---------|--------------------------|--------|----|------|-----|---------|------|------------|------------------------------------------
BLOCK     |USR       |126,12639  |apache |dev01   |pts/3    |sqlplus@dev01 (TNS V1-V3) |INACTIVE|TX  | 420  |排他 |NONE     |TEST  |TBL01       |
REQ       |USR       |142,15720  |apache |dev01   |pts/2    |sqlplus@deb01 (TNS V1-V3) |ACTIVE  |TX  | 417  |NONE |排他     |TEST  |TBL01       |update tbl01 set point=300 where id='aaaa'


全セッションの PGA / UGA を現状把握する SQL

show_pga.sql

実行結果サンプル

SQL> @show_pga.sql

SID |USERNAME |STATUS  |現PGA使用量(KB)  |最大PGA使用量(KB)|現UGA使用量(KB)  |最大UGA使用量(KB)
----|---------|--------|-----------------|-----------------|-----------------|-----------------
136 |USR      |INACTIVE|       803.585938|       931.585938|       289.710938|       601.953125
139 |USR      |INACTIVE|       483.585938|       483.585938|       154.648438|       154.648438
143 |USR      |INACTIVE|       1059.58594|       2275.58594|       346.273438|       1446.27344
154 |USR      |INACTIVE|       867.585938|       931.585938|       282.398438|       410.148438
135 |SYS      |ACTIVE  |       5458.92969|       10275.5859|       1829.34375|       8777.77344
142 |         |ACTIVE  |       547.585938|       1699.58594|       154.648438|       154.648438
145 |         |ACTIVE  |       483.585938|       483.585938|       154.648438|       154.648438


全セッションの詳細を取得する SQL

show_sess_detail.sql

実行結果サンプル

SQL> @show_sess_detail.sql

【 セッションの確認 】

SID/SERIAL     |OSUSER    |MACHINE        |TERMINAL  |PROGRAM                            |TYPE      |USERNAME  |STATUS  |COMMAND  |経過時間(sec)|CPU使用時間(sec)|CPU占有率(%)|ブロック取得回数|物理読み込み回数
---------------|----------|---------------|----------|-----------------------------------|----------|----------|--------|---------|-------------|----------------|------------|----------------|----------------
160,1          |oraclei   |dev01          |UNKNOWN   |oracle@dev01       (MMNL)          |BACKGROUND|          |ACTIVE  |待機中   |      5364656|           0.000|       0.000|          17,598|              63
160,1          |oraclei   |dev01          |UNKNOWN   |oracle@dev01       (MMNL)          |BACKGROUND|          |ACTIVE  |待機中   |      5364656|           0.350|       0.000|          17,598|              63
160,1          |oraclei   |dev01          |UNKNOWN   |oracle@dev01       (MMNL)          |BACKGROUND|          |ACTIVE  |待機中   |      5364656|           0.000|       0.000|          17,598|              63
160,1          |oraclei   |dev01          |UNKNOWN   |oracle@dev01       (MMNL)          |BACKGROUND|          |ACTIVE  |待機中   |      5364656|           0.000|       0.000|          17,598|              63
161,1          |oraclei   |dev01          |UNKNOWN   |oracle@dev01       (MMON)          |BACKGROUND|          |ACTIVE  |待機中   |      5364656|           0.000|       0.000|         913,259|          35,320
161,1          |oraclei   |dev01          |UNKNOWN   |oracle@dev01       (MMON)          |BACKGROUND|          |ACTIVE  |待機中   |      5364656|         426.560|       0.008|         913,259|          35,320
161,1          |oraclei   |dev01          |UNKNOWN   |oracle@dev01       (MMON)          |BACKGROUND|          |ACTIVE  |待機中   |      5364656|           0.000|       0.000|         913,259|          35,320
161,1          |oraclei   |dev01          |UNKNOWN   |oracle@dev01       (MMON)          |BACKGROUND|          |ACTIVE  |待機中   |      5364656|           0.000|       0.000|         913,259|          35,320
162,1          |oraclei   |dev01          |UNKNOWN   |oracle@dev01       (CJQ0)          |BACKGROUND|          |ACTIVE  |待機中   |      5364656|           0.000|       0.000|           1,657|           5,137
162,1          |oraclei   |dev01          |UNKNOWN   |oracle@dev01       (CJQ0)          |BACKGROUND|          |ACTIVE  |待機中   |      5364656|          80.480|       0.002|           1,657|           5,137
162,1          |oraclei   |dev01          |UNKNOWN   |oracle@dev01       (CJQ0)          |BACKGROUND|          |ACTIVE  |待機中   |      5364656|           0.000|       0.000|           1,657|           5,137


現在実行中の SQL 一覧を取得する SQL

show_runsql.sql

実行結果サンプル

SQL> @show_runsql.sql     

LOGON_TI|LOGON_USER | SID| SERIAL|STATUS  |SQL_TEXT
--------|-----------|----|-------|--------|----------------------------------------------------------------------------------------------------
20:34:18|SYS        | 135|  44316|ACTIVE  |SELECT TO_CHAR(SES.LOGON_TIME, 'HH24:MI:SS') AS LOGON_TIME,        SES.USERNAME    AS LOGON_USER,
        |           |    |       |        |     SES.SID         AS SID,        SES.SERIAL#     AS SERIAL,        SES.STATUS      AS STATUS,
        |           |    |       |        |    SQL.SQL_TEXT    AS SQL_TEXT FROM   V$SESSION SES, V$SQLAREA SQL WHERE  SES.SQL_ADDRESS = SQL.ADD
        |           |    |       |        |RESS(+) AND        SES.SQL_HASH_VALUE = SQL.HASH_VALUE AND        SES.TYPE = 'USER' ORDER BY SES.LOG
        |           |    |       |        |ON_TIME


そのシステムで推奨される SHARED_POOL_SIZE を計算する SQL

show_shared_pool_size.sql

実行結果サンプル

SQL> @show_shared_pool_size.sql

現在最低限必要なSHARED_POOL_SIZE->15077821
適正なSHARED_POOL_SIZE(×1.3)->19601167.3


ロールバックセグメント領域を現状把握する SQL

show_rbs.sql

※Oracle 8i でのみ意味がある結果がでます。9i 以降は UNDO 領域になったので実行しても意味なし。
実行結果サンプル(※Oracle 10g上で実行した結果なので意味無しですが・・・)

SQL> @show_rbs.sql

          |     |       |       |   |      |        |        |        |    |   E|   S|       |          |      |            |
          |     |       |       |   |      |        |        |        |    |   X|   H|       |          |      |            |
          |     |       |       |   |      |        |        |        |   W|   T|   R|       |          |      |       Total|    AVG
          |     |       |       |   |      |        |        |    High|   R|   E|   I|       |          |      |      Writes| Writes
          |     |   Init|   Next|   |      | Optimal|    Curr|   Water|   A|   N|   N|    AVG|          |      |       Since|    Per
          | Actv|    Ext|    Ext|Min|   Max|    Size|    Size|    Mark|   P|   D|   K| Shrink|    Header|Header|     Startup| HedGet
NAME      |Trans|   (Mb)|   (Mb)|Ext|   Ext|    (Mb)|    (Mb)|    (Mb)|   S|   S|   S|   (Mb)|      Gets| Waits|        (Kb)|(bytes)
----------|-----|-------|-------|---|------|--------|--------|--------|----|----|----|-------|----------|------|------------|-------
SYSTEM    |    0|   0.11|       |  1| 32765|        |    0.37|    0.37|   0|   0|   0|   0.00|     17231|     0|           5|      0
_SYSSMU1$ |    0|   0.13|       |  2| 32765|        |    0.12|    8.18|####| 464| 153|   0.89|    280551|    60|     208,540|    761
_SYSSMU10$|    0|   0.13|       |  2| 32765|        |    1.12|    7.99|####| 423| 144|   0.68|    275181|    51|     171,326|    638
_SYSSMU2$ |    0|   0.13|       |  2| 32765|        |    1.12|    7.18| 711| 239|  84|   1.21|    280299|    12|     186,153|    680
_SYSSMU3$ |    0|   0.13|       |  2| 32765|        |    1.12|    9.12| 650| 192|  85|   1.37|    278331|    21|     200,589|    738
_SYSSMU3$ |    0|   0.13|       |  2| 32765|        |    1.12|    8.12| 871| 296| 108|   0.95|    277556|    30|     184,683|    681
_SYSSMU5$ |    0|   0.13|       |  2| 32765|        |    1.12|   17.12| 927| 333| 120|   1.06|    277969|    43|     202,856|    747
_SYSSMU6$ |    0|   0.13|       |  2| 32765|        |    1.12|    9.12| 622| 193|  80|   1.31|    276058|    19|     185,311|    687
_SYSSMU7$ |    0|   0.13|       |  2| 32765|        |    0.18|    8.12| 808| 306|  99|   1.36|    281473|    25|     216,337|    787
_SYSSMU8$ |    0|   0.13|       |  2| 32765|        |    1.12|    8.12| 754| 243|  89|   1.15|    277859|    24|     193,511|    713
_SYSSMU9$ |    0|   0.13|       |  2| 32765|        |    1.12|    8.12|####| 392| 139|   0.87|    279755|    58|     203,350|    744
          |     |       |       |   |      |        |        |        |    |   E|   S|       |          |      |            |
          |     |       |       |   |      |        |        |        |    |   X|   H|       |          |      |            |
          |     |       |       |   |      |        |        |        |   W|   T|   R|       |          |      |       Total|    AVG
          |     |       |       |   |      |        |        |    High|   R|   E|   I|       |          |      |      Writes| Writes
          |     |   Init|   Next|   |      | Optimal|    Curr|   Water|   A|   N|   N|    AVG|          |      |       Since|    Per
          | Actv|    Ext|    Ext|Min|   Max|    Size|    Size|    Mark|   P|   D|   K| Shrink|    Header|Header|     Startup| HedGet
NAME      |Trans|   (Mb)|   (Mb)|Ext|   Ext|    (Mb)|    (Mb)|    (Mb)|   S|   S|   S|   (Mb)|      Gets| Waits|        (Kb)|(bytes)
----------|-----|-------|-------|---|------|--------|--------|--------|----|----|----|-------|----------|------|------------|-------
          |     |       |       |   |      |        |        |        |    |    |    |-------|----------|------|            |-------
avg       |     |       |       |   |      |        |        |        |    |    |    |   0.99|          |      |            |    653
sum       |     |       |       |   |      |        |        |        |    |    |    |       |   2802263|   343|            |
- スポンサーリンク -