GAGA LIFE.

DBAブログ -APとロジックとAKがある-

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

概要

現在、自分達DBAチームが管理している環境には、
以下の環境があるため、それぞれについてメモします。

  11.2.0.4 RAC
  12.1.0.2 RAC

また、以下の情報についてもそれぞれ記載します。

  ・GV$~ : ASHバッファの1秒間隔の情報
  ・DBA_HIST~ : 10秒間隔でサマリした情報(SYSAUX内のテーブル格納)

※普段は、下記SQLをシェルから呼び出して使っています。

使用上の注意

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
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;

■参考資料

https://docs.oracle.com/cd/E16338_01/server.112/b56311/dynviews_1007.htm#sthref3170

https://docs.oracle.com/cd/E16338_01/server.112/b56311/statviews_3201.htm#sthref2018

https://docs.oracle.com/cd/E57425_01/121/REFRN/GUID-69CEA3A1-6C5E-43D6-982C-F353CD4B984C.htm#GUID-69CEA3A1-6C5E-43D6-982C-F353CD4B984C

https://docs.oracle.com/cd/E57425_01/121/REFRN/GUID-335EC838-FEA0-4872-9E14-67C5A1908B35.htm#GUID-335EC838-FEA0-4872-9E14-67C5A1908B35

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