概要
現在、自分達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