---- 11.2.0.3 create or replace view dh_ash_11g_lhr (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) as select /* ASH/AWR meta attributes */ ash.snap_id, ash.dbid, ash.instance_number, ash.sample_id, ash.sample_time, /* Session/User attributes */ ash.session_id, ash.session_serial#, decode(ash.session_type, 1,'FOREGROUND', 'BACKGROUND'), ash.flags, ash.user_id, /* SQL attributes */ ash.sql_id, decode(bitand(ash.flags, power(2, 4)), NULL, 'N', 0, 'N', 'Y'), ash.sql_child_number, ash.sql_opcode, (select command_name from WRH$_SQLCOMMAND_NAME where command_type = ash.sql_opcode and dbid = ash.dbid) as sql_opname, ash.force_matching_signature, decode(ash.top_level_sql_id, NULL, ash.sql_id, ash.top_level_sql_id), decode(ash.top_level_sql_id, NULL, ash.sql_opcode, ash.top_level_sql_opcode), /* SQL Plan/Execution attributes */ ash.sql_plan_hash_value, decode(ash.sql_plan_line_id, 0, to_number(NULL), ash.sql_plan_line_id), (select operation_name from WRH$_PLAN_OPERATION_NAME where operation_id = ash.sql_plan_operation# and dbid = ash.dbid) as sql_plan_operation, (select option_name from WRH$_PLAN_OPTION_NAME where option_id = ash.sql_plan_options# and dbid = ash.dbid) as sql_plan_options, decode(ash.sql_exec_id, 0, to_number(NULL), ash.sql_exec_id), ash.sql_exec_start, /* PL/SQL attributes */ decode(ash.plsql_entry_object_id,0,to_number(NULL), ash.plsql_entry_object_id), decode(ash.plsql_entry_object_id,0,to_number(NULL), ash.plsql_entry_subprogram_id), decode(ash.plsql_object_id,0,to_number(NULL), ash.plsql_object_id), decode(ash.plsql_object_id,0,to_number(NULL), ash.plsql_subprogram_id), /* PQ attributes */ decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_instance_id), decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_session_id), decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_session_serial#), decode(ash.px_flags, 0, to_number(NULL), ash.px_flags), /* Wait event attributes */ decode(ash.wait_time, 0, evt.event_name, NULL), decode(ash.wait_time, 0, evt.event_id, NULL), ash.seq#, evt.parameter1, ash.p1, evt.parameter2, ash.p2, evt.parameter3, ash.p3, decode(ash.wait_time, 0, evt.wait_class, NULL), decode(ash.wait_time, 0, evt.wait_class_id, NULL), ash.wait_time, decode(ash.wait_time, 0, 'WAITING', 'ON CPU'), ash.time_waited, (case when ash.blocking_session = 4294967295 then 'UNKNOWN' when ash.blocking_session = 4294967294 then 'GLOBAL' when ash.blocking_session = 4294967293 then 'UNKNOWN' when ash.blocking_session = 4294967292 then 'NO HOLDER' when ash.blocking_session = 4294967291 then 'NOT IN WAIT' else 'VALID' end), (case when ash.blocking_session between 4294967291 and 4294967295 then to_number(NULL) else ash.blocking_session end), (case when ash.blocking_session between 4294967291 and 4294967295 then to_number(NULL) else ash.blocking_session_serial# end), (case when ash.blocking_session between 4294967291 and 4294967295 then to_number(NULL) else ash.blocking_inst_id end), (case when ash.blocking_session between 4294967291 and 4294967295 then NULL else decode(bitand(ash.flags, power(2, 3)), NULL, 'N', 0, 'N', 'Y') end), /* Session's working context */ ash.current_obj#, ash.current_file#, ash.current_block#, ash.current_row#, ash.top_level_call#, (select top_level_call_name from WRH$_TOPLEVELCALL_NAME where top_level_call# = ash.top_level_call# and dbid = ash.dbid) as top_level_call_name, decode(ash.consumer_group_id, 0, to_number(NULL), ash.consumer_group_id), ash.xid, decode(ash.remote_instance#, 0, to_number(NULL), ash.remote_instance#), ash.time_model, decode(bitand(ash.time_model,power(2, 3)),0,'N','Y') as in_connection_mgmt, decode(bitand(ash.time_model,power(2, 4)),0,'N','Y')as in_parse, decode(bitand(ash.time_model,power(2, 7)),0,'N','Y')as in_hard_parse, decode(bitand(ash.time_model,power(2,10)),0,'N','Y')as in_sql_execution, decode(bitand(ash.time_model,power(2,11)),0,'N','Y') as in_plsql_execution, decode(bitand(ash.time_model,power(2,12)),0,'N','Y')as in_plsql_rpc, decode(bitand(ash.time_model,power(2,13)),0,'N','Y') as in_plsql_compilation, decode(bitand(ash.time_model,power(2,14)),0,'N','Y') as in_java_execution, decode(bitand(ash.time_model,power(2,15)),0,'N','Y')as in_bind, decode(bitand(ash.time_model,power(2,16)),0,'N','Y')as in_cursor_close, decode(bitand(ash.time_model,power(2,17)),0,'N','Y')as in_sequence_load, decode(bitand(ash.flags,power(2,5)),NULL,'N',0,'N','Y') as capture_overhead, decode(bitand(ash.flags,power(2,6)), NULL,'N',0,'N','Y' ) as replay_overhead, decode(bitand(ash.flags,power(2,0)),NULL,'N',0,'N','Y') as is_captured, decode(bitand(ash.flags,power(2,2)), NULL,'N',0,'N','Y' )as is_replayed, /* Application attributes */ ash.service_hash, ash.program, ash.module module, ash.action action, ash.client_id, ash.machine, ash.port, ash.ecid, /* DB Replay info */ ash.dbreplay_file_id, ash.dbreplay_call_counter, /* stash columns */ ash.tm_delta_time, ash.tm_delta_cpu_time, ash.tm_delta_db_time, ash.delta_time, ash.delta_read_io_requests, ash.delta_write_io_requests, ash.delta_read_io_bytes, ash.delta_write_io_bytes, ash.delta_interconnect_io_bytes, ash.pga_allocated, ash.temp_space_allocated from WRM$_SNAPSHOT sn, WRH$_ACTIVE_SESSION_HISTORY ash, WRH$_EVENT_NAME evt where ash.snap_id = sn.snap_id(+) and ash.dbid = sn.dbid(+) and ash.instance_number = sn.instance_number(+) and ash.dbid = evt.dbid and ash.event_id = evt.event_id; |