Home » RDBMS Server » Performance Tuning » Find Locking History (Oracle 11.2.0.4.0 enterprise, CentOS 7.2)
icon14.gif  Find Locking History [message #679856] Wed, 08 April 2020 02:18 Go to next message
weekend79
Messages: 198
Registered: April 2005
Location: Islamabad
Senior Member

Hi
Need a query to find the history of "Which query" locked "Which another query/queries".
Note: gone through dba_hist_active_sess_history it gives blocking session serial no and blocking session but I can't get blocking query from it.

Thanks
Re: Find Locking History [message #679857 is a reply to message #679856] Wed, 08 April 2020 02:37 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
What you want may not be possible. Indeed, it is probably logically impossible.

My session could execute a zillion statements that, taken together, lock umpteen rows in numerous tables. And right now, perhaps my session is idle. Then your session hits a row that I have locked. Which of my statements do you want? That information is not recorded anywhere.
Re: Find Locking History [message #679858 is a reply to message #679857] Wed, 08 April 2020 02:52 Go to previous messageGo to next message
weekend79
Messages: 198
Registered: April 2005
Location: Islamabad
Senior Member

Thanks for enlighting me, Can we find it in a different manner?
i.e. find the history of "Which query" locked "Which another query/queries" and keep it locked for more than 5 seconds?

Plus can we track the blocking query from column BLOCKING_SESSION, BLOCKING_SESSION_SERRIAL#, BLOCKING_INST_ID of table dba_hist_active_sess_history ? if yes then how?
Re: Find Locking History [message #679859 is a reply to message #679858] Wed, 08 April 2020 03:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

There is no history of locks.
There is no complete history of queries (ash is just samples).

Re: Find Locking History [message #679860 is a reply to message #679858] Wed, 08 April 2020 03:46 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Nope - oracle tracks which session has a lock, it does not track which query did.
Re: Find Locking History [message #679861 is a reply to message #679858] Wed, 08 April 2020 03:48 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
The history you want is not possible. What if I insert a row and then update it and then delete it, all in one transaction. Which of the statements would you want?
Re: Find Locking History [message #679862 is a reply to message #679861] Wed, 08 April 2020 04:06 Go to previous message
weekend79
Messages: 198
Registered: April 2005
Location: Islamabad
Senior Member

Thanks, John Watson & Michel Cadot


Previous Topic: Increase page loading speed
Next Topic: DBMS_AUTO_SQLTUNE
Goto Forum:
  


Current Time: Thu Mar 28 12:39:16 CDT 2024