Skip to content
01/09/2020 / Oracle DBA

AWR REPORT SCRIPT

— 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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: