2019. 1. 17. 14:26 오라클
active session history 내역을 한방에 csv 로 생성하는 sql
11g 용
@ash_to_csv_11204.sql
Enter Line size from query: 150
Beginning date/time, format as mm/dd/yyyy hh24:mi:ss - 01/16/2019 11:00:00
Beginning date/time, format as mm/dd/yyyy hh24:mi:ss - 01/17/2019 11:00:00
실행된 위치 아래에 myash.csv 화일 생성
======================================
column ECID format a66
column MACHINE format a66
column CLIENT_ID format a66
column ACTION format a66
column MODULE format a66
column PROGRAM format a66
column IS_REPLAYED format a3
column IS_CAPTURED format a3
column REPLAY_OVERHEAD format a3
column CAPTURE_OVERHEAD format a3
column IN_SEQUENCE_LOAD format a3
column IN_CURSOR_CLOSE format a3
column IN_BIND format a3
column IN_JAVA_EXECUTION format a3
column IN_PLSQL_COMPILATION format a3
column IN_PLSQL_RPC format a3
column IN_PLSQL_EXECUTION format a3
column IN_SQL_EXECUTION format a3
column IN_HARD_PARSE format a3
column IN_PARSE format a3
column IN_CONNECTION_MGMT format a3
column XID format a10
column TOP_LEVEL_CALL_NAME format a66
column BLOCKING_HANGCHAIN_INFO format a3
column BLOCKING_SESSION_STATUS format a13
column SESSION_STATE format a9
column WAIT_CLASS format a66
column P3TEXT format a66
column P2TEXT format a66
column P1TEXT format a66
column EVENT format a66
column SQL_EXEC_START format a9
column SQL_PLAN_OPTIONS format a66
column SQL_PLAN_OPERATION format a66
column TOP_LEVEL_SQL_ID format a15
column SQL_OPNAME format a66
column IS_SQLID_CURRENT format a3
column SQL_ID format a15
column SESSION_TYPE format a12
column SAMPLE_TIME format a13
ACCEPT mylinesize num PROMPT 'Enter Line size from query: '
ACCEPT begindate date FORMAT 'mm/dd/yyyy hh24:mi:ss' PROMPT 'Beginning date/time, format as mm/dd/yyyy hh24:mi:ss -'
ACCEPT enddate date FORMAT 'mm/dd/yyyy hh24:mi:ss' PROMPT 'Ending date/time, format as mm/dd/yyyy hh24:mi:ss -'
alter session set nls_timestamp_format='MM/DD/YYYY HH24:MI:SS.FF3';
alter session set nls_date_format='MM/DD/YYYY HH:MI:SS';
set pages 2000
set lines &mylinesize
set wrap off
set heading off
set feedback off
set echo off
! echo "SNAP_ID , DBID , INSTANCE_NUMBER , SAMPLE_ID , SAMPLE_TIME , 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 " > myash.csv
spool myash.csv append
select SNAP_ID ||','|| DBID ||','|| INSTANCE_NUMBER ||','|| SAMPLE_ID ||','|| SAMPLE_TIME ||','|| 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_date('&begindate', 'MM/DD/YYYY hh24:mi:ss') and
sample_time<to_date('&enddate', 'MM/DD/YYYY hh24:mi:ss')
Order by sample_time, session_id asc;
spool off;
quit;
--------------------------------------------------
12c 용
@ash_to_csv_122_multitenant.sql
column CAPTURE_OVERHEAD format a3
column IS_CAPTURED format a3
column MODULE format a66
column IN_SEQUENCE_LOAD format a3
column SQL_PLAN_OPERATION format a66
column IN_SQL_EXECUTION format a3
column CLIENT_ID format a66
column IS_SQLID_CURRENT format a3
column P2TEXT format a66
column BLOCKING_HANGCHAIN_INFO format a3
column SAMPLE_TIME format a13
column IN_CONNECTION_MGMT format a3
column IS_REPLAYED format a3
column IN_PLSQL_COMPILATION format a3
column IN_JAVA_EXECUTION format a3
column IN_HARD_PARSE format a3
column IN_INMEMORY_POPULATE format a3
column REPLAY_OVERHEAD format a3
column ACTION format a66
column ECID format a66
column IN_INMEMORY_REPOPULATE format a3
column PROGRAM format a66
column SQL_EXEC_START format a9
column DBOP_NAME format a66
column EVENT format a66
column TOP_LEVEL_CALL_NAME format a66
column SQL_OPNAME format a66
column MACHINE format a66
column TOP_LEVEL_SQL_ID format a15
column P3TEXT format a66
column SQL_PLAN_OPTIONS format a66
column IN_PLSQL_EXECUTION format a3
column IN_INMEMORY_TREPOPULATE format a3
column IN_CURSOR_CLOSE format a3
column SQL_ID format a15
column WAIT_CLASS format a66
column P1TEXT format a66
column BLOCKING_SESSION_STATUS format a13
column XID format a10
column IN_PARSE format a3
column IN_BIND format a3
column IN_INMEMORY_QUERY format a3
column SESSION_STATE format a9
column IN_INMEMORY_PREPOPULATE format a3
column SESSION_TYPE format a12
column IN_PLSQL_RPC format a3
column IS_REPLAY_SYNC_TOKEN_HOLDER format a3
column SAMPLE_TIME_UTC format a13
ACCEPT mylinesize num PROMPT 'Enter Line size from query: '
ACCEPT begindate date FORMAT 'mm/dd/yyyy hh24:mi:ss' PROMPT 'Beginning date/time, format as mm/dd/yyyy hh24:mi:ss -'
ACCEPT enddate date FORMAT 'mm/dd/yyyy hh24:mi:ss' PROMPT 'Ending date/time, format as mm/dd/yyyy hh24:mi:ss -'
alter session set nls_timestamp_format='MM/DD/YYYY HH24:MI:SS.FF3';
alter session set nls_date_format='MM/DD/YYYY HH:MI:SS';
set pages 2000
set lines &mylinesize
set wrap off
set heading off
set feedback off
set echo off
! echo "SNAP_ID,DBID,INSTANCE_NUMBER,SAMPLE_ID,SAMPLE_TIME,SAMPLE_TIME_UTC,USECS_PER_ROW,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_FULL_PLAN_HASH_VALUE," -
"SQL_ADAPTIVE_PLAN_RESOLVED,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,IN_INMEMORY_QUERY,IN_INMEMORY_POPULATE,IN_INMEMORY_PREPOPULATE,IN_INMEMORY_REPOPULATE," -
"IN_INMEMORY_TREPOPULATE,CAPTURE_OVERHEAD,REPLAY_OVERHEAD,IS_CAPTURED,IS_REPLAYED,IS_REPLAY_SYNC_TOKEN_HOLDER,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,DBOP_NAME,DBOP_EXEC_ID," -
"CON_DBID,CON_ID," > myash.csv
spool myash.csv append
select SNAP_ID||','||DBID||','||INSTANCE_NUMBER||','||SAMPLE_ID||','||SAMPLE_TIME||','||SAMPLE_TIME_UTC||','||USECS_PER_ROW||','||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_FULL_PLAN_HASH_VALUE||','||SQL_ADAPTIVE_PLAN_RESOLVED||','||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||','||IN_INMEMORY_QUERY||','||IN_INMEMORY_POPULATE||','||IN_INMEMORY_PREPOPULATE||','||IN_INMEMORY_REPOPULATE||','|| -
IN_INMEMORY_TREPOPULATE||','||CAPTURE_OVERHEAD||','||REPLAY_OVERHEAD||','||IS_CAPTURED||','||IS_REPLAYED||','||IS_REPLAY_SYNC_TOKEN_HOLDER||','||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||','||DBOP_NAME||','||DBOP_EXEC_ID||','||CON_DBID||','||CON_ID||','
From DBA_HIST_ACTIVE_SESS_HISTORY
Where sample_time > to_date('&begindate', 'MM/DD/YYYY hh24:mi:ss') and
sample_time<to_date('&enddate', 'MM/DD/YYYY hh24:mi:ss')
Order by sample_time, session_id asc;
spool off;
quit;
