RE: Block dump - Uncommitted TXN - Help Urgent

Note that if you see a TX enqueue waiting on mode 4, that's not necessarily ITL slot shortage. If the waiting statement is an INSERT and the object is a heap table, then it's definitely not ITL shortage. (If there's ITL shortage on a table block, INSERT will go to another block on the free list, if it's on the index, INSERT will do a block split.)  

The other cause TX enqueue mode 4 waits is concurrent inserts colliding on a unique index, PK, or UK. (i.e. session 1 inserts key value 1, session 2 inserts value 2, session 1 attempts to insert value 2, session 2 attempts to insert value 1)  

Hope that helps,  

-Mark    

-- 
Mark J. Bobak 
Senior Oracle Architect 
ProQuest Information & Learning 

"A human being should be able to change a diaper, plan an invasion,
butcher a hog, conn a ship, design a building, write a sonnet, balance
accounts, build a wall, set a bone, comfort the dying, take orders, give
orders, cooperate, act alone, solve equations, analyze a new problem,
pitch manure, program a computer, cook a tasty meal, fight efficiently,
die gallantly. Specialization is for insects."   --Robert A. Heinlein


 

________________________________

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of BN
Sent: Wednesday, September 20, 2006 10:15 AM
To: goran bogdanovic
Cc: oracle-l_at_freelists.org
Subject: Re: Block dump - Uncommitted TXN - Help Urgent




On 9/20/06, goran bogdanovic <goran00_at_gmail.com> wrote: 

	Hi,
	
	The inittrans should correspond to your max active concurrent
dml sessions on that table
	
	
	goran
	
	
	
	On 9/20/06, BN < bnsarma_at_gmail.com <mailto:bnsarma_at_gmail.com> >
wrote: 

		
		
		
		On 9/19/06, Christian Antognini <
Christian.Antognini_at_trivadis.com
<mailto:Christian.Antognini_at_trivadis.com> > wrote: 





			> I was thinking of INITRANS , is there a way to

track that from


			> v$lock,

			> I remember once Steve Adams  answering some

body looking at v$lock


			> looking at Request Column, not sure though

			
			Hi
			
			Here an example of sessions waiting for a
transaction slot:
			
			SQL> SELECT event FROM v$session_wait WHERE sid
= 12;
			
			EVENT
			-----------
			enqueue
			
			SQL> SELECT type, id1, id2, lmode, request FROM
v$lock WHERE sid = 12; 
			
			TY        ID1        ID2      LMODE    REQUEST
			-- ---------- ---------- ---------- ----------
			TM      34522          0          3          0
			TX     393247       9877          0          4
			
			As you can see the request mode is S (4). 
			
			
			HTH
			Chris
			--
			http://www.freelists.org/webpage/oracle-l
			
			
			


		Greetings,
		
		Yes I saw  Request=4 for quite a few sessions. I ahve
Identified the Tables that needs INITRANS Bump. Is there a way to
figureout  how much should I bump for each table in question 
		
		-- 
		Regards & Thanks
		
		BN 



Greetings

Yes, I understand, is there a view  in the Datadictionary to check  how
much each table has reached, other than tracking/sampling v$lock or
v$transaction


-- 
Regards & Thanks
BN 

--
http://www.freelists.org/webpage/oracle-l