SQL query to fetch Blocking sessions details from DPA database

I need to fetch the blocking session details as we see on the UI with SPID, Blocking time, Program Name & machine name.

I have checked in in CONSW table and able to fetch Program name, machine name & SPID of blocking session as suggested in CONSW Column Definition. But for blocking time I am unable to build any logic. I tried calculating the first & last entry for the sessions in last hour and calculating the blocking time but it did not give desired result.

I need output as count for all session blocking which are greater than 5 minutes in the last 1 hour.

Find more posts tagged with

Accepted answers

All comments

david.bierman

Hello, @shantanu167

Here is a query that should do the trick. Let me know if this doesn't do what you had envisioned.

DPA Repository Query to find Blocking Sessions

shantanu167

Hello @david.bierman

Thanks for sharing the query. I ran it but the blocking duration does not seem to match with the values on the web console.

I checked on web console & database at the same time.

The machine name & application name are correct & match with the value on web console.

david.bierman

@shantanu167 yes, I see your point. The query that I provided only measures the time that a session "directly" blocked other sessions, and does not include the impact of indirect blockers. As you can see, in your screenshot, session 96 (a blocker) has 4 direct blockees. Each direct blockee was blocked for 16 seconds (16 x 4 = 64, which matches the query results).

It will take more thought and time to rework the query such that it reports all direct and indirect blocking time. I'll be in touch.

david.bierman

@shantanu167 We are still working on the query, sorry for the delay. I want to clarify what you are after. Do you simply want to know if the cumulative blocking (across all sessions) is > 5 minutes in the last hour? Or do you need more detail than that (e.g. which sessions were blocked > 5 minutes)?