ash_stuff.txt

---
--- Top Waits - AWR ASH
---

  SELECT wait_class, event, COUNT (*)
    FROM dba_hist_active_sess_history
   WHERE     session_state = 'WAITING'
         --AND wait_class = '&wait_class'
         AND sample_time >= TO_DATE ('&start', 'dd/mm/yyyy hh24:mi:ss')
         AND sample_time <= TO_DATE ('&end', 'dd/mm/yyyy hh24:mi:ss')
GROUP BY wait_class, event
ORDER BY COUNT (*) DESC;

---
--- Waiting sessions - AWR ASH
---
--- Good for when you know a spike has occurred for a particular wait event
--- and you want to get an overview of who the blocking sessions were. You
--- can drill into those further by uncommenting the session_id predicate
--- and specifying the relevent blocking sids.
---
--- Adapted from:
--- http://dboptimizer.com/2012/01/19/buffer-busy-waits-and-disk-file-operations-io
---

  SELECT TO_CHAR (MIN (sample_time), 'HH24:MI') minst,
         TO_CHAR (MAX (sample_time), 'HH24:MI') maxst,
         COUNT (*),
         session_id,
         session_serial#,
         ash.p1,
         ash.p2,
         ash.p3,
         ash.SQL_ID,
         blocking_session bsid,
         blocking_session_serial# bserial
    FROM dba_hist_active_sess_history ash
   WHERE     event = '&event'
         AND session_state = 'WAITING'
         AND sample_time >= TO_DATE ('&start', 'dd/mm/yyyy hh24:mi:ss')
         AND sample_time <= TO_DATE ('&end', 'dd/mm/yyyy hh24:mi:ss')
         --AND session_id IN (1938, 2863)
GROUP BY session_id,
         session_serial#,
         sql_id,
         blocking_session,
         blocking_session_serial#,
         ash.p1,
         ash.p2,
         ash.p3
ORDER BY 1;

---
--- ASH Detail - AWR ASH
---

  SELECT sample_time,
         session_id,
         session_serial#,
         session_type,
         u.username,
         sql_id,
         sql_child_number,
         is_sqlid_current,
         sql_opname,
         top_level_sql_id,
         event,
         wait_class,
         p1,
         p2,
         p3,
         session_state,
         blocking_session,
         blocking_session_serial#,
         program,
         module,
         machine
    FROM dba_hist_active_sess_history ash, dba_users u
   WHERE     sample_time >= TO_DATE ('&start', 'dd/mm/yyyy hh24:mi:ss')
         AND sample_time <= TO_DATE ('&end', 'dd/mm/yyyy hh24:mi:ss')
         --AND event = '&event'
         --AND wait_class = '&wait_class'
         --AND session_state = 'WAITING'
         --AND sql_id = '&sql_id'
         AND ash.user_id = u.user_id
ORDER BY sample_time ASC;

---
--- The TM enqueue locks a structure from change and involves an exclusive
--- row level lock. A common cause of TM contention involves referential
--- integrity constraints. For example, a session inserts a new value into
--- a child table and then a second session wants to delete a row in the parent
--- This session only knows there is an outstanding change, it doesn't know
--- what the pending value is. Therefore, it has to wait so that there is
--- no chance of it changing the parent record and violating the constraint
--- Lengthy waits can occur when the change to the child table is left
--- uncommited for a long time, or there is no index on the child's foreign
--- key column so a full scan occurs when verifying the constraint.
---
--- The following query shows sessions waiting on the TM enqueue e.g. attempting
--- to modify the parent table, and also shows the child table being modified
--- by the blocker. The mode will be 4 when the blocked session is trying
--- to delete from the parent, and 3 when attempting to insert into the parent.
--- Based on some comments on oracle-l it would appear that mode 2 is where a
--- session is blocked trying to modify the parent and there has been an insert
--- into the child. Mode 6 means someone has issued "lock table in exclusive mode"
--- "create index" or "insert /*+ append */ select"
---

 SELECT sample_time,
         event,
         sql_id,
         MOD (p1, 16) AS "mode",
         p2 || ' ' || o.name obj,
         machine,
         program
    FROM v$active_session_history ash, sys.obj$ o
   WHERE event = 'enq: TM - contention' AND o.obj#(+) = ash.p2
ORDER BY sample_time;