Saturday, July 11, 2015

Query to get wait events details related with concurrent programs

  SELECT s.saddr,
         s.sid,
         s.serial#,
         s.audsid,
         s.paddr,
         s.user#,
         s.username,
         s.command,
         s.ownerid,
         s.taddr,
         s.lockwait,
         s.status,
         s.server,
         s.schema#,
         s.schemaname,
         s.osuser,
         s.process,
         s.machine,
         s.terminal,
         UPPER (s.program) program,
         s.TYPE,
         s.sql_address,
         s.sql_hash_value,
         s.sql_id,
         s.sql_child_number,
         s.sql_exec_start,
         s.sql_exec_id,
         s.prev_sql_addr,
         s.prev_hash_value,
         s.prev_sql_id,
         s.prev_child_number,
         s.prev_exec_start,
         s.prev_exec_id,
         s.plsql_entry_object_id,
         s.plsql_entry_subprogram_id,
         s.plsql_object_id,
         s.plsql_subprogram_id,
         s.module,
         s.module_hash,
         s.action,
         s.action_hash,
         s.client_info,
         s.fixed_table_sequence,
         s.row_wait_obj#,
         s.row_wait_file#,
         s.row_wait_block#,
         s.row_wait_row#,
         s.logon_time,
         s.last_call_et,
         s.pdml_enabled,
         s.failover_type,
         s.failover_method,
         s.failed_over,
         s.resource_consumer_group,
         s.pdml_status,
         s.pddl_status,
         s.pq_status,
         s.current_queue_duration,
         s.client_identifier,
         s.blocking_session_status,
         s.blocking_instance,
         s.blocking_session,
         s.seq#,
         s.event#,
         s.event,
         s.p1text,
         s.p1,
         s.p1raw,
         s.p2text,
         s.p2,
         s.p2raw,
         s.p3text,
         s.p3,
         s.p3raw,
         s.wait_class_id,
         s.wait_class#,
         s.wait_class,
         s.wait_time,
         s.seconds_in_wait,
         s.state,
         s.wait_time_micro,
         s.time_remaining_micro,
         s.time_since_last_wait_micro,
         s.service_name,
         s.sql_trace,
         s.sql_trace_waits,
         s.sql_trace_binds,
         s.sql_trace_plan_stats,
         s.session_edition_id,
         s.creator_addr,
         s.creator_serial#
    FROM v$session s
   WHERE (    (s.username IS NOT NULL)
          AND (NVL (s.osuser, 'x') <> 'system')
          AND (s.TYPE <> 'background')
          AND status = 'active')

ORDER BY "program";

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect