Home » RDBMS Server » Performance Tuning » Lots of 'Row Lock Waits" on the indexes, but no on the based table
Lots of 'Row Lock Waits" on the indexes, but no on the based table [message #271510] Mon, 01 October 2007 15:45 Go to next message
wqhhust
Messages: 4
Registered: September 2007
Junior Member
After I run the AWR report, I found there are lots of "'Row Lock Waits" on the 4 indexes of one table. But the strange thing is that there is no such wait on the base table.

(the stats shown in the section of Segments by Row Lock Waits)

How can it be like that? Under what condition will the "'Row Lock Waits" happens to index but not on the base table?


Re: Lots of 'Row Lock Waits" on the indexes, but no on the based table [message #271515 is a reply to message #271510] Mon, 01 October 2007 16:34 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Under what condition will the "'Row Lock Waits" happens to index but not on the base table?
Typically a session will read the index & then read the table.
A session can easily be waiting on the index (especially when FOR UPDATE) & not have to wait on the table itself.
Re: Lots of 'Row Lock Waits" on the indexes, but no on the based table [message #271523 is a reply to message #271515] Mon, 01 October 2007 17:20 Go to previous messageGo to next message
wqhhust
Messages: 4
Registered: September 2007
Junior Member
create table test1 as select * from dba_objects
create index idx_test1 on test1(owner)

first
session 1:
select * from test1 where owner='SYS' for update

then
session 2:
update test1 set owner='test'


I found the incearsed value of "row lock waits" is 2 instead of 1.
That meant the following query result jump from x to x+2 instead to x+1.
select value from v$segment_statistics where statistic_name='row lock waits' and object_name='TEST1'
Re: Lots of 'Row Lock Waits" on the indexes, but no on the based table [message #271525 is a reply to message #271510] Mon, 01 October 2007 17:28 Go to previous message
wqhhust
Messages: 4
Registered: September 2007
Junior Member
Hi, anacedent

And the row lock wait is on the table instead of the index(but you it's easy to wait on the index, but even I create the index, it still wait on the table)
Previous Topic: Rollback per transaction % too high and a lot db file sequential reads
Next Topic: open cursors
Goto Forum:
  


Current Time: Sat Dec 10 18:47:33 CST 2016

Total time taken to generate the page: 0.07683 seconds