Oracle 問合せ変換① OR拡張

概要

Oracleの問合せ変換の中の「OR拡張(Or Expansion)」について記載します。

目的

WHERE句にORを含むSQLを1つまたは複数のUNION ALL集合演算子を使用する複合問合せに変換することで、追加の索引アクセス・パスを有効にするために適用される変換

実行例

OR拡張クエリ

SELECT 
 SUM (amount_sold) cnt
 FROM sh.sales JOIN sh.products USING (prod_id)
WHERE time_id = DATE '1998-03-31' OR prod_name = 'Y Box';
----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |                 |     1 |    13 |   469   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE                                |                 |     1 |    13 |            |          |       |       |
|   2 |   VIEW                                         | VW_ORE_3E51BB93 | 13941 |   176K|   469   (0)| 00:00:01 |       |       |
|   3 |    UNION-ALL                                   |                 |       |       |            |          |       |       |
|   4 |     NESTED LOOPS                               |                 |  1188 | 24948 |    24   (0)| 00:00:01 |       |       |
|   5 |      PARTITION RANGE SINGLE                    |                 |  1188 | 20196 |    24   (0)| 00:00:01 |     5 |     5 |
|   6 |       TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES           |  1188 | 20196 |    24   (0)| 00:00:01 |     5 |     5 |
|   7 |        BITMAP CONVERSION TO ROWIDS             |                 |       |       |            |          |       |       |
|*  8 |         BITMAP INDEX SINGLE VALUE              | SALES_TIME_BIX  |       |       |            |          |     5 |     5 |
|*  9 |      INDEX UNIQUE SCAN                         | PRODUCTS_PK     |     1 |     4 |     0   (0)| 00:00:01 |       |       |
|  10 |     NESTED LOOPS                               |                 | 12753 |   585K|   445   (0)| 00:00:01 |       |       |
|  11 |      NESTED LOOPS                              |                 | 12753 |   585K|   445   (0)| 00:00:01 |       |       |
|* 12 |       TABLE ACCESS FULL                        | PRODUCTS        |     1 |    30 |     3   (0)| 00:00:01 |       |       |
|  13 |       PARTITION RANGE ALL                      |                 |       |       |            |          |     1 |    28 |
|  14 |        BITMAP CONVERSION TO ROWIDS             |                 |       |       |            |          |       |       |
|* 15 |         BITMAP INDEX SINGLE VALUE              | SALES_PROD_BIX  |       |       |            |          |     1 |    28 |
|* 16 |      TABLE ACCESS BY LOCAL INDEX ROWID         | SALES           | 12753 |   211K|   445   (0)| 00:00:01 |     1 |     1 |
----------------------------------------------------------------------------------------------------------------------------------

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

   8 - access("SALES"."TIME_ID"=TO_DATE(' 1998-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   9 - access("SALES"."PROD_ID"="PRODUCTS"."PROD_ID")
  12 - filter("PRODUCTS"."PROD_NAME"='Y Box')
  15 - access("SALES"."PROD_ID"="PRODUCTS"."PROD_ID")
  16 - filter(LNNVL("SALES"."TIME_ID"=TO_DATE(' 1998-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

★12cR2から機能拡張され、実行計画上も「UNION-ALL」と出力されるようになりました。
12cR1までは、「CONCATENATION(連結)演算子」と出力されます(後述)

変換(OR拡張)されていない実行計画

SELECT /*+ no_expand */
      SUM (amount_sold) cnt
 FROM sh.sales JOIN sh.products USING (prod_id)
WHERE time_id = DATE '1998-03-31' OR prod_name = 'Y Box';
--------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |    47 |   523   (2)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE       |          |     1 |    47 |            |          |       |       |
|*  2 |   HASH JOIN           |          | 13382 |   614K|   523   (2)| 00:00:01 |       |       |
|   3 |    TABLE ACCESS FULL  | PRODUCTS |    72 |  2160 |     3   (0)| 00:00:01 |       |       |
|   4 |    PARTITION RANGE ALL|          |   918K|    14M|   517   (2)| 00:00:01 |     1 |    28 |
|   5 |     TABLE ACCESS FULL | SALES    |   918K|    14M|   517   (2)| 00:00:01 |     1 |    28 |
--------------------------------------------------------------------------------------------------

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

   2 - access("SALES"."PROD_ID"="PRODUCTS"."PROD_ID")
       filter("SALES"."TIME_ID"=TO_DATE(' 1998-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
              OR "PRODUCTS"."PROD_NAME"='Y Box')

USE_CONCATヒント付き

SELECT /*+ use_concat */
      SUM (amount_sold) cnt
 FROM sh.sales JOIN sh.products USING (prod_id)
WHERE time_id = DATE '1998-03-31' OR prod_name = 'Y Box';

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |                |     1 |    47 |   472   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE                               |                |     1 |    47 |            |          |       |       |
|   2 |   CONCATENATION                               |                |       |       |            |          |       |       |
|   3 |    NESTED LOOPS                               |                | 12762 |   585K|   445   (0)| 00:00:01 |       |       |
|   4 |     NESTED LOOPS                              |                | 12762 |   585K|   445   (0)| 00:00:01 |       |       |
|*  5 |      TABLE ACCESS FULL                        | PRODUCTS       |     1 |    30 |     3   (0)| 00:00:01 |       |       |
|   6 |      PARTITION RANGE ALL                      |                |       |       |            |          |     1 |    28 |
|   7 |       BITMAP CONVERSION TO ROWIDS             |                |       |       |            |          |       |       |
|*  8 |        BITMAP INDEX SINGLE VALUE              | SALES_PROD_BIX |       |       |            |          |     1 |    28 |
|   9 |     TABLE ACCESS BY LOCAL INDEX ROWID         | SALES          | 12762 |   211K|   445   (0)| 00:00:01 |     1 |     1 |
|* 10 |    HASH JOIN                                  |                |  1188 | 55836 |    27   (0)| 00:00:01 |       |       |
|* 11 |     TABLE ACCESS FULL                         | PRODUCTS       |    71 |  2130 |     3   (0)| 00:00:01 |       |       |
|  12 |     PARTITION RANGE SINGLE                    |                |  1188 | 20196 |    24   (0)| 00:00:01 |     5 |     5 |
|  13 |      TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES          |  1188 | 20196 |    24   (0)| 00:00:01 |     5 |     5 |
|  14 |       BITMAP CONVERSION TO ROWIDS             |                |       |       |            |          |       |       |
|* 15 |        BITMAP INDEX SINGLE VALUE              | SALES_TIME_BIX |       |       |            |          |     5 |     5 |
--------------------------------------------------------------------------------------------------------------------------------

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

   5 - filter("PRODUCTS"."PROD_NAME"='Y Box')
   8 - access("SALES"."PROD_ID"="PRODUCTS"."PROD_ID")
  10 - access("SALES"."PROD_ID"="PRODUCTS"."PROD_ID")
  11 - filter(LNNVL("PRODUCTS"."PROD_NAME"='Y Box'))
  15 - access("SALES"."TIME_ID"=TO_DATE(' 1998-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

★USE_CONCATヒントを使用した場合、12cR1と同様「CONCATENATION(連結)演算子」と出力されます。

最適化後のSQL(10053)

SELECT
    SUM("VW_ORE_3E51BB93"."ITEM_1") "CNT"
FROM
    ((
            SELECT
                "SALES"."AMOUNT_SOLD" "ITEM_1"
            FROM
                "SH"."PRODUCTS" "PRODUCTS",
                "SH"."SALES" "SALES"
            WHERE
                "SALES"."TIME_ID" = TO_DATE(' 1998-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
            AND "SALES"."PROD_ID" = "PRODUCTS"."PROD_ID"
        ) UNION ALL(
        SELECT
            "SALES"."AMOUNT_SOLD" "ITEM_1"
        FROM
            "SH"."PRODUCTS" "PRODUCTS",
            "SH"."SALES" "SALES"
        WHERE
            "PRODUCTS"."PROD_NAME" = 'Y Box'
        AND "SALES"."PROD_ID" = "PRODUCTS"."PROD_ID"
        AND LNNVL(
                "SALES"."TIME_ID" = TO_DATE(' 1998-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
            )
    )) "VW_ORE_3E51BB93"

参考資料

Oracle® Database SQLチューニング・ガイド 12cリリース2 (12.2)
https://docs.oracle.com/cd/E82638_01/TGSQL/toc.htm
http://www.oracle.com/technetwork/jp/database/articles/tsushima/tsushima-hakushi-60-3763796-ja.html
津島博士のパフォーマンス講座　第60回　Oracle Database 12cR2で強化されたSQL処理について
Troubleshooting Oracle Performance, Second Edition

Troubleshooting Oracle Performance

Expert Oracle SQL

Expert Oracle SQL: Optimization, Deployment, and Statistics

ASH(Active Session History)をCSV形式で出力

概要

現在、自分達DBAチームが管理している環境は、以下の環境があるため、それぞれについて、メモします。
- 11.2.0.4 RAC
- 12.1.0.2 RAC
また、以下の情報についてもそれぞれ記載します。
- ASHバッファの1秒間隔の情報:GV$～
- 10秒間隔でサマリした情報(SYSAUX内のテーブル格納):DBA_HIST～

使用上の注意

WHERE句の「SAMPLE_TIME」を情報を取得したい時間に変更して使っています。
※例の「2018/05/08 11:00:00」と「2018/05/08 12:00:00」部分
実際に使用する際は、環境数も多いためDBID等を指定して使っています。

11.2.0.4 RAC環境用(GV$)

SET LINESIZE 32767;
SET PAGESIZE 0;
SET FEED OFF;
SET TRIMSPOOL ON;
SET TERMOUT OFF;
SET ARRAYSIZE 5000;
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY/MM/DD HH24:MI:SS';
spool gv_ash_11204.csv
PROMPT "Y","M","D","H","MI","S","MS","INST_ID","SAMPLE_ID","SAMPLE_TIME","SAMPLE_TIME2","IS_AWR_SAMPLE","SESSION_ID","SESSION_SERIAL#","SESSION_TYPE","FLAGS","USER_ID","SQL_ID","IS_SQLID_CURRENT","SQL_CHILD_NUMBER","SQL_OPCODE","SQL_OPNAME","FORCE_MATCHING_SIGNATURE","TOP_LEVEL_SQL_ID","TOP_LEVEL_SQL_OPCODE","SQL_PLAN_HASH_VALUE","SQL_PLAN_LINE_ID","SQL_PLAN_OPERATION","SQL_PLAN_OPTIONS","SQL_EXEC_ID","SQL_EXEC_START","PLSQL_ENTRY_OBJECT_ID","PLSQL_ENTRY_SUBPROGRAM_ID","PLSQL_OBJECT_ID","PLSQL_SUBPROGRAM_ID","QC_INSTANCE_ID","QC_SESSION_ID","QC_SESSION_SERIAL#","PX_FLAGS","EVENT","EVENT_ID","EVENT#","SEQ#","P1TEXT","P1","P2TEXT","P2","P3TEXT","P3","WAIT_CLASS","WAIT_CLASS_ID","WAIT_TIME","SESSION_STATE","TIME_WAITED","BLOCKING_SESSION_STATUS","BLOCKING_SESSION","BLOCKING_SESSION_SERIAL#","BLOCKING_INST_ID","BLOCKING_HANGCHAIN_INFO","CURRENT_OBJ#","CURRENT_FILE#","CURRENT_BLOCK#","CURRENT_ROW#","TOP_LEVEL_CALL#","TOP_LEVEL_CALL_NAME","CONSUMER_GROUP_ID","XID","REMOTE_INSTANCE#","TIME_MODEL","IN_CONNECTION_MGMT","IN_PARSE","IN_HARD_PARSE","IN_SQL_EXECUTION","IN_PLSQL_EXECUTION","IN_PLSQL_RPC","IN_PLSQL_COMPILATION","IN_JAVA_EXECUTION","IN_BIND","IN_CURSOR_CLOSE","IN_SEQUENCE_LOAD","CAPTURE_OVERHEAD","REPLAY_OVERHEAD","IS_CAPTURED","IS_REPLAYED","SERVICE_HASH","PROGRAM","MODULE","ACTION","CLIENT_ID","MACHINE","PORT","ECID","DBREPLAY_FILE_ID","DBREPLAY_CALL_COUNTER","TM_DELTA_TIME","TM_DELTA_CPU_TIME","TM_DELTA_DB_TIME","DELTA_TIME","DELTA_READ_IO_REQUESTS","DELTA_WRITE_IO_REQUESTS","DELTA_READ_IO_BYTES","DELTA_WRITE_IO_BYTES","DELTA_INTERCONNECT_IO_BYTES","PGA_ALLOCATED","TEMP_SPACE_ALLOCATED"
SELECT
  '"' || TO_CHAR(SAMPLE_TIME, 'YYYY') || '",' ||
  '"' || TO_CHAR(SAMPLE_TIME, 'MM')   || '",' ||
  '"' || TO_CHAR(SAMPLE_TIME, 'DD')   || '",' ||
  '"' || TO_CHAR(SAMPLE_TIME, 'HH24') || '",' ||
  '"' || TO_CHAR(SAMPLE_TIME, 'MI')   || '",' ||
  '"' || TO_CHAR(SAMPLE_TIME, 'SS')   || '",' ||
  '"' || TO_CHAR(SAMPLE_TIME, 'FF3')  || '",' ||
  '"' || INST_ID                      || '",' ||
  '"' || SAMPLE_ID                    || '",' ||
  '"' || SAMPLE_TIME                  || '",' ||
  '"' || TO_CHAR(SAMPLE_TIME, 'YYYY/MM/DD HH24:MI:SS.FF3') || '",' ||
  '"' || IS_AWR_SAMPLE                || '",' ||
  '"' || SESSION_ID                   || '",' ||
  '"' || SESSION_SERIAL#              || '",' ||
  '"' || SESSION_TYPE                 || '",' ||
  '"' || FLAGS                        || '",' ||
  '"' || USER_ID                      || '",' ||
  '"' || SQL_ID                       || '",' ||
  '"' || IS_SQLID_CURRENT             || '",' ||
  '"' || SQL_CHILD_NUMBER             || '",' ||
  '"' || SQL_OPCODE                   || '",' ||
  '"' || SQL_OPNAME                   || '",' ||
  '"' || FORCE_MATCHING_SIGNATURE     || '",' ||
  '"' || TOP_LEVEL_SQL_ID             || '",' ||
  '"' || TOP_LEVEL_SQL_OPCODE         || '",' ||
  '"' || SQL_PLAN_HASH_VALUE          || '",' ||
  '"' || SQL_PLAN_LINE_ID             || '",' ||
  '"' || SQL_PLAN_OPERATION           || '",' ||
  '"' || SQL_PLAN_OPTIONS             || '",' ||
  '"' || SQL_EXEC_ID                  || '",' ||
  '"' || SQL_EXEC_START               || '",' ||
  '"' || PLSQL_ENTRY_OBJECT_ID        || '",' ||
  '"' || PLSQL_ENTRY_SUBPROGRAM_ID    || '",' ||
  '"' || PLSQL_OBJECT_ID              || '",' ||
  '"' || PLSQL_SUBPROGRAM_ID          || '",' ||
  '"' || QC_INSTANCE_ID               || '",' ||
  '"' || QC_SESSION_ID                || '",' ||
  '"' || QC_SESSION_SERIAL#           || '",' ||
  '"' || PX_FLAGS                     || '",' ||
  '"' || EVENT                        || '",' ||
  '"' || EVENT_ID                     || '",' ||
  '"' || EVENT#                       || '",' ||
  '"' || SEQ#                         || '",' ||
  '"' || P1TEXT                       || '",' ||
  '"' || P1                           || '",' ||
  '"' || P2TEXT                       || '",' ||
  '"' || P2                           || '",' ||
  '"' || P3TEXT                       || '",' ||
  '"' || P3                           || '",' ||
  '"' || WAIT_CLASS                   || '",' ||
  '"' || WAIT_CLASS_ID                || '",' ||
  '"' || WAIT_TIME                    || '",' ||
  '"' || SESSION_STATE                || '",' ||
  '"' || TIME_WAITED                  || '",' ||
  '"' || BLOCKING_SESSION_STATUS      || '",' ||
  '"' || BLOCKING_SESSION             || '",' ||
  '"' || BLOCKING_SESSION_SERIAL#     || '",' ||
  '"' || BLOCKING_INST_ID             || '",' ||
  '"' || BLOCKING_HANGCHAIN_INFO      || '",' ||
  '"' || CURRENT_OBJ#                 || '",' ||
  '"' || CURRENT_FILE#                || '",' ||
  '"' || CURRENT_BLOCK#               || '",' ||
  '"' || CURRENT_ROW#                 || '",' ||
  '"' || TOP_LEVEL_CALL#              || '",' ||
  '"' || TOP_LEVEL_CALL_NAME          || '",' ||
  '"' || CONSUMER_GROUP_ID            || '",' ||
  '"' || XID                          || '",' ||
  '"' || REMOTE_INSTANCE#             || '",' ||
  '"' || TIME_MODEL                   || '",' ||
  '"' || IN_CONNECTION_MGMT           || '",' ||
  '"' || IN_PARSE                     || '",' ||
  '"' || IN_HARD_PARSE                || '",' ||
  '"' || IN_SQL_EXECUTION             || '",' ||
  '"' || IN_PLSQL_EXECUTION           || '",' ||
  '"' || IN_PLSQL_RPC                 || '",' ||
  '"' || IN_PLSQL_COMPILATION         || '",' ||
  '"' || IN_JAVA_EXECUTION            || '",' ||
  '"' || IN_BIND                      || '",' ||
  '"' || IN_CURSOR_CLOSE              || '",' ||
  '"' || IN_SEQUENCE_LOAD             || '",' ||
  '"' || CAPTURE_OVERHEAD             || '",' ||
  '"' || REPLAY_OVERHEAD              || '",' ||
  '"' || IS_CAPTURED                  || '",' ||
  '"' || IS_REPLAYED                  || '",' ||
  '"' || SERVICE_HASH                 || '",' ||
  '"' || PROGRAM                      || '",' ||
  '"' || MODULE                       || '",' ||
  '"' || ACTION                       || '",' ||
  '"' || CLIENT_ID                    || '",' ||
  '"' || MACHINE                      || '",' ||
  '"' || PORT                         || '",' ||
  '"' || ECID                         || '",' ||
  '"' || DBREPLAY_FILE_ID             || '",' ||
  '"' || DBREPLAY_CALL_COUNTER        || '",' ||
  '"' || TM_DELTA_TIME                || '",' ||
  '"' || TM_DELTA_CPU_TIME            || '",' ||
  '"' || TM_DELTA_DB_TIME             || '",' ||
  '"' || DELTA_TIME                   || '",' ||
  '"' || DELTA_READ_IO_REQUESTS       || '",' ||
  '"' || DELTA_WRITE_IO_REQUESTS      || '",' ||
  '"' || DELTA_READ_IO_BYTES          || '",' ||
  '"' || DELTA_WRITE_IO_BYTES         || '",' ||
  '"' || DELTA_INTERCONNECT_IO_BYTES  || '",' ||
  '"' || PGA_ALLOCATED                || '",' ||
  '"' || TEMP_SPACE_ALLOCATED         || '"'
  FROM GV$ACTIVE_SESSION_HISTORY
 WHERE SAMPLE_TIME >= TO_TIMESTAMP('2018/05/08 11:00:00', 'YYYY/MM/DD HH24:MI:SS')
   AND SAMPLE_TIME <  TO_TIMESTAMP('2018/05/08 12:00:00', 'YYYY/MM/DD HH24:MI:SS')
/
spool off;
SET TERMOUT ON;

11.2.0.4 RAC環境用(DBA_HIST～)

SET LINESIZE 32767;
SET PAGESIZE 0;
SET FEED OFF;
SET TRIMSPOOL ON;
SET TERMOUT OFF;
SET ARRAYSIZE 5000;
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY/MM/DD HH24:MI:SS';
spool dba_hist_ash_11204.csv
PROMPT "Y","M","D","H","MI","S","MS","SNAP_ID","DBID","INSTANCE_NUMBER","SAMPLE_ID","SAMPLE_TIME","SAMPLE_TIME2","SESSION_ID","SESSION_SERIAL#","SESSION_TYPE","FLAGS","USER_ID","SQL_ID","IS_SQLID_CURRENT","SQL_CHILD_NUMBER","SQL_OPCODE","SQL_OPNAME","FORCE_MATCHING_SIGNATURE","TOP_LEVEL_SQL_ID","TOP_LEVEL_SQL_OPCODE","SQL_PLAN_HASH_VALUE","SQL_PLAN_LINE_ID","SQL_PLAN_OPERATION","SQL_PLAN_OPTIONS","SQL_EXEC_ID","SQL_EXEC_START","PLSQL_ENTRY_OBJECT_ID","PLSQL_ENTRY_SUBPROGRAM_ID","PLSQL_OBJECT_ID","PLSQL_SUBPROGRAM_ID","QC_INSTANCE_ID","QC_SESSION_ID","QC_SESSION_SERIAL#","PX_FLAGS","EVENT","EVENT_ID","SEQ#","P1TEXT","P1","P2TEXT","P2","P3TEXT","P3","WAIT_CLASS","WAIT_CLASS_ID","WAIT_TIME","SESSION_STATE","TIME_WAITED","BLOCKING_SESSION_STATUS","BLOCKING_SESSION","BLOCKING_SESSION_SERIAL#","BLOCKING_INST_ID","BLOCKING_HANGCHAIN_INFO","CURRENT_OBJ#","CURRENT_FILE#","CURRENT_BLOCK#","CURRENT_ROW#","TOP_LEVEL_CALL#","TOP_LEVEL_CALL_NAME","CONSUMER_GROUP_ID","XID","REMOTE_INSTANCE#","TIME_MODEL","IN_CONNECTION_MGMT","IN_PARSE","IN_HARD_PARSE","IN_SQL_EXECUTION","IN_PLSQL_EXECUTION","IN_PLSQL_RPC","IN_PLSQL_COMPILATION","IN_JAVA_EXECUTION","IN_BIND","IN_CURSOR_CLOSE","IN_SEQUENCE_LOAD","CAPTURE_OVERHEAD","REPLAY_OVERHEAD","IS_CAPTURED","IS_REPLAYED","SERVICE_HASH","PROGRAM","MODULE","ACTION","CLIENT_ID","MACHINE","PORT","ECID","DBREPLAY_FILE_ID","DBREPLAY_CALL_COUNTER","TM_DELTA_TIME","TM_DELTA_CPU_TIME","TM_DELTA_DB_TIME","DELTA_TIME","DELTA_READ_IO_REQUESTS","DELTA_WRITE_IO_REQUESTS","DELTA_READ_IO_BYTES","DELTA_WRITE_IO_BYTES","DELTA_INTERCONNECT_IO_BYTES","PGA_ALLOCATED","TEMP_SPACE_ALLOCATED"
SELECT
  '"' || TO_CHAR(SAMPLE_TIME, 'YYYY') || '",' ||
  '"' || TO_CHAR(SAMPLE_TIME, 'MM')   || '",' ||
  '"' || TO_CHAR(SAMPLE_TIME, 'DD')   || '",' ||
  '"' || TO_CHAR(SAMPLE_TIME, 'HH24') || '",' ||
  '"' || TO_CHAR(SAMPLE_TIME, 'MI')   || '",' ||
  '"' || TO_CHAR(SAMPLE_TIME, 'SS')   || '",' ||
  '"' || TO_CHAR(SAMPLE_TIME, 'FF3')  || '",' ||
  '"' || SNAP_ID                      || '",' ||
  '"' || DBID                         || '",' ||
  '"' || INSTANCE_NUMBER              || '",' ||
  '"' || SAMPLE_ID                    || '",' ||
  '"' || SAMPLE_TIME                  || '",' ||
  '"' || TO_CHAR(SAMPLE_TIME, 'YYYY/MM/DD HH24:MI:SS.FF3') || '",' ||
  '"' || SESSION_ID                   || '",' ||
  '"' || SESSION_SERIAL#              || '",' ||
  '"' || SESSION_TYPE                 || '",' ||
  '"' || FLAGS                        || '",' ||
  '"' || USER_ID                      || '",' ||
  '"' || SQL_ID                       || '",' ||
  '"' || IS_SQLID_CURRENT             || '",' ||
  '"' || SQL_CHILD_NUMBER             || '",' ||
  '"' || SQL_OPCODE                   || '",' ||
  '"' || SQL_OPNAME                   || '",' ||
  '"' || FORCE_MATCHING_SIGNATURE     || '",' ||
  '"' || TOP_LEVEL_SQL_ID             || '",' ||
  '"' || TOP_LEVEL_SQL_OPCODE         || '",' ||
  '"' || SQL_PLAN_HASH_VALUE          || '",' ||
  '"' || SQL_PLAN_LINE_ID             || '",' ||
  '"' || SQL_PLAN_OPERATION           || '",' ||
  '"' || SQL_PLAN_OPTIONS             || '",' ||
  '"' || SQL_EXEC_ID                  || '",' ||
  '"' || SQL_EXEC_START               || '",' ||
  '"' || PLSQL_ENTRY_OBJECT_ID        || '",' ||
  '"' || PLSQL_ENTRY_SUBPROGRAM_ID    || '",' ||
  '"' || PLSQL_OBJECT_ID              || '",' ||
  '"' || PLSQL_SUBPROGRAM_ID          || '",' ||
  '"' || QC_INSTANCE_ID               || '",' ||
  '"' || QC_SESSION_ID                || '",' ||
  '"' || QC_SESSION_SERIAL#           || '",' ||
  '"' || PX_FLAGS                     || '",' ||
  '"' || EVENT                        || '",' ||
  '"' || EVENT_ID                     || '",' ||
  '"' || SEQ#                         || '",' ||
  '"' || P1TEXT                       || '",' ||
  '"' || P1                           || '",' ||
  '"' || P2TEXT                       || '",' ||
  '"' || P2                           || '",' ||
  '"' || P3TEXT                       || '",' ||
  '"' || P3                           || '",' ||
  '"' || WAIT_CLASS                   || '",' ||
  '"' || WAIT_CLASS_ID                || '",' ||
  '"' || WAIT_TIME                    || '",' ||
  '"' || SESSION_STATE                || '",' ||
  '"' || TIME_WAITED                  || '",' ||
  '"' || BLOCKING_SESSION_STATUS      || '",' ||
  '"' || BLOCKING_SESSION             || '",' ||
  '"' || BLOCKING_SESSION_SERIAL#     || '",' ||
  '"' || BLOCKING_INST_ID             || '",' ||
  '"' || BLOCKING_HANGCHAIN_INFO      || '",' ||
  '"' || CURRENT_OBJ#                 || '",' ||
  '"' || CURRENT_FILE#                || '",' ||
  '"' || CURRENT_BLOCK#               || '",' ||
  '"' || CURRENT_ROW#                 || '",' ||
  '"' || TOP_LEVEL_CALL#              || '",' ||
  '"' || TOP_LEVEL_CALL_NAME          || '",' ||
  '"' || CONSUMER_GROUP_ID            || '",' ||
  '"' || XID                          || '",' ||
  '"' || REMOTE_INSTANCE#             || '",' ||
  '"' || TIME_MODEL                   || '",' ||
  '"' || IN_CONNECTION_MGMT           || '",' ||
  '"' || IN_PARSE                     || '",' ||
  '"' || IN_HARD_PARSE                || '",' ||
  '"' || IN_SQL_EXECUTION             || '",' ||
  '"' || IN_PLSQL_EXECUTION           || '",' ||
  '"' || IN_PLSQL_RPC                 || '",' ||
  '"' || IN_PLSQL_COMPILATION         || '",' ||
  '"' || IN_JAVA_EXECUTION            || '",' ||
  '"' || IN_BIND                      || '",' ||
  '"' || IN_CURSOR_CLOSE              || '",' ||
  '"' || IN_SEQUENCE_LOAD             || '",' ||
  '"' || CAPTURE_OVERHEAD             || '",' ||
  '"' || REPLAY_OVERHEAD              || '",' ||
  '"' || IS_CAPTURED                  || '",' ||
  '"' || IS_REPLAYED                  || '",' ||
  '"' || SERVICE_HASH                 || '",' ||
  '"' || PROGRAM                      || '",' ||
  '"' || MODULE                       || '",' ||
  '"' || ACTION                       || '",' ||
  '"' || CLIENT_ID                    || '",' ||
  '"' || MACHINE                      || '",' ||
  '"' || PORT                         || '",' ||
  '"' || ECID                         || '",' ||
  '"' || DBREPLAY_FILE_ID             || '",' ||
  '"' || DBREPLAY_CALL_COUNTER        || '",' ||
  '"' || TM_DELTA_TIME                || '",' ||
  '"' || TM_DELTA_CPU_TIME            || '",' ||
  '"' || TM_DELTA_DB_TIME             || '",' ||
  '"' || DELTA_TIME                   || '",' ||
  '"' || DELTA_READ_IO_REQUESTS       || '",' ||
  '"' || DELTA_WRITE_IO_REQUESTS      || '",' ||
  '"' || DELTA_READ_IO_BYTES          || '",' ||
  '"' || DELTA_WRITE_IO_BYTES         || '",' ||
  '"' || DELTA_INTERCONNECT_IO_BYTES  || '",' ||
  '"' || PGA_ALLOCATED                || '",' ||
  '"' || TEMP_SPACE_ALLOCATED         || '"'
  FROM DBA_HIST_ACTIVE_SESS_HISTORY
 WHERE SAMPLE_TIME >= TO_TIMESTAMP('2018/05/08 11:00:00', 'YYYY/MM/DD HH24:MI:SS')
   AND SAMPLE_TIME <  TO_TIMESTAMP('2018/05/08 12:00:00', 'YYYY/MM/DD HH24:MI:SS')
/
spool off;
SET TERMOUT ON;

12.1.0.2 RAC環境用(GV$)

SET LINESIZE 32767;
SET PAGESIZE 0;
SET FEED OFF;
SET TRIMSPOOL ON;
SET TERMOUT OFF;
SET ARRAYSIZE 5000;
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY/MM/DD HH24:MI:SS';
spool gv_ash_12102.csv
PROMPT "Y","M","D","H","MI","S","MS","SAMPLE_ID","SAMPLE_TIME","SAMPLE_TIME2","IS_AWR_SAMPLE","SESSION_ID","SESSION_SERIAL#","SESSION_TYPE","FLAGS","USER_ID","SQL_ID","IS_SQLID_CURRENT","SQL_CHILD_NUMBER","SQL_OPCODE","SQL_OPNAME","FORCE_MATCHING_SIGNATURE","TOP_LEVEL_SQL_ID","TOP_LEVEL_SQL_OPCODE","SQL_PLAN_HASH_VALUE","SQL_PLAN_LINE_ID","SQL_PLAN_OPERATION","SQL_PLAN_OPTIONS","SQL_EXEC_ID","SQL_EXEC_START","PLSQL_ENTRY_OBJECT_ID","PLSQL_ENTRY_SUBPROGRAM_ID","PLSQL_OBJECT_ID","PLSQL_SUBPROGRAM_ID","QC_INSTANCE_ID","QC_SESSION_ID","QC_SESSION_SERIAL#","PX_FLAGS","EVENT","EVENT_ID","EVENT#","SEQ#","P1TEXT","P1","P2TEXT","P2","P3TEXT","P3","WAIT_CLASS","WAIT_CLASS_ID","WAIT_TIME","SESSION_STATE","TIME_WAITED","BLOCKING_SESSION_STATUS","BLOCKING_SESSION","BLOCKING_SESSION_SERIAL#","BLOCKING_INST_ID","BLOCKING_HANGCHAIN_INFO","CURRENT_OBJ#","CURRENT_FILE#","CURRENT_BLOCK#","CURRENT_ROW#","TOP_LEVEL_CALL#","TOP_LEVEL_CALL_NAME","CONSUMER_GROUP_ID","XID","REMOTE_INSTANCE#","TIME_MODEL","IN_CONNECTION_MGMT","IN_PARSE","IN_HARD_PARSE","IN_SQL_EXECUTION","IN_PLSQL_EXECUTION","IN_PLSQL_RPC","IN_PLSQL_COMPILATION","IN_JAVA_EXECUTION","IN_BIND","IN_CURSOR_CLOSE","IN_SEQUENCE_LOAD","CAPTURE_OVERHEAD","REPLAY_OVERHEAD","IS_CAPTURED","IS_REPLAYED","SERVICE_HASH","PROGRAM","MODULE","ACTION","CLIENT_ID","MACHINE","PORT","ECID","DBREPLAY_FILE_ID","DBREPLAY_CALL_COUNTER","TM_DELTA_TIME","TM_DELTA_CPU_TIME","TM_DELTA_DB_TIME","DELTA_TIME","DELTA_READ_IO_REQUESTS","DELTA_WRITE_IO_REQUESTS","DELTA_READ_IO_BYTES","DELTA_WRITE_IO_BYTES","DELTA_INTERCONNECT_IO_BYTES","PGA_ALLOCATED","TEMP_SPACE_ALLOCATED","CON_DBID","CON_ID","DBOP_NAME","DBOP_EXEC_ID"
SELECT
  '"' || TO_CHAR(SAMPLE_TIME, 'YYYY') || '",' ||
  '"' || TO_CHAR(SAMPLE_TIME, 'MM')   || '",' ||
  '"' || TO_CHAR(SAMPLE_TIME, 'DD')   || '",' ||
  '"' || TO_CHAR(SAMPLE_TIME, 'HH24') || '",' ||
  '"' || TO_CHAR(SAMPLE_TIME, 'MI')   || '",' ||
  '"' || TO_CHAR(SAMPLE_TIME, 'SS')   || '",' ||
  '"' || TO_CHAR(SAMPLE_TIME, 'FF3')  || '",' ||
  '"' || INST_ID                      || '",' ||
  '"' || SAMPLE_ID                    || '",' ||
  '"' || SAMPLE_TIME                  || '",' ||
  '"' || TO_CHAR(SAMPLE_TIME, 'YYYY/MM/DD HH24:MI:SS.FF3') || '",' ||
  '"' || IS_AWR_SAMPLE                || '",' ||
  '"' || SESSION_ID                   || '",' ||
  '"' || SESSION_SERIAL#              || '",' ||
  '"' || SESSION_TYPE                 || '",' ||
  '"' || FLAGS                        || '",' ||
  '"' || USER_ID                      || '",' ||
  '"' || SQL_ID                       || '",' ||
  '"' || IS_SQLID_CURRENT             || '",' ||
  '"' || SQL_CHILD_NUMBER             || '",' ||
  '"' || SQL_OPCODE                   || '",' ||
  '"' || SQL_OPNAME                   || '",' ||
  '"' || FORCE_MATCHING_SIGNATURE     || '",' ||
  '"' || TOP_LEVEL_SQL_ID             || '",' ||
  '"' || TOP_LEVEL_SQL_OPCODE         || '",' ||
  '"' || SQL_PLAN_HASH_VALUE          || '",' ||
  '"' || SQL_PLAN_LINE_ID             || '",' ||
  '"' || SQL_PLAN_OPERATION           || '",' ||
  '"' || SQL_PLAN_OPTIONS             || '",' ||
  '"' || SQL_EXEC_ID                  || '",' ||
  '"' || SQL_EXEC_START               || '",' ||
  '"' || PLSQL_ENTRY_OBJECT_ID        || '",' ||
  '"' || PLSQL_ENTRY_SUBPROGRAM_ID    || '",' ||
  '"' || PLSQL_OBJECT_ID              || '",' ||
  '"' || PLSQL_SUBPROGRAM_ID          || '",' ||
  '"' || QC_INSTANCE_ID               || '",' ||
  '"' || QC_SESSION_ID                || '",' ||
  '"' || QC_SESSION_SERIAL#           || '",' ||
  '"' || PX_FLAGS                     || '",' ||
  '"' || EVENT                        || '",' ||
  '"' || EVENT_ID                     || '",' ||
  '"' || EVENT#                       || '",' ||
  '"' || SEQ#                         || '",' ||
  '"' || P1TEXT                       || '",' ||
  '"' || P1                           || '",' ||
  '"' || P2TEXT                       || '",' ||
  '"' || P2                           || '",' ||
  '"' || P3TEXT                       || '",' ||
  '"' || P3                           || '",' ||
  '"' || WAIT_CLASS                   || '",' ||
  '"' || WAIT_CLASS_ID                || '",' ||
  '"' || WAIT_TIME                    || '",' ||
  '"' || SESSION_STATE                || '",' ||
  '"' || TIME_WAITED                  || '",' ||
  '"' || BLOCKING_SESSION_STATUS      || '",' ||
  '"' || BLOCKING_SESSION             || '",' ||
  '"' || BLOCKING_SESSION_SERIAL#     || '",' ||
  '"' || BLOCKING_INST_ID             || '",' ||
  '"' || BLOCKING_HANGCHAIN_INFO      || '",' ||
  '"' || CURRENT_OBJ#                 || '",' ||
  '"' || CURRENT_FILE#                || '",' ||
  '"' || CURRENT_BLOCK#               || '",' ||
  '"' || CURRENT_ROW#                 || '",' ||
  '"' || TOP_LEVEL_CALL#              || '",' ||
  '"' || TOP_LEVEL_CALL_NAME          || '",' ||
  '"' || CONSUMER_GROUP_ID            || '",' ||
  '"' || XID                          || '",' ||
  '"' || REMOTE_INSTANCE#             || '",' ||
  '"' || TIME_MODEL                   || '",' ||
  '"' || IN_CONNECTION_MGMT           || '",' ||
  '"' || IN_PARSE                     || '",' ||
  '"' || IN_HARD_PARSE                || '",' ||
  '"' || IN_SQL_EXECUTION             || '",' ||
  '"' || IN_PLSQL_EXECUTION           || '",' ||
  '"' || IN_PLSQL_RPC                 || '",' ||
  '"' || IN_PLSQL_COMPILATION         || '",' ||
  '"' || IN_JAVA_EXECUTION            || '",' ||
  '"' || IN_BIND                      || '",' ||
  '"' || IN_CURSOR_CLOSE              || '",' ||
  '"' || IN_SEQUENCE_LOAD             || '",' ||
  '"' || CAPTURE_OVERHEAD             || '",' ||
  '"' || REPLAY_OVERHEAD              || '",' ||
  '"' || IS_CAPTURED                  || '",' ||
  '"' || IS_REPLAYED                  || '",' ||
  '"' || SERVICE_HASH                 || '",' ||
  '"' || PROGRAM                      || '",' ||
  '"' || MODULE                       || '",' ||
  '"' || ACTION                       || '",' ||
  '"' || CLIENT_ID                    || '",' ||
  '"' || MACHINE                      || '",' ||
  '"' || PORT                         || '",' ||
  '"' || ECID                         || '",' ||
  '"' || DBREPLAY_FILE_ID             || '",' ||
  '"' || DBREPLAY_CALL_COUNTER        || '",' ||
  '"' || TM_DELTA_TIME                || '",' ||
  '"' || TM_DELTA_CPU_TIME            || '",' ||
  '"' || TM_DELTA_DB_TIME             || '",' ||
  '"' || DELTA_TIME                   || '",' ||
  '"' || DELTA_READ_IO_REQUESTS       || '",' ||
  '"' || DELTA_WRITE_IO_REQUESTS      || '",' ||
  '"' || DELTA_READ_IO_BYTES          || '",' ||
  '"' || DELTA_WRITE_IO_BYTES         || '",' ||
  '"' || DELTA_INTERCONNECT_IO_BYTES  || '",' ||
  '"' || PGA_ALLOCATED                || '",' ||
  '"' || TEMP_SPACE_ALLOCATED         || '"'
  FROM GV$ACTIVE_SESSION_HISTORY
 WHERE SAMPLE_TIME >= TO_TIMESTAMP('2018/05/08 11:00:00', 'YYYY/MM/DD HH24:MI:SS')
   AND SAMPLE_TIME <  TO_TIMESTAMP('2018/05/08 12:00:00', 'YYYY/MM/DD HH24:MI:SS')
/
spool off;
SET TERMOUT ON;

12.1.0.2 RAC環境用(DBA_HIST～)

SET LINESIZE 32767;
SET PAGESIZE 0;
SET FEED OFF;
SET TRIMSPOOL ON;
SET TERMOUT OFF;
SET ARRAYSIZE 5000;
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY/MM/DD HH24:MI:SS';
spool dba_hist_ash_12102.csv
PROMPT "Y","M","D","H","MI","S","MS","SNAP_ID","DBID","INSTANCE_NUMBER","SAMPLE_ID","SAMPLE_TIME","SAMPLE_TIME2","SESSION_ID","SESSION_SERIAL#","SESSION_TYPE","FLAGS","USER_ID","SQL_ID","IS_SQLID_CURRENT","SQL_CHILD_NUMBER","SQL_OPCODE","SQL_OPNAME","FORCE_MATCHING_SIGNATURE","TOP_LEVEL_SQL_ID","TOP_LEVEL_SQL_OPCODE","SQL_PLAN_HASH_VALUE","SQL_PLAN_LINE_ID","SQL_PLAN_OPERATION","SQL_PLAN_OPTIONS","SQL_EXEC_ID","SQL_EXEC_START","PLSQL_ENTRY_OBJECT_ID","PLSQL_ENTRY_SUBPROGRAM_ID","PLSQL_OBJECT_ID","PLSQL_SUBPROGRAM_ID","QC_INSTANCE_ID","QC_SESSION_ID","QC_SESSION_SERIAL#","PX_FLAGS","EVENT","EVENT_ID","SEQ#","P1TEXT","P1","P2TEXT","P2","P3TEXT","P3","WAIT_CLASS","WAIT_CLASS_ID","WAIT_TIME","SESSION_STATE","TIME_WAITED","BLOCKING_SESSION_STATUS","BLOCKING_SESSION","BLOCKING_SESSION_SERIAL#","BLOCKING_INST_ID","BLOCKING_HANGCHAIN_INFO","CURRENT_OBJ#","CURRENT_FILE#","CURRENT_BLOCK#","CURRENT_ROW#","TOP_LEVEL_CALL#","TOP_LEVEL_CALL_NAME","CONSUMER_GROUP_ID","XID","REMOTE_INSTANCE#","TIME_MODEL","IN_CONNECTION_MGMT","IN_PARSE","IN_HARD_PARSE","IN_SQL_EXECUTION","IN_PLSQL_EXECUTION","IN_PLSQL_RPC","IN_PLSQL_COMPILATION","IN_JAVA_EXECUTION","IN_BIND","IN_CURSOR_CLOSE","IN_SEQUENCE_LOAD","CAPTURE_OVERHEAD","REPLAY_OVERHEAD","IS_CAPTURED","IS_REPLAYED","SERVICE_HASH","PROGRAM","MODULE","ACTION","CLIENT_ID","MACHINE","PORT","ECID","DBREPLAY_FILE_ID","DBREPLAY_CALL_COUNTER","TM_DELTA_TIME","TM_DELTA_CPU_TIME","TM_DELTA_DB_TIME","DELTA_TIME","DELTA_READ_IO_REQUESTS","DELTA_WRITE_IO_REQUESTS","DELTA_READ_IO_BYTES","DELTA_WRITE_IO_BYTES","DELTA_INTERCONNECT_IO_BYTES","PGA_ALLOCATED","TEMP_SPACE_ALLOCATED","CON_DBID","CON_ID","DBOP_NAME","DBOP_EXEC_ID"
SELECT
  '"' || TO_CHAR(SAMPLE_TIME, 'YYYY') || '",' ||
  '"' || TO_CHAR(SAMPLE_TIME, 'MM')   || '",' ||
  '"' || TO_CHAR(SAMPLE_TIME, 'DD')   || '",' ||
  '"' || TO_CHAR(SAMPLE_TIME, 'HH24') || '",' ||
  '"' || TO_CHAR(SAMPLE_TIME, 'MI')   || '",' ||
  '"' || TO_CHAR(SAMPLE_TIME, 'SS')   || '",' ||
  '"' || TO_CHAR(SAMPLE_TIME, 'FF3')  || '",' ||
  '"' || SNAP_ID                      || '",' ||
  '"' || DBID                         || '",' ||
  '"' || INSTANCE_NUMBER              || '",' ||
  '"' || SAMPLE_ID                    || '",' ||
  '"' || SAMPLE_TIME                  || '",' ||
  '"' || TO_CHAR(SAMPLE_TIME, 'YYYY/MM/DD HH24:MI:SS.FF3') || '",' ||
  '"' || SESSION_ID                   || '",' ||
  '"' || SESSION_SERIAL#              || '",' ||
  '"' || SESSION_TYPE                 || '",' ||
  '"' || FLAGS                        || '",' ||
  '"' || USER_ID                      || '",' ||
  '"' || SQL_ID                       || '",' ||
  '"' || IS_SQLID_CURRENT             || '",' ||
  '"' || SQL_CHILD_NUMBER             || '",' ||
  '"' || SQL_OPCODE                   || '",' ||
  '"' || SQL_OPNAME                   || '",' ||
  '"' || FORCE_MATCHING_SIGNATURE     || '",' ||
  '"' || TOP_LEVEL_SQL_ID             || '",' ||
  '"' || TOP_LEVEL_SQL_OPCODE         || '",' ||
  '"' || SQL_PLAN_HASH_VALUE          || '",' ||
  '"' || SQL_PLAN_LINE_ID             || '",' ||
  '"' || SQL_PLAN_OPERATION           || '",' ||
  '"' || SQL_PLAN_OPTIONS             || '",' ||
  '"' || SQL_EXEC_ID                  || '",' ||
  '"' || SQL_EXEC_START               || '",' ||
  '"' || PLSQL_ENTRY_OBJECT_ID        || '",' ||
  '"' || PLSQL_ENTRY_SUBPROGRAM_ID    || '",' ||
  '"' || PLSQL_OBJECT_ID              || '",' ||
  '"' || PLSQL_SUBPROGRAM_ID          || '",' ||
  '"' || QC_INSTANCE_ID               || '",' ||
  '"' || QC_SESSION_ID                || '",' ||
  '"' || QC_SESSION_SERIAL#           || '",' ||
  '"' || PX_FLAGS                     || '",' ||
  '"' || EVENT                        || '",' ||
  '"' || EVENT_ID                     || '",' ||
  '"' || SEQ#                         || '",' ||
  '"' || P1TEXT                       || '",' ||
  '"' || P1                           || '",' ||
  '"' || P2TEXT                       || '",' ||
  '"' || P2                           || '",' ||
  '"' || P3TEXT                       || '",' ||
  '"' || P3                           || '",' ||
  '"' || WAIT_CLASS                   || '",' ||
  '"' || WAIT_CLASS_ID                || '",' ||
  '"' || WAIT_TIME                    || '",' ||
  '"' || SESSION_STATE                || '",' ||
  '"' || TIME_WAITED                  || '",' ||
  '"' || BLOCKING_SESSION_STATUS      || '",' ||
  '"' || BLOCKING_SESSION             || '",' ||
  '"' || BLOCKING_SESSION_SERIAL#     || '",' ||
  '"' || BLOCKING_INST_ID             || '",' ||
  '"' || BLOCKING_HANGCHAIN_INFO      || '",' ||
  '"' || CURRENT_OBJ#                 || '",' ||
  '"' || CURRENT_FILE#                || '",' ||
  '"' || CURRENT_BLOCK#               || '",' ||
  '"' || CURRENT_ROW#                 || '",' ||
  '"' || TOP_LEVEL_CALL#              || '",' ||
  '"' || TOP_LEVEL_CALL_NAME          || '",' ||
  '"' || CONSUMER_GROUP_ID            || '",' ||
  '"' || XID                          || '",' ||
  '"' || REMOTE_INSTANCE#             || '",' ||
  '"' || TIME_MODEL                   || '",' ||
  '"' || IN_CONNECTION_MGMT           || '",' ||
  '"' || IN_PARSE                     || '",' ||
  '"' || IN_HARD_PARSE                || '",' ||
  '"' || IN_SQL_EXECUTION             || '",' ||
  '"' || IN_PLSQL_EXECUTION           || '",' ||
  '"' || IN_PLSQL_RPC                 || '",' ||
  '"' || IN_PLSQL_COMPILATION         || '",' ||
  '"' || IN_JAVA_EXECUTION            || '",' ||
  '"' || IN_BIND                      || '",' ||
  '"' || IN_CURSOR_CLOSE              || '",' ||
  '"' || IN_SEQUENCE_LOAD             || '",' ||
  '"' || CAPTURE_OVERHEAD             || '",' ||
  '"' || REPLAY_OVERHEAD              || '",' ||
  '"' || IS_CAPTURED                  || '",' ||
  '"' || IS_REPLAYED                  || '",' ||
  '"' || SERVICE_HASH                 || '",' ||
  '"' || PROGRAM                      || '",' ||
  '"' || MODULE                       || '",' ||
  '"' || ACTION                       || '",' ||
  '"' || CLIENT_ID                    || '",' ||
  '"' || MACHINE                      || '",' ||
  '"' || PORT                         || '",' ||
  '"' || ECID                         || '",' ||
  '"' || DBREPLAY_FILE_ID             || '",' ||
  '"' || DBREPLAY_CALL_COUNTER        || '",' ||
  '"' || TM_DELTA_TIME                || '",' ||
  '"' || TM_DELTA_CPU_TIME            || '",' ||
  '"' || TM_DELTA_DB_TIME             || '",' ||
  '"' || DELTA_TIME                   || '",' ||
  '"' || DELTA_READ_IO_REQUESTS       || '",' ||
  '"' || DELTA_WRITE_IO_REQUESTS      || '",' ||
  '"' || DELTA_READ_IO_BYTES          || '",' ||
  '"' || DELTA_WRITE_IO_BYTES         || '",' ||
  '"' || DELTA_INTERCONNECT_IO_BYTES  || '",' ||
  '"' || PGA_ALLOCATED                || '",' ||
  '"' || TEMP_SPACE_ALLOCATED         || '",' ||
  '"' || CON_DBID                     || '",' ||
  '"' || CON_ID                       || '",' ||
  '"' || DBOP_NAME                    || '",' ||
  '"' || DBOP_EXEC_ID                 || '"'
  FROM DBA_HIST_ACTIVE_SESS_HISTORY
WHERE SAMPLE_TIME >= TO_TIMESTAMP('2018/05/08 11:00:00', 'YYYY/MM/DD HH24:MI:SS')
  AND SAMPLE_TIME <  TO_TIMESTAMP('2018/05/08 13:00:00', 'YYYY/MM/DD HH24:MI:SS')
/
spool off;
SET TERMOUT ON;

参考資料

http://www.oracle.com/webfolder/technetwork/jp/ondemand/ddd2014/A1-4.pdf

Dockerコンポーネント

概要

主要なDockerコンポーネントついて記載します。

各コンポーネント

Dockerは複数のコンポーネントで構成されており、その中核的な機能のDocker Engineを使ってアプリケーションの実行環境を構築していきます。

コンポーネント名	説明
Docker Engine	Dockerのコア機能
Docker Registry	イメージ公開/共有
Docker Compose	複数コンテナ一元管理
Docker Machine	Docker実行環境構築
Docker Swarm	クラスタ管理

Docker Engine

Dockerイメージ生成・コンテナ起動などを実行するためのDockerのコア機能。
Dockerfileでのイメージ生成も実行。
通常、DockerとはこのDocker Engine(エンジン)を意味します。

Docker Registry

コンテナの基となるDockerイメージを公開/共有するためのレジストリ機能。
Docker Hub(公式レジストリサービス)もDocker Registryを使用している。

Docker Compose

複数コンテナの構成情報をコードで定義し、コマンドを実行します。
これによりアプリケーション実行環境を構成するコンテナ群を一元管理するためのツール。

Docker Machine

Docker実行環境をコマンドで自動生成するためのツール。
AWS EC2、Microsoft Azure、Google Container Engine(GKE)などのクラウド環境やローカルのVirtualBoxなどで動作させます。

Docker Swarm

複数のDockerホストをクラスタ化するツール。
Manager:クラスタ管理やAPIの提供を実施
Node:Dockerコンテナを実行
※Kubernetesの利用拡大によりあまり使用されなくなってきている。

参考資料

https://docs.docker.com/

Dockerの基本機能（Build/Ship/Run）

概要

Dockerのスローガンである「Build/Ship/Run」について学習したことを記載します。
Dockerの基本的な機能として、以下の3つがあります。

機能	説明
Build	Dockerイメージの作成
Ship	Dockerイメージの共有
Run	Dockerコンテナの動作

Build

「Build」は、コンテナイメージを構築（ビルド）するフェーズ。
Dockerでは、アプリケーションの実行に必要になるプログラム本体/ライブラリ/ミドルウェア/OS/ネットワークの設定などを1つにまとめてDockerイメージを作成します。
Dockerは、「1イメージ=1アプリケーション」として複数コンテナを連携してサービス構築する方法が推奨されています。
主なビルド手順:
   - docker buildコマンド
   - Docker Hub標準機能のAutomated Build
   - CI/CDサービス(Travis CI,Circle CI)※ビルド/テスト/デプロイ
コンテナのバージョン管理では、下記のファイルをGitの作法で管理するのが良いとされています。
これらの構成定義ファイルがGitHubやBitbucketのようなコード管理プラットフォーム上に保管されている場合、ほとんどのレジストリサービスやCI/CDサービスと連携が可能です。
   - Dockerfile:アプリケーション環境をコンテナイメージ化するためのビルド手順を定義
   - Composeファイル:アプリケーション環境の構成を定義

Ship

「Ship」は、DockerイメージをDockerレジストリで共有するフェーズ。
ビルド済みコンテナイメージの保管/バージョン管理/保管済みコンテナイメージの安全性を管理します。
Docker Hub(レジストリ):
   利用可能なコンテナイメージを永続的に保管し、コンテナイメージをバージョン管理していくために使用されるデータストア。
   公式イメージ以外にも個人が作成したイメージをDocker Hubで自由に公開して共有することが可能。
Dockerコマンドを使用するとDocker Hubにログインし、レジストリ上のイメージ検索やアップロード/ダウンロードが可能。
また、Docker Hubは、GitHubやBitbucketと連携することも可能。
例えば、GitHub上でDockerfileを管理し、そこからDockerイメージを自動生成(Automated Build)し、Docker Hubで公開することができます。
セキュリティの問題として、レジストリ上に一度保管して共有したコンテナイメージをアップグレードしないまま放置しているような場合、セキュリティホールなどの脆弱性が残った状態でユーザーに利用され続けてしまうリスクがあります。
そのため、このような問題に対応するために各種機能が用意されています。
   - Docker Context Trust:Dockerイメージの提供者を検証することができる機能
   - Docker Security Scanning:Dockerイメージをスキャンし、既知のセキュリティ上の脆弱性がないことを確認し、イメージの安全性を確認できる機能。

Run

「Run」は、実際にDockerコンテナを動作させるフェーズ。
Dockerは、Linux上でコンテナ単位でサーバ機能を動作させます。
このコンテナの基になるのがDockerイメージ。DockerイメージがあればDockerがインストールされた環境であればどこでもコンテナを動作可能。
コンテナデプロイのために必要になる煩雑な手続きは、クラスタ管理プラットフォームであるコンテナオーケストレーターに託されることが期待されています。
コンテナオーケストレーター:
   - レジストリからPull(ダウンロード)した任意のコンテナイメージをプラットフォーム環境上にデプロイ
   - デプロイしたコンテナに対する定期的なヘルスチェックの実行
   - コンテナの意図しない停止が検知された場合には、コンテナオーケストレーターが持つクラスタ管理機能が動作し、正常稼働している他の収容ノード上にコンテナを再収容するスケジュールがコンテナオーケストレーターのジョブに自動投入されて実行される
上記動作が実行されていますが、ユーザーはその動作の詳細を気にすることなく、それぞれのCaaS(Container as a Service)より提供されるAPIやWebインターフェースを操作することでコンテナ運用管理ができます。

Docker全体の管理系コマンド

Dockerコマンドのhelp[docker]

dockerのコマンドとオプションを確認する場合、「docker」もしくは「docker --help」を使用します。

$ docker

Dockerのバージョンの確認[docker version]

インストールされているDockerのバージョンを確認する際には、「docker version」コマンドを使用します。
確認対象:

• Dockerバージョン
• Go言語のバージョン
• OS/アーキテクチャ

$ docker version 

docker systemコマンド

Command	Description
docker system df	Show docker disk usage
docker system events	Get real time events from the server
docker system info	Display system-wide information
docker system prune	Remove unused data

Dockerの実行環境確認[docker system info]

Dockerが実行されている環境の詳細設定を確認できます。

$ docker system info

Dockerのディスク利用状況[docker system df]

Dockerが利用しているディスクの利用状況を確認する際に使用します。

$ docker system df

「-v」オプションで詳細表示可

Dockerのイベント確認[docker system events]

特定のdockerコマンドで発生するイベントをリアルタイムに監視します。

$ docker system events 

Dockerの使用していないデータの削除[docker system prune]

削除対象:

• 停止中のコンテナ
• 使用されてないボリューム
• 使用されてないネットワーク
• 使用されてないイメージ

$ docker system prune

Dockerで'Hello world'

概要

Dockerインストール後の正常性確認のためにDockerコンテナを作成します。
その上で"Hello world"をターミナル上に表示します。
今回は、Ubuntuイメージを基にDockerコンテナを作成・実行します。
なお、Dockerコンテナを作成する場合や実行する際には、「docker container run」コマンドを使用します。
このコマンドを含むDockerコマンドは以下も参考にしてください。

手順概要

1. Hello worldの実行
2. 実行結果の確認

使用コマンド

docker container runコマンド
docker container run <Docker Image> [コマンド]]
----------(1)--------- --------(2)--------- ----(3)----
(1)コンテナ作成・実行
(2)基となるDockerイメージ
(3)コンテナの中での実行コマンド
run -> ローカルキャッシュになければDocerリポジトリからDockerイメージをダウンロードし、コンテナ起動
ubuntu:latest -> Ubuntuの最新バージョンイメージ(latest)を取得
/bin/echo 'Hello world' -> 文字表示コマンドechoを使用して'Hello world'と表示する

実行内容

1.Hello worldの実行

$ docker container run ubuntu:latest /bin/echo 'Hello world'

2.実行結果の確認

docker@LesPaul:~$ docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
docker@LesPaul:~$ docker container run ubuntu:latest /bin/echo 'Hello world'
Unable to find image 'ubuntu:latest' locally
latest: Pulling from library/ubuntu
124c757242f8: Pull complete
9d866f8bde2a: Pull complete
fa3f2f277e67: Pull complete
398d32b153e8: Pull complete
afde35469481: Pull complete
Digest: sha256:de774a3145f7ca4f0bd144c7d4ffb2931e06634f11529653b23eba85aef8e378
Status: Downloaded newer image for ubuntu:latest
Hello world
docker@LesPaul:~$ docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
fcf29ccd026d ubuntu:latest "/bin/echo 'Hello wo…" 10 seconds ago Exited (0) 5 seconds ago priceless_bassi

Appendix

初回はローカルにDockerイメージが存在しておらず、Dockerリポジトリからダウンロードするため、多少時間を要します。
しかしながら、2回目以降ではローカルにダウンロードしたDockerイメージを基にコンテナを起動するため高速でコンテナが起動します。

Dockerインストール(Ubuntu 18.04)

概要

Docker(CE)をUbuntuにインストールする手順を記載します。
この記事の記載時点(2018/09/12)では、以下のUbuntuのバージョンがサポートされていました。

• Bionic 18.04 (LTS)
• Artful 17.10
• Xenial 16.04 (LTS)
• Trusty 14.04 (LTS)

https://docs.docker.com/install/linux/docker-ce/ubuntu/
今回は、Docker CEを「Ubuntu Bionic 18.04」にインストールします。

環境情報

ホストOS:Windows 10
VirtualBox:Ver 5.2.18
ゲストOS:Ubuntu 18.04
Docker:docker-ce amd64 18.06.1~ce~3-0~ubuntu

手順概要

1. 事前準備
2. Dockerインストール
3. 起動確認

実行内容

1.事前準備

apt(Advanced Packaging Tool)のアップデート

$ sudo apt-get update

HTTPS経由でリポジトリが使用可能となるようにパッケージをインストール

$ sudo apt-get install -y apt-transport-https ca-certificates curl software-properties-common

Dockerの公式GPG keyキーの追加

$ curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -

-> 正常に登録されると「OK」が表示されます。

GPGキーの確認

$ sudo apt-key fingerprint 0EBFCD88

docker@LesPaul:~$ sudo apt-key fingerprint 0EBFCD88
pub rsa4096 2017-02-22 [SCEA]
9DC8 5822 9FC7 DD38 854A E2D8 8D81 803C 0EBF CD88
uid [ 不明 ] Docker Release (CE deb) <docker@docker.com>
sub rsa4096 2017-02-22 [S]

Dockerリポジトリの追加(登録)

$ sudo add-apt-repository \
"deb [arch=amd64] https://download.docker.com/linux/ubuntu \
$(lsb_release -cs) \
stable"

aptパッケージの再アップデート

$ sudo apt-get update

2.Dockerインストール

$ sudo apt-get install docker-ce

-> インストール中に継続するか否かを聞かれるので「Y」を入力します。

3．起動確認

インストール後は自動的にDockerが起動

$ sudo systemctl status docker

docker@LesPaul:~$ sudo systemctl status docker
● docker.service - Docker Application Container Engine
Loaded: loaded (/lib/systemd/system/docker.service; enabled; vendor preset: enabled)
Active: active (running) since Wed 2018-09-12 22:12:31 JST; 3min 27s ago
Docs: https://docs.docker.com
Main PID: 28571 (dockerd)
Tasks: 29
CGroup: /system.slice/docker.service
├─28571 /usr/bin/dockerd -H fd://
└─28599 docker-containerd --config /var/run/docker/containerd/containerd.toml
$ sudo docker ps
docker@LesPaul:~$ sudo docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES

参考資料

https://docs.docker.com/install/