株式会社コーソル

コーソルDatabaseエンジニアのブログ

技術ブログ

?.sql from TPT Scripts

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

Tanel Poderさんのエントリを翻訳しようと思ったのですが、翻訳作業に使用しているGoogle翻訳者ツールキットの調子がイマイチだった(今では改善されているかもしれません)ので、翻訳はあきらめて、 引き続きTPT Scriptについて説明したいと思います。

非常に有益なノウハウが詰め込まれたTPT Scriptsですが、非常に多くのスクリプト から構成されており、全てをチェックするのは一般に困難です。

[oracle@l64rw3 sql]$ find -type f -name "*.sql" |wc -l
841

実際、私も全てのスクリプトをチェックできていません・・・ 抜粋してチェックしてゆきたいのですが、 どの観点から絞り込んでゆくか? いくつかの観点が考えられるかと思いますが、いわゆるUNIXの流儀に従うと、常用するコマンドは短い名前のはず!ということで、?.sqlというスクリプトをチェックすることにします。

[oracle@l64rw3 scripts]$ find -type f -name "?.sql" |wc -l
16
[oracle@l64rw3 scripts]$ find -type f -name "?.sql"  |sort
./ash/w.sql
./a.sql
./c.sql
./d.sql
./f.sql
./h.sql
./i.sql
./l.sql
./m.sql
./o.sql
./p.sql
./s.sql
./u.sql
./v.sql
./w.sql
./x.sql
[oracle@l64rw3 scripts]$

16個ならば、なんとかなりそうです。 🙂

a.sql : Display active sessions

http://blog.tanelpoder.com/files/scripts/a.sql

V$SESSIONからアクティブなセッションを表示します。自セッション、バックグラウンドセッションは除外します。

SQL> @a
A-Script: Display active sessions...

  COUNT(*) SQL_ID        STATE   EVENT
---------- ------------- ------- ----------------------------------------------------------------
         1               WAITING target log write size
         1 atcsf1uudcvkd WAITING buffer busy waits
         1 154djmvjawrq3 ON CPU  On CPU / runqueue
         1               WAITING log file parallel write

c.sql: OPENしているカーソルのライブラリキャッシュ情報を表示

http://blog.tanelpoder.com/files/scripts/c.sql

V$OPEN_CURSORとX$KGLOBから情報を取得し、指定したセッションがOPENしているカーソルに対応するライブラリキャッシュオブジェクトの情報を表示します。

SQL> @c 26

KGLHDADR         KGLHDPAR         TSTAMP            CC_OBJTYPE                     HEAPSIZE   KGLHDLDC   KGLHDEXC   KGLHDLKC   KGLOBPC0 DEC   KGLHDCLT   KGLHDIVC   KGLHDKMK OWNER                     SQL_TEXT                                 DB_LINK
---------------- ---------------- ----------------- ---------------------------- ---------- ---------- ---------- ---------- ---------- --- ---------- ---------- ---------- ------------------------- ---------------------------------------- -------------------------
000000007C19EFE8 000000007C19EFE8 20131217 16:37:27 NOT LOADED                         4072          2          3          1          0 NO        7564          0          0                           BEGIN
                                                                                                                                                                                                       DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL
                                                                                                                                                                                                       ); END;
  : 

情報が膨大なので、pr.sqlで整形します。

SQL> @pr
Pivoting output using Tom Kyte's printtab....
==============================
KGLHDADR                      : 000000007C19EFE8
KGLHDPAR                      : 000000007C19EFE8
TSTAMP                        : 20131217 16:37:27
CC_OBJTYPE                    : NOT LOADED
HEAPSIZE                      : 4072
KGLHDLDC                      : 2
KGLHDEXC                      : 3
KGLHDLKC                      : 1
KGLOBPC0                      : 0
DECODE(KGLHDKMK,0,'NO','YES') : NO
KGLHDCLT                      : 7564
KGLHDIVC                      : 0
KGLHDKMK                      : 0
C_KGLNAOWN                    :
C_KGLNAOBJ                    : BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;
C_KGLNADLK                    :
==============================
  :

d.sql: Show data dictionary views and x$ tables matching the expression

http://blog.tanelpoder.com/files/scripts/d.sql

DICT(DICTIONARY)やDBA_TABLESを検索して、名称に指定された文字列を含むディクショナリを表示します。

例えば、AWR関連のディクショナリを表示したい場合は以下のようにします。

SQL> @d DBA_HIST
Show data dictionary views and x$ tables matching the expression "DBA_HIST"...

TABLE_NAME                     COMMENTS
------------------------------ --------------------------------------------------------------------------------
DBA_HIST_ACTIVE_SESS_HISTORY   Active Session Historical Statistics Information
DBA_HIST_APPLY_SUMMARY         Streams/Goldengate/XStream Apply Historical Statistics Information
DBA_HIST_ASH_SNAPSHOT
DBA_HIST_BASELINE              Baseline Metadata Information
DBA_HIST_BASELINE_DETAILS      Baseline Stats on per Instance Level
  :
DBA_HIST_WAITCLASSMET_HISTORY  Wait Class Metric History
DBA_HIST_WAITSTAT              Wait Historical Statistics Information
DBA_HIST_WR_CONTROL            Workload Repository Control Information
DBA_HISTOGRAMS                 Synonym for DBA_TAB_HISTOGRAMS

122行が選択されました。

レコードが選択されませんでした。

それに加えて、該当するディクショナリを使用しているビュー(主にV$ビュー)も表示してくれます。 特定のX$表がどのV$ビューで使用されているかを確認したい場合に便利です。

SQL> @d BH
Show data dictionary views and x$ tables matching the expression "BH"...

TABLE_NAME                     COMMENTS
------------------------------ --------------------------------------------------------------------------------
GV$BH                          Synonym for GV_$BH
V$BH                           Synonym for V_$BH

TABLE_NAME                     USED_IN
------------------------------ ------------------------------
X$KJZNCBHANGS
X$KCBBHS
X$BH                           GV$BH
X$KCBOBH
X$KRCFBH
X$KTFBHC
X$KFCBH
X$KFBH
GV$BH                          V$BH
V$BH                           V$BH

10行が選択されました。

f.sql: Search for Fixed view (V$ view)

http://blog.tanelpoder.com/files/scripts/f.sql

V$FIXED_VIEW_DEFINITIONから確認できるビュー定義(view_definition列)から、該当する文字列を含むV$ビューを表示します。d.sqlと同様に、特定のX$表がどのV$ビューで使用されているかを確認したい場合に便利です。

SQL> @f X$BH
Search for Fixed view (V$ view) text containing %X$BH%

VIEW_NAME                 TEXT
------------------------- ------------------------------------------------------------
GV$BH                     select bh.inst_id, file#, dbablk, class,
                          decode(state,0,'free',1,'xcur',2,'scur',3,'cr',
                          4,'read',5,'mrec',6,'irec',7,'write',8,'pi',
                          9,'memory',10,'mwrite',11,'donated', 12,'protected',
                          13,'securefile', 14,'siop',15,'recckpt', 16, 'flashfree',
                          17, 'flashcur', 18, 'flashna'), 0, 0, 0, bh.le_addr,
                          le_id1, le_id2, decode(bitand(flag,1), 0, 'N', 'Y'),
                          decode(bitand(flag,16), 0, 'N', 'Y'),
                          decode(bitand(flag,1536), 0, 'N', 'Y'),
                          decode(bitand(flag,16384), 0, 'N', 'Y'),
                          decode(bitand(flag,65536), 0, 'N', 'Y'), 'N', obj, ts#,
                          lobid,  bitand(OBJ_FLAG, 240)/16, decode(bitand(OBJ_FLAG,
                          48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'),
                          decode(bitand(OBJ_FLAG, 192)/64, 1, 'KEEP', 2, 'NONE',
                          'DEFAULT'), bh.con_id from x$bh bh, x$le le where
                          bh.le_addr = le.le_addr (+)

GV$PING                   WITH bh AS (SELECT b.inst_id, file#, dbablk block#, class
                          class#,

                          : 

h.sql: sqlplus.exeコマンド履歴の検索(Windows限定)

http://blog.tanelpoder.com/files/scripts/h.sql

host " doskey /history /exename=sqlplus.exe | find /i /n "&1" | find /v "]@h " "

Windowsプラットフォーム限定のスクリプトです。doskeyが保持するコマンド履歴情報から、指定した文字列を含む情報を表示します。自分の環境では適切に動作しませんでした。このスクリプトが適切に動作するかは、doskeyが適切にするか依存すると思われます。

i.sql:

http://blog.tanelpoder.com/files/scripts/i.sql

http://co-sol.jp/techdb/2012/12/tpt_scripts_and_i_sql.htmlで説明しているので、参考にしてください。

l.sql: Display Latch stats from V$LATCH for latches

http://blog.tanelpoder.com/files/scripts/l.sql

名称に指定した文字列を含むラッチの統計情報を表示します。

SQL> @l shared
Display Latch stats from V$LATCH for latches matching %shared%

ADDR             NAME                                                                   GETS     MISSES         IG         IM   SPINGETS  WAIT_TIME
---------------- ---------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ----------
0000000060009B58 test shared non-parent l0                                                 0          0          0          0          0          0
00000000600105B8 ksxp shared latch                                                         0          0          0          0          0          0
0000000060034348 shared pool                                                        17194520      29891          0          0      28009    2007788
0000000060034D48 shared pool simulator                                                492095          0          0          0          0          0
0000000060034DE8 shared pool sim alloc                                                  2247          0          0          0          0          0
0000000060034F28 Shared B-Tree                                                         45940          0          0          0          0          0
0000000060037E58 shared server configuration                                               8          0          1          0          0          0
0000000060038218 shared server info                                                        1          0          0          0          0          0

8行が選択されました。

m.sql: 自セッションの統計を表示します

http://blog.tanelpoder.com/files/scripts/m.sql

自セッションの統計(待機イベント、統計情報)を表示します。内部的にse.sqlを呼び出しています。

SQL> @m

    SID EVENT                                     TIME_WAITED TOTAL_WAITS TOTAL_TIMEOUTS AVERAGE_WAIT   MAX_WAIT
------- ---------------------------------------- ------------ ----------- -------------- ------------ ----------
     42 SQL*Net message from client                     64.82          11              0       5.8929      64.81
        CPU Time                                          .05
        SQL*Net message to client                         .00          12              0            0          0
        Disk file operations I/O                          .00           1              0        .0003          0

o.sql: 指定した文字列を含むオブジェクトの情報を表示

http://blog.tanelpoder.com/files/scripts/o.sql

名称に指定した文字列を含むオブジェクトの情報を表示します。内部的にo2.sqlを呼び出しています。

SQL> @o ADVISOR

owner                     object_name                    object_type        status           OID      D_OID CREATED           LAST_DDL_TIME
------------------------- ------------------------------ ------------------ --------- ---------- ---------- ----------------- -----------------
SYS                       AUTO_SPACE_ADVISOR_PROG        PROGRAM            VALID          16928            20131016 17:19:45 20131016 17:19:45
SYS                       CDB_ADVISOR_ACTIONS            VIEW               VALID          14142            20131016 17:15:54 20131016 17:15:54
SYS                       CDB_ADVISOR_COMMANDS           VIEW               VALID          14144            20131016 17:15:54 20131016 17:15:54
SYS                       CDB_ADVISOR_DEFINITIONS        VIEW               VALID          14146            20131016 17:15:54 20131016 17:15:54
SYS                       CDB_ADVISOR_DEF_PARAMETERS     VIEW               VALID          14148            20131016 17:15:54 20131016 17:15:54
SYS                       CDB_ADVISOR_DIR_DEFINITIONS    VIEW               VALID          14150            20131016 17:15:54 20131016 17:15:54
SYS                       CDB_ADVISOR_DIR_INSTANCES      VIEW               VALID          14152            20131016 17:15:54 20131016 17:15:54
SYS                       CDB_ADVISOR_DIR_TASK_INST      VIEW               VALID          14154            20131016 17:15:54 20131016 17:15:54
  : 

p.sql: 指定した文字列を含む初期化パラメータを表示

http://blog.tanelpoder.com/files/scripts/p.sql

名称に指定した文字列を含む初期化パラメータの情報を表示します。いわゆる隠しパラメータも表示します。

SQL> @p buffer

NAME                                     VALUE
---------------------------------------- ----------------------------------------
use_indirect_data_buffers                FALSE
_trace_buffers                           ALL:256
_skgxp_udp_timed_wait_buffering          1024
_datapump_metadata_buffer_size           131072
_datapump_tabledata_buffer_size          262144
_datapump_compressbas_buffer_size        0
_lm_postevent_buffer_size                256
db_block_buffers                         0
_db_block_buffers                        24500
_clear_buffer_before_reuse               FALSE
_data_warehousing_scan_buffers           TRUE
buffer_pool_keep
buffer_pool_recycle
_trace_buffer_wait_timeouts              0
_buffer_busy_wait_timeout                100
  : 

s.sql: Display current Session Wait and SQL_ID info (10g+)

http://blog.tanelpoder.com/files/scripts/s.sql

指定したセッションの待機情報と実行中SQLのSQL_IDを表示します。

SQL> @s 26

    SID SQLID_AND_CHILD      STATUS   STATE   EVENT                                          SEQ# SEC_IN_WAIT BLOCKING_SID P1                 P2                 P3                 P1TRANSL
------- -------------------- -------- ------- ---------------------------------------- ---------- ----------- ------------ ------------------ ------------------ ------------------ ------------------------------------------
     26 bvr7ppsyp19qw 0      ACTIVE   WORKING On CPU / runqueue                              5746           5 NOT IN WAIT

SQL> @s 26

    SID SQLID_AND_CHILD      STATUS   STATE   EVENT                                          SEQ# SEC_IN_WAIT BLOCKING_SID P1                 P2                 P3                 P1TRANSL
------- -------------------- -------- ------- ---------------------------------------- ---------- ----------- ------------ ------------------ ------------------ ------------------ ------------------------------------------
     26 bvr7ppsyp19qw 0      ACTIVE   WORKING On CPU / runqueue                              5746          12 NOT IN WAIT

SQL> @s 26

    SID SQLID_AND_CHILD      STATUS   STATE   EVENT                                          SEQ# SEC_IN_WAIT BLOCKING_SID P1                 P2                 P3                 P1TRANSL
------- -------------------- -------- ------- ---------------------------------------- ---------- ----------- ------------ ------------------ ------------------ ------------------ ------------------------------------------
     26 bvr7ppsyp19qw 0      ACTIVE   WORKING On CPU / runqueue                              5746          15 NOT IN WAIT

u.sql: V$SESSIONとV$PROCESSを結合して表示

http://blog.tanelpoder.com/files/scripts/u.sql

現行のセッションに関する、V$SESSIONとV$PROCESSの情報を結合して表示します。

SQL> @u

USERNAME                SID            OSUSER           MACHINE            PROGRAM              MODULE               ACTION                                                           SPID         SQL_ID        SQL_HASH_VALUE   LASTCALL STATUS   LOGON_TIME
----------------------- -------------- ---------------- ------------------ -------------------- -------------------- ---------------------------------------------------------------- ------------ ------------- -------------- ---------- -------- -----------------
                         '1,1'         oracle           l64rw3.domain      (PMON)                                                                                                     4380                                    0    5342428 ACTIVE   20131016 21:22:47
                         '238,1'       oracle           l64rw3.domain      (PSP0)                                                                                                     4384                                    0    5342428 ACTIVE   20131016 21:22:47
                         '2,1'         oracle           l64rw3.domain      (VKTM)                                                                                                     4388                                    0    5342428 ACTIVE   20131016 21:22:48
                         '239,1'       oracle           l64rw3.domain      (GEN0)                                                                                                     4394                                    0    5342425 ACTIVE   20131016 21:22:48
    :

v.sql: Show SQL text of views

http://blog.tanelpoder.com/files/scripts/v.sql

名称に指定した文字列を含むビューについて、ビュー定義のSQLを表示します。

SQL> @v SQLTEXT
Show SQL text of views matching "%SQLTEXT%"...

VIEW_NAME                      TEXT
------------------------------ ----------------------------------------------------------------------------------------------------
V_$SQLTEXT                     select "ADDRESS","HASH_VALUE","SQL_ID","COMMAND_TYPE","PIECE","SQL_TEXT","CON_ID" from v$sqltext
V_$SQLTEXT_WITH_NEWLINES       select "ADDRESS","HASH_VALUE","SQL_ID","COMMAND_TYPE","PIECE","SQL_TEXT","CON_ID" from
                               v$sqltext_with_newlines

GV_$SQLTEXT                    select "INST_ID","ADDRESS","HASH_VALUE","SQL_ID","COMMAND_TYPE","PIECE","SQL_TEXT","CON_ID" from
                               gv$sqltext

GV_$SQLTEXT_WITH_NEWLINES      select "INST_ID","ADDRESS","HASH_VALUE","SQL_ID","COMMAND_TYPE","PIECE","SQL_TEXT","CON_ID" from
                               gv$sqltext_with_newlines

DBA_HIST_SQLTEXT               select dbid, sql_id, sql_text, command_type,
                               decode(con_dbid, 0, dbid, con_dbid),
                               con_dbid_to_id(decode(con_dbid, NULL, 0, con_dbid)) con_id
                               from WRH$_SQLTEXT

CDB_HIST_SQLTEXT               SELECT "DBID","SQL_ID","SQL_TEXT","COMMAND_TYPE","CON_DBID","CON_ID" FROM
                               CDB$VIEW("SYS"."DBA_HIST_SQLTEXT")

6行が選択されました。

VIEW_NAME                      TEXT
------------------------------ ----------------------------------------------------------------------------------------------------
GV$SQLTEXT                     select inst_id,kglhdadr, kglnahsh, kglnasqlid, kgloboct, piece, name, con_id  from x$kglna where
                               kgloboct != 0

V$SQLTEXT                      select  ADDRESS, HASH_VALUE, SQL_ID, COMMAND_TYPE , PIECE,  SQL_TEXT, CON_ID from GV$SQLTEXT where
                               inst_id = USERENV('Instance')

GV$SQLTEXT_WITH_NEWLINES       select inst_id,kglhdadr, kglnahsh, kglnasqlid, kgloboct, piece, name, con_id  from x$kglna1 where
                               kgloboct != 0

V$SQLTEXT_WITH_NEWLINES        select  ADDRESS, HASH_VALUE, SQL_ID, COMMAND_TYPE, PIECE,          SQL_TEXT, CON_ID  from
                               GV$SQLTEXT_WITH_NEWLINES where inst_id = USERENV('Instance')

w.sql: 5秒間の負荷状態をレポート

http://blog.tanelpoder.com/files/scripts/w.sql

snapper.sqlを呼び出し、5秒間の負荷状態をレポートします。

SQL> @w
What's going on right now?!
Sampling SID all with interval 5 seconds, taking 1 snapshots...

-- Session Snapper v3.64 by Tanel Poder ( http://blog.tanelpoder.com )

---------------------------------------------------------------------------------------------
Active% | SQL_ID          | SQL_CHILD | EVENT                               | WAIT_CLASS
---------------------------------------------------------------------------------------------
   100% | bvr7ppsyp19qw   | 0         | ON CPU                              | ON CPU
     2% |                 | 0         | control file parallel write         | System I/O

---------------------------------------------------------------
Active% | EVENT                               | WAIT_CLASS
---------------------------------------------------------------
   100% | ON CPU                              | ON CPU
     2% | control file parallel write         | System I/O

------------------------------------------------------------------------------------------
Active% |    SID | SQL_ID          | EVENT                               | WAIT_CLASS
------------------------------------------------------------------------------------------
   100% |     26 | bvr7ppsyp19qw   | ON CPU                              | ON CPU
     2% |    243 |                 | control file parallel write         | System I/O

-------------------------
Active% | SQL_ID
-------------------------
   100% | bvr7ppsyp19qw
     2% |

--  End of ASH snap 1, end=2013-12-17 17:27:48, seconds=5, samples_taken=44

PL/SQLプロシージャが正常に完了しました。

x.sql: Display execution plan for last statement for this session from library cache

http://blog.tanelpoder.com/files/scripts/x.sql

dbms_xplan.display_cursor(null,null,'ALLSTATS LAST +PEEKED_BINDS +PARALLEL +PARTITION +COST +BYTES') を実行し、直近で実行されたSQLの実行計画と実行統計を表示します。

SQL> SELECT * FROM DUAL;

D
-
X

SQL> @x
Display execution plan for last statement for this session from library cache...

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
SQL_ID  9g6pyx7qz035v, child number 0
-------------------------------------
SELECT * FROM DUAL

Plan hash value: 272002086

----------------------------------------------------------------
| Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |       |     2 (100)|
|   1 |  TABLE ACCESS FULL| DUAL |      1 |     2 |     2   (0)|
----------------------------------------------------------------

14行が選択されました。

実行統計を表示するためには、statistics_levelをALLに設定するか、SQLに /*+ gather_plan_statistics */ ヒントを指定する必要があることに注意してください。

SQL> show parameter statistics

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_pending_statistics     boolean     FALSE
statistics_level                     string      TYPICAL
timed_os_statistics                  integer     0
timed_statistics                     boolean     TRUE
SQL> ALTER SESSION SET statistics_level='ALL';

セッションが変更されました。

SQL> SELECT * FROM DUAL;

D
-
X

SQL> @x
Display execution plan for last statement for this session from library cache...

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
SQL_ID  9g6pyx7qz035v, child number 1
-------------------------------------
SELECT * FROM DUAL

Plan hash value: 272002086

---------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |     2 (100)|      1 |00:00:00.01 |       3 |
|   1 |  TABLE ACCESS FULL| DUAL |      1 |      1 |     2 |     2   (0)|      1 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------------------

13行が選択されました。

ash/w.sql

http://blog.tanelpoder.com/files/scripts/ash/w.sql

内部的にashtop.sqlを呼び出しています。ashtop.sqlの動作を含め後日説明予定です。

SQL> @ash/w
What's going on? Showing top timed events from last minute...

    Total
  Seconds     AAS %This   SESSION EVENT
--------- ------- ------- ------- ----------------------------------------------------------------
        5      .1   71% | ON CPU
        1      .0   14% | WAITING db file async I/O submit
        1      .0   14% | WAITING log file parallel write

Showing top SQL and wait classes from last minute...

    Total
  Seconds     AAS %This   SQL_ID        SESSION WAIT_CLASS
--------- ------- ------- ------------- ------- ----------------------------------------------------------------
        4      .1   57% |               ON CPU
        2      .0   29% |               WAITING System I/O
        1      .0   14% | bvr7ppsyp19qw ON CPU

プロフィール

On7tWW6m1Ul4

渡部 亮太

・Oracle ACE
・AWS Certified Solutions Architect - Associate
・ORACLE MASTER Platinum Oracle Database 11g, 12c 他多数

カテゴリー

アーカイブ