SQL*Plus -Fastオプション / FAQ Tweet

Previously on Mac De Oracle.
前回は、アドベントカレンダーのおまけのおまけwでした

今日は、そこで仕込んでおいたネタを使い、SQL*Plusも機能拡張されてたのすっかり忘れていた！　ので、
高Fetch圧症の話に絡めてSQL*Plusの-F[ast]オプション書いておこうと思います。

軽めですが。

Fetch回数削減に効果があるので、多数の行をFetchするような時は思い出すと良いですね。
Client/Server間のrount tripが減ることに繋がるわけで、そこが慢性病の原因なら少しでも楽になれたら良いと思いますし。
（ということで、Fetch Sizeも忘れないでね。という気持ちを込めて。）

最初は、-Fastオプションなしで。arraysizeのデフォルトは 15です。なお、この -F[ast]オプションは、Oracle Database 12c 12.2以降でサポートされています。

[oracle@localhost ~]$ sqlplus scott/tiger@orclpdb1

...略...

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
に接続されました。
SCOTT@orclpdb1> @dayx2
  1  WITH
  2  t1 AS
  3  (
  4    SELECT
  5      pkey
  6      , CASE
  7          WHEN col2 IS NULL
  8            THEN col1
  9            ELSE col2
 10      END AS join_key
 11      ,description
 12    FROM
 13      nikoichi_mitaina_subtype
 14    WHERE
 15      col1 IS NOT NULL
 16      OR col2 IS NOT NULL
 17  )
 18  SELECT *
 19  FROM
 20    supertype st
 21    LEFT OUTER JOIN t1
 22    ON
 23*     st.pkey = t1.join_key

1000001行が選択されました。

経過: 00:02:18.83

実行計画
----------------------------------------------------------
Plan hash value: 2223315184

-------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                          |  1000K|  1451M|       |   119K  (1)| 00:00:05 |
|*  1 |  HASH JOIN OUTER   |                          |  1000K|  1451M|   497M|   119K  (1)| 00:00:05 |
|   2 |   TABLE ACCESS FULL| SUPERTYPE                |  1000K|   486M|       | 19593   (1)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| NIKOICHI_MITAINA_SUBTYPE |   750K|   723M|       | 38796   (1)| 00:00:02 |
-------------------------------------------------------------------------------------------------------

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

   1 - access("ST"."PKEY"=CASE  WHEN (ROWID(+) IS NOT NULL) THEN CASE  WHEN ("COL2"(+) IS
              NULL) THEN "COL1"(+) ELSE "COL2"(+) END  ELSE NULL END )
   3 - filter("COL1"(+) IS NOT NULL OR "COL2"(+) IS NOT NULL)


統計
----------------------------------------------------------
       1297  recursive calls
          0  db block gets
     229599  consistent gets
     374715  physical reads
          0  redo size
 1551273711  bytes sent via SQL*Net to client
     735146  bytes received via SQL*Net from client
      66668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1000001  rows processed


  1  WITH
  2  t1 AS
  3  (
  4    SELECT
  5      pkey
  6      , CASE
  7          WHEN col2 IS NULL
  8            THEN col1
  9            ELSE col2
 10      END AS join_key
 11      ,description
 12    FROM
 13      nikoichi_mitaina_subtype
 14    WHERE
 15      col1 IS NOT NULL
 16      OR col2 IS NOT NULL
 17  )
 18  SELECT /*+ MONITOR */ *
 19  FROM
 20    supertype st
 21    LEFT OUTER JOIN t1
 22    ON
 23*     st.pkey = t1.join_key

DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_ID=>NULL,TYPE=>'TEXT')
---------------------------------------------------------------------
SQL Monitoring Report

SQL Text
------------------------------
WITH t1 AS ( SELECT pkey , CASE WHEN col2 IS NULL THEN col1 ELSE col2 END AS join_key ,description
FROM nikoichi_mitaina_subtype WHERE col1 IS NOT NULL OR col2 IS NOT NULL ) SELECT /*+ MONITOR */ *
FROM supertype st LEFT OUTER JOIN t1 ON st.pkey = t1.join_key

Global Information
------------------------------

...略...

   Duration            :  173s

 ...略...

   Fetch Calls         :  66668

Global Stats
===========================================================================================
| Elapsed |   Cpu   |    IO    |  Other   | Fetch | Buffer | Read | Read  | Write | Write |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes | Reqs  | Bytes |
===========================================================================================
|      17 |      15 |     1.59 |     0.84 | 66668 |   230K | 6878 |   3GB |  5169 |   1GB |
===========================================================================================

SQL Plan Monitoring Details (Plan Hash Value=2223315184)
==============================================================================================================================================================================================================
| Id |      Operation       |           Name           |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read | Read  | Write | Write |  Mem  | Temp  | Activity |         Activity Detail         |
|    |                      |                          | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs | Bytes | Reqs  | Bytes | (Max) | (Max) |   (%)    |           (# samples)           |
==============================================================================================================================================================================================================
|  0 | SELECT STATEMENT     |                          |         |       |       172 |     +2 |     1 |       1M |      |       |       |       |     . |     . |          |                                 |
|  1 |   HASH JOIN OUTER    |                          |      1M |  119K |       173 |     +1 |     1 |       1M | 5169 |   1GB |  5169 |   1GB | 184MB |   1GB |    76.92 | Cpu (9)                         |
|    |                      |                          |         |       |           |        |       |          |      |       |       |       |       |       |          | SQL*Net more data to client (1) |
|  2 |    TABLE ACCESS FULL | SUPERTYPE                |      1M | 19593 |         1 |     +2 |     1 |       1M |  575 | 559MB |       |       |     . |     . |          |                                 |
|  3 |    TABLE ACCESS FULL | NIKOICHI_MITAINA_SUBTYPE |    750K | 38796 |        47 |     +2 |     1 |       1M | 1134 |   1GB |       |       |     . |     . |    15.38 | Cpu (2)                         |
==============================================================================================================================================================================================================

次に、-F[ast]オプションで接続します。このオプションにより、ARRAYSIZE = 100に設定されます。それ以外にも3.5.1.5 FASTオプションいくつかの設定が変更されます。

[oracle@localhost ~]$ sqlplus -Fast scott/tiger@orclpdb1

...略...

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
に接続されました。
SCOTT@orclpdb1> @dayx2
  1  WITH
  2  t1 AS
  3  (
  4    SELECT
  5      pkey
  6      , CASE
  7          WHEN col2 IS NULL
  8            THEN col1
  9            ELSE col2
 10      END AS join_key
 11      ,description
 12    FROM
 13      nikoichi_mitaina_subtype
 14    WHERE
 15      col1 IS NOT NULL
 16      OR col2 IS NOT NULL
 17  )
 18  SELECT *
 19  FROM
 20    supertype st
 21    LEFT OUTER JOIN t1
 22    ON
 23*     st.pkey = t1.join_key

1000001行が選択されました。

経過: 00:01:55.03

実行計画
----------------------------------------------------------
Plan hash value: 2223315184

-------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                          |  1000K|  1451M|       |   119K  (1)| 00:00:05 |
|*  1 |  HASH JOIN OUTER   |                          |  1000K|  1451M|   497M|   119K  (1)| 00:00:05 |
|   2 |   TABLE ACCESS FULL| SUPERTYPE                |  1000K|   486M|       | 19593   (1)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| NIKOICHI_MITAINA_SUBTYPE |   750K|   723M|       | 38796   (1)| 00:00:02 |
-------------------------------------------------------------------------------------------------------

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

   1 - access("ST"."PKEY"=CASE  WHEN (ROWID(+) IS NOT NULL) THEN CASE  WHEN ("COL2"(+) IS
              NULL) THEN "COL1"(+) ELSE "COL2"(+) END  ELSE NULL END )
   3 - filter("COL1"(+) IS NOT NULL OR "COL2"(+) IS NOT NULL)


統計
----------------------------------------------------------
       1297  recursive calls
          0  db block gets
     216797  consistent gets
     374715  physical reads
          0  redo size
 1539940308  bytes sent via SQL*Net to client
     110262  bytes received via SQL*Net from client
      10001  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1000001  rows processed


  1  WITH
  2  t1 AS
  3  (
  4    SELECT
  5      pkey
  6      , CASE
  7          WHEN col2 IS NULL
  8            THEN col1
  9            ELSE col2
 10      END AS join_key
 11      ,description
 12    FROM
 13      nikoichi_mitaina_subtype
 14    WHERE
 15      col1 IS NOT NULL
 16      OR col2 IS NOT NULL
 17  )
 18  SELECT /*+ MONITOR */ *
 19  FROM
 20    supertype st
 21    LEFT OUTER JOIN t1
 22    ON
 23*     st.pkey = t1.join_key

DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_ID=>NULL,TYPE=>'TEXT')
----------------------------------------------------------------
SQL Monitoring Report

SQL Text
------------------------------
WITH t1 AS ( SELECT pkey , CASE WHEN col2 IS NULL THEN col1 ELSE col2 END AS join_key ,description
FROM nikoichi_mitaina_subtype WHERE col1 IS NOT NULL OR col2 IS NOT NULL ) SELECT /*+ MONITOR */ *
FROM supertype st LEFT OUTER JOIN t1 ON st.pkey = t1.join_key

Global Information
------------------------------

...略...

   Duration            :  146s

 ...略...

   Fetch Calls         :  10001

Global Stats
============================================================================================
| Elapsed |   Cpu   |    IO    |  Other   | Fetch  | Buffer | Read | Read  | Write | Write |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls  |  Gets  | Reqs | Bytes | Reqs  | Bytes |
============================================================================================
|      17 |      14 |     1.76 |     1.72 |  10001 |   216K | 6878 |   3GB |  5169 |   1GB |
============================================================================================

SQL Plan Monitoring Details (Plan Hash Value=2223315184)
==============================================================================================================================================================================================================
| Id |      Operation       |           Name           |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read | Read  | Write | Write |  Mem  | Temp  | Activity |         Activity Detail         |
|    |                      |                          | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs | Bytes | Reqs  | Bytes | (Max) | (Max) |   (%)    |           (# samples)           |
==============================================================================================================================================================================================================
|  0 | SELECT STATEMENT     |                          |         |       |       145 |     +2 |     1 |       1M |      |       |       |       |     . |     . |          |                                 |
|  1 |   HASH JOIN OUTER    |                          |      1M |  119K |       146 |     +1 |     1 |       1M | 5169 |   1GB |  5169 |   1GB | 184MB |   1GB |   100.00 | Cpu (14)                        |
|    |                      |                          |         |       |           |        |       |          |      |       |       |       |       |       |          | SQL*Net more data to client (4) |
|  2 |    TABLE ACCESS FULL | SUPERTYPE                |      1M | 19593 |         1 |     +2 |     1 |       1M |  575 | 559MB |       |       |     . |     . |          |                                 |
|  3 |    TABLE ACCESS FULL | NIKOICHI_MITAINA_SUBTYPE |    750K | 38796 |        42 |     +2 |     1 |       1M | 1134 |   1GB |       |       |     . |     . |          |                                 |
==============================================================================================================================================================================================================

-Fastオプションなしの場合と比較してみるとSQL*Net roundtrips to/from clientなど減ってますよね:)
このround tripは、待機イベントSQL*Net more data to clientなどで現れます。(SQL監視のActivity Detailsにも現れていますので、覚えておくと良いと思います)

-Fastオプションなし（auto trace)

1551273711  bytes sent via SQL*Net to client
    735146  bytes received via SQL*Net from client
     66668  SQL*Net roundtrips to/from client

-Fastオプションあり（auto trace)

1539940308  bytes sent via SQL*Net to client
    110262  bytes received via SQL*Net from client
     10001  SQL*Net roundtrips to/from client

-Fastオプションなし（SQL監視)

Global Information
------------------------------
...略...
Duration            :  173s
...略...
Fetch Calls         :  66668

-Fastオプションあり（auto trace)

Global Information
------------------------------
...略...
Dration             :  146s
...略...
Fetch Calls         :  10001

早く、ポカポカ陽気にならないかなぁ。

と思う寒い日々。

では、また。