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