Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, AmirReza.

Asked: November 28, 2022 - 2:56 pm UTC

Last updated: December 01, 2022 - 3:49 am UTC

Version: 12.1.0.2.0

Viewed 100+ times

You Asked

hello sir,
I want to see the blocking sessions that occur during the day to fix the sessions or SQLs that are causing the others to lock up. There is no trace of locker SQLs in the GV$ACTIVE_SESSION_HISTORY . The sql_id and top_level_sql_id fields specify the locked SQLs, but not the locker SQLs. How can I get it?
thanks for your help.

and Connor said...

Not directly because there is not really a direct relationship. I could

- delete a row in a table T, the delete finishes at 9am,
- I run 10 more queries plus update some other tables
- I walk away from my desk without committing

At 11am you try to lock the table T. V$ACTIVE_SESSION_HISTORY will tell you that my session is the one that is stopping you, but the SQL that created the problem is long gone.

Sometimes you can use V$ACTIVE_SESSION_HISTORY to get a good "guess" but seeing when the blocking started, and then seeing what SQL_ID's the offending session has run around that time frame, but obviously that does not guard against scenarios like the one I presented, ie, a transaction held open for a long time.


In extreme cases, you can resort to a processstate/ systemstate dump ( http://www.juliandyke.com/Diagnostics/Dumps/SYSTEMSTATE.php ) which will often dump out recently run SQL's from each process.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database