OraFAQ Forum: Performance Tuning » Find Locking History

Home » RDBMS Server » Performance Tuning » Find Locking History (Oracle 11.2.0.4.0 enterprise, CentOS 7.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Find Locking History [message #679856]

Wed, 08 April 2020 02:18

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

Report message to a moderator

Re: Find Locking History [message #679857 is a reply to message #679856]

Wed, 08 April 2020 02:37

John Watson
Messages: 8870
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.

Report message to a moderator

Re: Find Locking History [message #679858 is a reply to message #679857]

Wed, 08 April 2020 02:52

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?

Report message to a moderator

Re: Find Locking History [message #679859 is a reply to message #679858]

Wed, 08 April 2020 03:46

Michel Cadot
Messages: 68501
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).

Report message to a moderator

Re: Find Locking History [message #679860 is a reply to message #679858]

Wed, 08 April 2020 03:46

cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester

Senior Member

Nope - oracle tracks which session has a lock, it does not track which query did.

Report message to a moderator

Re: Find Locking History [message #679861 is a reply to message #679858]

Wed, 08 April 2020 03:48

John Watson
Messages: 8870
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?

Report message to a moderator

Re: Find Locking History [message #679862 is a reply to message #679861]

Wed, 08 April 2020 04:06

weekend79
Messages: 198
Registered: April 2005
Location: Islamabad

Senior Member

Thanks, John Watson & Michel Cadot

Report message to a moderator

Previous Topic:	Increase page loading speed
Next Topic:	DBMS_AUTO_SQLTUNE

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Sep 24 19:53:41 CDT 2023