Hii.
I need your ideas about track blocking session information from ash.
I am not only trying to find blocking session sql also I need to find that what is the blocker's sql_id.
Ash does not give blocker sql_id everytime if sql completed before sampled so I just try to find sql_id takes some much time.
Is the following sql is correct according to your opinions ? or any suggestion
Best regards
SELECT X1.SAMPLE_ID AS WAITER_SAMPLE_ID,
X1.SAMPLE_TIME AS WAITER_SAMPLE_TIME,
X1.SESSION_ID AS WAITER_SESSION_ID,
X1.SQL_ID AS WAITER_SQL_ID,
X1.BLOCKING_SESSION AS WAITER_BLOCKER_SESSION_ID,
X2.SAMPLE_ID AS HOLDER_SAMPLE_ID,
X2.SAMPLE_TIME AS HOLDER_SAMPLE_TIME,
X2.SESSION_ID AS HOLDER_SESSION_ID,
x2.sql_id AS HOLDER_SQL_ID
FROM V$ACTIVE_SESSION_HISTORY X1,
V$ACTIVE_SESSION_HISTORY X2
WHERE X1.blocking_session IS NOT NULL
AND x1.blocking_session=x2.session_id
and x1.BLOCKING_SESSION_SERIAL#=x2.SESSION_SERIAL#
AND X1.SAMPLE_ID=X2.SAMPLE_ID