| Simulate session blocking problems [message #503405] |
Fri, 15 April 2011 05:58  |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Hi - I'm working on some code to detect blocking sessions. I can simulate contention for row locks easily, but I'd like to test the effect of (for example) sessions serializing on library cache locks or buffer busy waits.
Does anyone know how to cause some of these serialization problems for testing purposes?
|
|
|
|
|
|
| Re: Simulate session blocking problems [message #503410 is a reply to message #503409] |
Fri, 15 April 2011 06:17  |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I'm looking at queries based on
select sid,blocking_session,event from v$session;
which I think tells me about all contention problems, whether latches or enqueues. Then depending on the event, I can join to whatever other view is appropriate (such as v$lock for a TX or TM enqueue event.)
|
|
|
|
|
|
| Re: Simulate session blocking problems [message #503418 is a reply to message #503412] |
Fri, 15 April 2011 07:08 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Thanks for replying. I mentioned library cache lock because that was one problem we had recently, several secheduler jobs hanging for a long time:
1 select u.machine,u.program,u.username,u.sid,u.blocking_session,u.event,u.seconds_in_wait from
2 v$session u
3* where u.wait_class <> 'Idle' and u.wait_time=0
192.168.56.101:1521/qs_prod> /
MACHINE PROGRAM USERNAME SID BLOCKING_SESSION EVENT SECONDS_IN_WAIT
-------------------- -------------------- ---------- ---------- ---------------- ------------------------------ ---------------
pass06 oracle@pass06 (J002) SYS 197 288 library cache lock 1876
pass06 oracle@pass06 (J003) SYS 476 288 library cache lock 2178
I'd like to simulate this situation, and others, so that I can test an event handler for any type of block.
|
|
|
|
| Re: Simulate session blocking problems [message #503419 is a reply to message #503418] |
Fri, 15 April 2011 07:11 |
 |
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
ind> select event,total_waits from v$system_event
2 where event in('free buffer waits','buffer busy waits');
EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
buffer busy waits 1861
1 row selected.
---Before inserting it was just 27 Only!
ind> select u.machine,u.program,u.username,u.sid,u.blocking_session,u.event,u.seconds_in_wait from
2 v$session u
3 where u.wait_class <> 'Idle' and u.wait_time=0
4 /
MACHINE
----------------------------------------------------------------
PROGRAM
----------------------------------------------------------------
USERNAME SID BLOCKING_SESSION
------------------------------ ---------- ----------------
EVENT SECONDS_IN_WAIT
---------------------------------------------------------------- ---------------
sqlplusw.exe
APPO 578
db file sequential read 0
sqlplusw.exe
APPO 603
db file scattered read 0
SDB
ORACLE.EXE (LGWR)
661
log file parallel write 0
SDB
ORACLE.EXE (DBW0)
662
db file parallel write 0
If I read it proper ...buffer busy waits happen because two users want to change a block at the same time. Two users can change the same block, or even same row "at the same time" ie without committing, but that's different from the actual operation of modifying the block. The modification on the block in RAM, or computer memory, can only be done by one process at at time in order to avoid memory corruptions. Different users can modify different blocks at the same time but only one user or process can modify a the same block at a time.
(I am inserting > 10000 rows each into 1 table from different sessions)
Sriram
[Updated on: Fri, 15 April 2011 07:16] Report message to a moderator |
|
|
|
| Re: Simulate session blocking problems [message #503421 is a reply to message #503419] |
Fri, 15 April 2011 07:19 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Yes, of course, Sriram. But you see how hard it is to simulate the problem in a way that makes it detectable? I need to know in real time if a session is blocked, and as you have proved, usually a buffer busy wait (or a free buffer wait) doesn't last long enough. It would be different if we had EE and the management packs because then I could use ASH.
[edit: I shouldn't have mentioned "free buffer wait" above, because that isn't a contention issue, so I wouldn't expect anything in the blocking_session column]
[Updated on: Fri, 15 April 2011 07:27] Report message to a moderator |
|
|
|
|
|
| Re: Simulate session blocking problems [message #503426 is a reply to message #503424] |
Fri, 15 April 2011 07:56 |
 |
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
I do not agree with his following sentence:
Quote:But 313 has already requested that library cache pin in mode 3. A queue is building up here.
Many processes can queue behind session 313
There is no queue with latch and no one can say if session 313 or 442 will take the latch first.
It is possible that session 313 waits indefinitively when others take the latch before it.
Regards
Michel
[Updated on: Fri, 15 April 2011 07:57] Report message to a moderator |
|
|
|
| Re: Simulate session blocking problems [message #503434 is a reply to message #503426] |
Fri, 15 April 2011 09:46 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Thanks, that article gives nice examples of how to force contention problems. For example, I've set up this situation: SID BLOCKING_SESSION EVENT SECONDS_IN_WAIT
---------- ---------------- ------------------------------ ---------------
19 144 library cache lock 307
144 144 library cache pin 434
which looks a bit odd because of 144 blocking itself as well as others, but that is exactly what is happening.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
(ofcourse latches too)