lotslios.sql from TPT Scripts : Generate Lots of Logical IOs for testing purposes

Oracle Database
2013.12.19
渡部亮太

lotslios.sql from TPT Scripts : Generate Lots of Logical IOs for testing purposes

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

Tanel Poderさんのエントリを翻訳しようと思ったのですが、翻訳作業に使用しているGoogle翻訳者ツールキットの調子がイマイチだった（今では改善されているかもしれません）ので、翻訳はあきらめて、スクリプト lotslios.sql を紹介することにします。 lotslios.sql は、大量の論理I/Oを人為的に発生させるスクリプトです。容易にキャッシュできるサイズのテーブル(sys.obj$)を繰り返しフルスキャンするSQLを実行して、大量の論理I/O(=バッファアクセス)を発生させます。引数でI/O量を調整できます。

実行するSQLは以下の通りです。

select
    /*+ monitor
        ordered 
        use_nl(b) use_nl(c) use_nl(d) 
        full(a) full(b) full(c) full(d) */
    count(*)
from
    sys.obj$ a,
    sys.obj$ b,
    sys.obj$ c,
    sys.obj$ d
where
    a.owner# = b.owner#
and b.owner# = c.owner#
and c.owner# = d.owner#
and rownum <= &1  <== (*1)
/

fullヒントを指定し、かつ、セルフジョインで同一表に繰り返しアクセスしているため、アクセスする表のすべてのブロックがキャッシュ済みであることが期待できます。

(*1)の指定により、返される行数を調整できます。今回use_nlヒントを指定して、ネステッドループ結合でセルフジョインしているため、バッファへのアクセス数と行数をおおむね比例関係にできます。なぜなら、ネステッドループ結合は指定した行数を取得すれば、処理を停止できるFIRST_ROWS系のオペレーションだからです。

ちょっと説明を端折りすぎかもしれません。実行例で説明しましょう。引数を100, 10000, 1000000 と変えて、実行計画と実行統計を確認します。

SQL>  alter session set STATISTICS_LEVEL = ALL;

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

SQL> @lotslios 100
generate lots of LIOs by repeatedly full scanning through a small table...

  COUNT(*)
----------
       100

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(null, null, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  6f7xzt60hbcds, child number 0
-------------------------------------
select     /*+ ordered use_nl(b) use_nl(c) use_nl(d) full(a) full(b)
full(c) full(d) */     count(*) from     sys.obj$ a,     sys.obj$ b,
 sys.obj$ c,     sys.obj$ d where     a.owner# = b.owner# and b.owner#
= c.owner# and c.owner# = d.owner# and rownum <= 100

Plan hash value: 3691747574

-----------------------------------------------------------------------------------------
| Id  | Operation              | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |      1 |        |      1 |00:00:00.01 |      13 |<==
|   1 |  SORT AGGREGATE        |      |      1 |      1 |      1 |00:00:00.01 |      13 |
|*  2 |   COUNT STOPKEY        |      |      1 |        |    100 |00:00:00.01 |      13 |
|   3 |    NESTED LOOPS        |      |      1 |   2331 |    100 |00:00:00.01 |      13 |
|   4 |     NESTED LOOPS       |      |      1 |      9 |      1 |00:00:00.01 |       9 |
|   5 |      NESTED LOOPS      |      |      1 |      3 |      1 |00:00:00.01 |       6 |
|   6 |       TABLE ACCESS FULL| OBJ$ |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  7 |       TABLE ACCESS FULL| OBJ$ |      1 |      3 |      1 |00:00:00.01 |       3 |
|*  8 |      TABLE ACCESS FULL | OBJ$ |      1 |      3 |      1 |00:00:00.01 |       3 |
|*  9 |     TABLE ACCESS FULL  | OBJ$ |      1 |    259 |    100 |00:00:00.01 |       4 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM<=100)
   7 - filter("A"."OWNER#"="B"."OWNER#")
   8 - filter("B"."OWNER#"="C"."OWNER#")
   9 - filter("C"."OWNER#"="D"."OWNER#")

32行が選択されました。

SQL> @lotslios 10000
generate lots of LIOs by repeatedly full scanning through a small table...

  COUNT(*)
----------
     10000

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(null, null, 'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3vb2a3bb0yjfr, child number 0
-------------------------------------
select     /*+ ordered use_nl(b) use_nl(c) use_nl(d) full(a) full(b)
full(c) full(d) */     count(*) from     sys.obj$ a,     sys.obj$ b,
 sys.obj$ c,     sys.obj$ d where     a.owner# = b.owner# and b.owner#
= c.owner# and c.owner# = d.owner# and rownum <= 10000

Plan hash value: 3691747574

-----------------------------------------------------------------------------------------
| Id  | Operation              | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |      1 |        |      1 |00:00:00.22 |     240 |<==
|   1 |  SORT AGGREGATE        |      |      1 |      1 |      1 |00:00:00.22 |     240 |
|*  2 |   COUNT STOPKEY        |      |      1 |        |  10000 |00:00:00.18 |     240 |
|   3 |    NESTED LOOPS        |      |      1 |    232K|  10000 |00:00:00.11 |     240 |
|   4 |     NESTED LOOPS       |      |      1 |      9 |      1 |00:00:00.01 |       9 |
|   5 |      NESTED LOOPS      |      |      1 |      3 |      1 |00:00:00.01 |       6 |
|   6 |       TABLE ACCESS FULL| OBJ$ |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  7 |       TABLE ACCESS FULL| OBJ$ |      1 |      3 |      1 |00:00:00.01 |       3 |
|*  8 |      TABLE ACCESS FULL | OBJ$ |      1 |      3 |      1 |00:00:00.01 |       3 |
|*  9 |     TABLE ACCESS FULL  | OBJ$ |      1 |  25863 |  10000 |00:00:00.04 |     231 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM<=10000)
   7 - filter("A"."OWNER#"="B"."OWNER#")
   8 - filter("B"."OWNER#"="C"."OWNER#")
   9 - filter("C"."OWNER#"="D"."OWNER#")

32行が選択されました。

SQL> @lotslios 1000000
generate lots of LIOs by repeatedly full scanning through a small table...

  COUNT(*)
----------
   1000000

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(null, null, 'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  4br5j422nhw5c, child number 0
-------------------------------------
select     /*+ ordered use_nl(b) use_nl(c) use_nl(d) full(a) full(b)
full(c) full(d) */     count(*) from     sys.obj$ a,     sys.obj$ b,
 sys.obj$ c,     sys.obj$ d where     a.owner# = b.owner# and b.owner#
= c.owner# and c.owner# = d.owner# and rownum <= 1000000

Plan hash value: 3691747574

-----------------------------------------------------------------------------------------
| Id  | Operation              | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |      1 |        |      1 |00:00:20.26 |   33272 |<==
|   1 |  SORT AGGREGATE        |      |      1 |      1 |      1 |00:00:20.26 |   33272 |
|*  2 |   COUNT STOPKEY        |      |      1 |        |   1000K|00:00:16.93 |   33272 |
|   3 |    NESTED LOOPS        |      |      1 |     20M|   1000K|00:00:10.36 |   33272 |
|   4 |     NESTED LOOPS       |      |      1 |    228 |     24 |00:00:00.01 |       9 |
|   5 |      NESTED LOOPS      |      |      1 |      3 |      1 |00:00:00.01 |       6 |
|   6 |       TABLE ACCESS FULL| OBJ$ |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  7 |       TABLE ACCESS FULL| OBJ$ |      1 |      3 |      1 |00:00:00.01 |       3 |
|*  8 |      TABLE ACCESS FULL | OBJ$ |      1 |     76 |     24 |00:00:00.01 |       3 |
|*  9 |     TABLE ACCESS FULL  | OBJ$ |     24 |  90005 |   1000K|00:00:03.66 |   33263 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM<=1000000)
   7 - filter("A"."OWNER#"="B"."OWNER#")
   8 - filter("B"."OWNER#"="C"."OWNER#")
   9 - filter("C"."OWNER#"="D"."OWNER#")

32行が選択されました。

アクセスしているバッファ数(Buffers統計)が増えているのがわかるでしょう。（完全に線系な比例関係ではありませんが）

次にスクリプトのuse_nlヒントをuse_hashに変えた同様のコマンドを実行します。

SQL> @lotslios_hj 100
generate lots of LIOs by repeatedly full scanning through a small table...

  COUNT(*)
----------
       100

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(null, null, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8g0ac459tbksm, child number 0
-------------------------------------
select     /*+ ordered use_hash(b) use_hash(c) use_hash(d) full(a)
full(b) full(c) full(d) */     count(*) from     sys.obj$ a,
sys.obj$ b,     sys.obj$ c,     sys.obj$ d where     a.owner# =
b.owner# and b.owner# = c.owner# and c.owner# = d.owner# and rownum <=
100

Plan hash value: 3474866098

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |      1 |        |      1 |00:00:00.12 |    4197 |       |       |          |
|   1 |  SORT AGGREGATE        |      |      1 |      1 |      1 |00:00:00.12 |    4197 |       |       |          |
|*  2 |   COUNT STOPKEY        |      |      1 |        |    100 |00:00:00.12 |    4197 |       |       |          |
|*  3 |    HASH JOIN           |      |      1 |   2331 |    100 |00:00:00.12 |    4197 |  5447K|  3487K| 7328K (0)|
|   4 |     TABLE ACCESS FULL  | OBJ$ |      1 |  90005 |  90106 |00:00:00.01 |    1398 |       |       |          |
|*  5 |     HASH JOIN          |      |      1 |      9 |    200 |00:00:00.08 |    2799 |  5447K|  3487K| 7320K (0)|
|   6 |      TABLE ACCESS FULL | OBJ$ |      1 |  90005 |  90106 |00:00:00.01 |    1398 |       |       |          |
|*  7 |      HASH JOIN         |      |      1 |      3 |    200 |00:00:00.04 |    1401 |  5447K|  3487K| 7296K (0)|
|   8 |       TABLE ACCESS FULL| OBJ$ |      1 |  90005 |  90106 |00:00:00.01 |    1398 |       |       |          |
|   9 |       TABLE ACCESS FULL| OBJ$ |      1 |      3 |     81 |00:00:00.01 |       3 |       |       |          |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM<=100)
   3 - access("C"."OWNER#"="D"."OWNER#")
   5 - access("B"."OWNER#"="C"."OWNER#")
   7 - access("A"."OWNER#"="B"."OWNER#")

33行が選択されました。

SQL> @lotslios_hj 10000
generate lots of LIOs by repeatedly full scanning through a small table...

  COUNT(*)
----------
     10000

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(null, null, 'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  26pfu943qtvjf, child number 0
-------------------------------------
select     /*+ ordered use_hash(b) use_hash(c) use_hash(d) full(a)
full(b) full(c) full(d) */     count(*) from     sys.obj$ a,
sys.obj$ b,     sys.obj$ c,     sys.obj$ d where     a.owner# =
b.owner# and b.owner# = c.owner# and c.owner# = d.owner# and rownum <=
10000

Plan hash value: 3474866098

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |      1 |        |      1 |00:00:00.26 |    4197 |       |       |          |
|   1 |  SORT AGGREGATE        |      |      1 |      1 |      1 |00:00:00.26 |    4197 |       |       |          |
|*  2 |   COUNT STOPKEY        |      |      1 |        |  10000 |00:00:00.23 |    4197 |       |       |          |
|*  3 |    HASH JOIN           |      |      1 |    232K|  10000 |00:00:00.16 |    4197 |  5447K|  3487K| 8010K (0)|
|   4 |     TABLE ACCESS FULL  | OBJ$ |      1 |  90005 |  90106 |00:00:00.01 |    1398 |       |       |          |
|*  5 |     HASH JOIN          |      |      1 |      9 |    200 |00:00:00.07 |    2799 |  5447K|  3487K| 8008K (0)|
|   6 |      TABLE ACCESS FULL | OBJ$ |      1 |  90005 |  90106 |00:00:00.01 |    1398 |       |       |          |
|*  7 |      HASH JOIN         |      |      1 |      3 |    200 |00:00:00.04 |    1401 |  5447K|  3487K| 8047K (0)|
|   8 |       TABLE ACCESS FULL| OBJ$ |      1 |  90005 |  90106 |00:00:00.01 |    1398 |       |       |          |
|   9 |       TABLE ACCESS FULL| OBJ$ |      1 |      3 |     81 |00:00:00.01 |       3 |       |       |          |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM<=10000)
   3 - access("C"."OWNER#"="D"."OWNER#")
   5 - access("B"."OWNER#"="C"."OWNER#")
   7 - access("A"."OWNER#"="B"."OWNER#")

33行が選択されました。

SQL> @lotslios_hj 1000000
generate lots of LIOs by repeatedly full scanning through a small table...

  COUNT(*)
----------
   1000000

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(null, null, 'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  54bwq8x1zwz99, child number 0
-------------------------------------
select     /*+ ordered use_hash(b) use_hash(c) use_hash(d) full(a)
full(b) full(c) full(d) */     count(*) from     sys.obj$ a,
sys.obj$ b,     sys.obj$ c,     sys.obj$ d where     a.owner# =
b.owner# and b.owner# = c.owner# and c.owner# = d.owner# and rownum <=
1000000

Plan hash value: 3474866098

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |      1 |        |      1 |00:00:13.21 |    4197 |       |       |          |
|   1 |  SORT AGGREGATE        |      |      1 |      1 |      1 |00:00:13.21 |    4197 |       |       |          |
|*  2 |   COUNT STOPKEY        |      |      1 |        |   1000K|00:00:09.98 |    4197 |       |       |          |
|*  3 |    HASH JOIN           |      |      1 |     20M|   1000K|00:00:03.48 |    4197 |  5447K|  3487K| 8010K (0)|
|   4 |     TABLE ACCESS FULL  | OBJ$ |      1 |  90005 |  90106 |00:00:00.02 |    1398 |       |       |          |
|*  5 |     HASH JOIN          |      |      1 |    228 |    200 |00:00:00.07 |    2799 |  5447K|  3487K| 8008K (0)|
|   6 |      TABLE ACCESS FULL | OBJ$ |      1 |  90005 |  90106 |00:00:00.01 |    1398 |       |       |          |
|*  7 |      HASH JOIN         |      |      1 |      3 |    200 |00:00:00.04 |    1401 |  5447K|  3487K| 8028K (0)|
|   8 |       TABLE ACCESS FULL| OBJ$ |      1 |  90005 |  90106 |00:00:00.01 |    1398 |       |       |          |
|   9 |       TABLE ACCESS FULL| OBJ$ |      1 |      3 |     81 |00:00:00.01 |       3 |       |       |          |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM<=1000000)
   3 - access("C"."OWNER#"="D"."OWNER#")
   5 - access("B"."OWNER#"="C"."OWNER#")
   7 - access("A"."OWNER#"="B"."OWNER#")

33行が選択されました。

バッファへのアクセス数(Buffers統計)が増えないことがわかるでしょう。

ネステッドループジョインとハッシュジョインの違いは、結果セットの一部のデータのみを対象に処理が可能かどうかです。ネステッドループジョインは、結果セットの一部のデータのみを対象に処理が可能です。したがって、

and rownum <= &1

という行数を制限する述語（実行計画上は "COUNT STOPKEY"オペレーションとして表記）を指定した場合、結果セットの先頭X行のみを処理することができます。このため、上記の&1(=スクリプトの第１引数)の値の大小により処理の量を制御できます。ひいては、バッファへのアクセス数を制御できます。

一方、ハッシュジョインは結果セットの一部のデータのみを対象に処理ができません。したがって、、上記の&1(=スクリプトの第１引数)の値の大小により処理の量を制御できません。ひいては、バッファへのアクセス数を制御できません。

さて、lotslios.sqlを実行することで論理I/O を発生させることが分かったところで、論理I/O多発時のプロセスレベルでの実行状態を見ておきましょう。

SQL> @i

USERNAME             INST_NAME    HOST_NAME                 SID   SERIAL#  VERSION    STARTED  SPID            OPID  CPID
-------------------- ------------ ------------------------- ----- -------- ---------- -------- --------------- ----- ---------------
SADDR            PADDR
---------------- ----------------
SYS                  c101         l64rw3.domain             41    7387     12.1.0.1.0 20131016 15270           22    15267
000000008C4ADA70 000000008C9A1258

SQL> @lotslios 1000000000
generate lots of LIOs by repeatedly full scanning through a small table...

以下の通り、SQLを実行しているプロセス(SPID=15270)が、CPUを100%使用していることがわかります。一般に論理I/Oの多発は、CPU使用率の上昇を招きます。

top - 14:36:49 up 20 days, 22:57,  5 users,  load average: 0.23, 0.06, 0.02
Tasks: 246 total,   2 running, 244 sleeping,   0 stopped,   0 zombie
Cpu(s): 52.3%us,  2.1%sy,  0.0%ni, 45.4%id,  0.2%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   1922520k total,  1789692k used,   132828k free,    76504k buffers
Swap:  4194296k total,   738428k used,  3455868k free,  1272100k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
15270 oracle    20   0 1031m  49m  46m R 100.0  2.6   0:15.33 oracle_15270_c1 ←★
 4388 oracle    -2   0 1027m 3568 3252 S  7.0  0.2   2527:57 ora_vktm_c101
 4406 oracle    20   0 1027m 3440 3256 S  0.3  0.2   4:21.56 ora_diag_c101
 4410 oracle    20   0 1028m  72m  70m S  0.3  3.8   3:51.26 ora_dbrm_c101
 4426 oracle    20   0 1027m  22m  21m S  0.3  1.2  20:09.18 ora_ckpt_c101
 4454 oracle    20   0 1029m  33m  32m S  0.3  1.8  29:29.35 ora_mmnl_c101
12258 oracle    20   0 97900 1568  628 S  0.3  0.1   0:00.90 sshd
  :

プロフィール

渡部亮太

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

KNOWLEDGE

技術情報

Oracle DBベストプラクティス

Toad Tips

DPA Tips

Oracle DB Tips

Dbvisitのよくある質問

資料ダウンロード

著書・出版物

雑誌寄稿・WEBコラム

セミナー

COMPANY

会社情報

企業理念

事業概要

各種認証

資格取得者数

ニュース / プレスリリース

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

lotslios.sql from TPT Scripts : Generate Lots of Logical IOs for testing purposes

プロフィール

カテゴリー

アーカイブ