Contention on a table

Folks, I broke my head over this one. So, I have this table in a third party application. The table has row level locking, contains around 400000 rows of 220 bytes each. The way it behaves under the load is unique to this table only, the rest of them behave pretty much normal. Let's say, I run a SELECT statement against this table during with isolation set to dirty read, lock mode set to not wait. Running it during quiet time when the majority of OLTP users are off the system, yields results in a time that can be deemed reasonable and basically what I would have expected. However, when I run the same thing during the more or less busy time, the elapsed time grows by a factor of 4-5! Well, the table is not a hardest hit in the database, the I/O seems to be not a bottleneck. There usually a bunch of users and there are update and intent locks associated with them for this table : 118621ec 0 4173f518 154b8100 HDR+U 2200002 10f206 0 14a55148 0 41746398 15159a1c HDR+U 2200002 8add06 0 15159a1c 0 41746398 151b9204 HDR+IX 2200002 0 0 1516e9ac 0 5fd50818 1518b5c0 HDR+U 2200002 b30c04 0 15170518 0 3662f258 151b8128 IX 2200002 0 0 15176768 0 5fd5bed8 15a6797c HDR+U 2200002 aaa701 0 15179edc 0 5fd63198 1517ac44 IX 2200002 0 0 1517cd2c 0 5fd64b18 1515f03c IX 2200002 0 0 1517db30 0 34054b58 151b9370 HDR+U 2200002 a20701 0 15184ffc 0 36635858 1517985c IX 2200002 0 0 1518b5c0 0 5fd50818 1505dae4 IX 2200002 0 0 151b86a4 0 5fd63198 15179edc HDR+U 2200002 74db06 0 151b86d8 0 36635858 15184ffc HDR+U 2200002 bd2704 0 151b8ab4 0 5fd64b18 1517cd2c HDR+U 2200002 b4dc01 0 151b9370 0 34054b58 151b8fc8 IX 2200002 0 0 1542d29c 0 3662f258 15170518 HDR+U 2200002 bbfe04 0 154b8100 0 4173f518 15443578 IX 2200002 0 0 15a6797c 0 5fd5bed8 15159c58 IX 2200002 0 0 At the same time, looking at the flags in onstat -u output I don't see any session waiting on a lock.... The SELECT that I am running is not waiting on anything either. Whats the catch ? Any ideas will be greatly appreciated ! Ilya Bogorad North American Leisure Group Toronto, Canada ---------------------------- Sent via Deja.com http://www.deja.com/ Before you buy.

My question to you is, How many extents does this table have? That could be a performance issue. ibogorad@yahoo.com wrote: > Folks, > > I broke my head over this one. > So, I have this table in a third party application. The table has row > level locking, contains around 400000 rows of 220 bytes each. > > The way it behaves under the load is unique to this table only, the > rest of them behave pretty much normal. > > Let's say, I run a SELECT statement against this table during with > isolation set to dirty read, lock mode set to not wait. Running it > during quiet time when the majority of OLTP users are off the system, > yields results in a time that can be deemed reasonable and basically > what I would have expected. However, when I run the same thing during > the more or less busy time, the elapsed time grows by a factor of 4-5! > Well, the table is not a hardest hit in the database, the I/O seems to > be not a bottleneck. There usually a bunch of users and there are > update and intent locks associated with them for this table : > > 118621ec 0 4173f518 154b8100 HDR+U 2200002 10f206 0 > 14a55148 0 41746398 15159a1c HDR+U 2200002 8add06 0 > 15159a1c 0 41746398 151b9204 HDR+IX 2200002 0 0 > 1516e9ac 0 5fd50818 1518b5c0 HDR+U 2200002 b30c04 0 > 15170518 0 3662f258 151b8128 IX 2200002 0 0 > 15176768 0 5fd5bed8 15a6797c HDR+U 2200002 aaa701 0 > 15179edc 0 5fd63198 1517ac44 IX 2200002 0 0 > 1517cd2c 0 5fd64b18 1515f03c IX 2200002 0 0 > 1517db30 0 34054b58 151b9370 HDR+U 2200002 a20701 0 > 15184ffc 0 36635858 1517985c IX 2200002 0 0 > 1518b5c0 0 5fd50818 1505dae4 IX 2200002 0 0 > 151b86a4 0 5fd63198 15179edc HDR+U 2200002 74db06 0 > 151b86d8 0 36635858 15184ffc HDR+U 2200002 bd2704 0 > 151b8ab4 0 5fd64b18 1517cd2c HDR+U 2200002 b4dc01 0 > 151b9370 0 34054b58 151b8fc8 IX 2200002 0 0 > 1542d29c 0 3662f258 15170518 HDR+U 2200002 bbfe04 0 > 154b8100 0 4173f518 15443578 IX 2200002 0 0 > 15a6797c 0 5fd5bed8 15159c58 IX 2200002 0 0 > > At the same time, looking at the flags in onstat -u output I don't see > any session waiting on a lock.... The SELECT that I am running is not > waiting on anything either. > > Whats the catch ? Any ideas will be greatly appreciated ! > > Ilya Bogorad > North American Leisure Group > Toronto, Canada > ---------------------------- > > Sent via Deja.com http://www.deja.com/ > Before you buy.

In article <8ab2qd$gtl$1@nnrp1.deja.com>, ibogorad@yahoo.com wrote: > Folks, > > I broke my head over this one. > So, I have this table in a third party application. The table has row > level locking, contains around 400000 rows of 220 bytes each. > > The way it behaves under the load is unique to this table only, the > rest of them behave pretty much normal. > > Let's say, I run a SELECT statement against this table during with > isolation set to dirty read, lock mode set to not wait. Running it > during quiet time when the majority of OLTP users are off the system, > yields results in a time that can be deemed reasonable and basically > what I would have expected. However, when I run the same thing during > the more or less busy time, the elapsed time grows by a factor of 4-5! > Well, the table is not a hardest hit in the database, the I/O seems to > be not a bottleneck. There usually a bunch of users and there are > update and intent locks associated with them for this table : > > 118621ec 0 4173f518 154b8100 HDR+U 2200002 10f206 0 > 14a55148 0 41746398 15159a1c HDR+U 2200002 8add06 0 > 15159a1c 0 41746398 151b9204 HDR+IX 2200002 0 0 > 1516e9ac 0 5fd50818 1518b5c0 HDR+U 2200002 b30c04 0 > 15170518 0 3662f258 151b8128 IX 2200002 0 0 > 15176768 0 5fd5bed8 15a6797c HDR+U 2200002 aaa701 0 > 15179edc 0 5fd63198 1517ac44 IX 2200002 0 0 > 1517cd2c 0 5fd64b18 1515f03c IX 2200002 0 0 > 1517db30 0 34054b58 151b9370 HDR+U 2200002 a20701 0 > 15184ffc 0 36635858 1517985c IX 2200002 0 0 > 1518b5c0 0 5fd50818 1505dae4 IX 2200002 0 0 > 151b86a4 0 5fd63198 15179edc HDR+U 2200002 74db06 0 > 151b86d8 0 36635858 15184ffc HDR+U 2200002 bd2704 0 > 151b8ab4 0 5fd64b18 1517cd2c HDR+U 2200002 b4dc01 0 > 151b9370 0 34054b58 151b8fc8 IX 2200002 0 0 > 1542d29c 0 3662f258 15170518 HDR+U 2200002 bbfe04 0 > 154b8100 0 4173f518 15443578 IX 2200002 0 0 > 15a6797c 0 5fd5bed8 15159c58 IX 2200002 0 0 > > At the same time, looking at the flags in onstat -u output I don't see > any session waiting on a lock.... The SELECT that I am running is not > waiting on anything either. > > Whats the catch ? Any ideas will be greatly appreciated ! > Nothing specific comes to my mind. I would check the indexes on this table. If possible drop em and recreate them. Check the query and the query plan may be ? What version of the Engine and platform ? Sun Solaris / 7.2? ??? Ram S. > Ilya Bogorad > North American Leisure Group > Toronto, Canada > ---------------------------- > > Sent via Deja.com http://www.deja.com/ > Before you buy. > Sent via Deja.com http://www.deja.com/ Before you buy.

Guys, Here is an update as requested: 1) Extents are kept at bay - there are very few of them, besides, this would be an unlikely cause 2) Query plan stays the same. The concern here is not the elapsed time itself, but rather, how it degrades when there is a load on the system. 3) There are five indices on the table, all of them are very reasonable. This is 24x7 shop, so ... I don't have a luxury of rebuilding the indexes "just to be safe"... and even on seq scan selects I am seeing this slow down. Thanks a lot for you thoughts ! Ilya Sent via Deja.com http://www.deja.com/ Before you buy.

ibogorad@yahoo.com wrote: > > Folks, > > I broke my head over this one. > So, I have this table in a third party application. The table has row > level locking, contains around 400000 rows of 220 bytes each. > > The way it behaves under the load is unique to this table only, the > rest of them behave pretty much normal. > > Let's say, I run a SELECT statement against this table during with > isolation set to dirty read, lock mode set to not wait. Running it > during quiet time when the majority of OLTP users are off the system, > yields results in a time that can be deemed reasonable and basically > what I would have expected. However, when I run the same thing during > the more or less busy time, the elapsed time grows by a factor of 4-5! > Well, the table is not a hardest hit in the database, the I/O seems to > be not a bottleneck. There usually a bunch of users and there are > update and intent locks associated with them for this table : [SNIP] > At the same time, looking at the flags in onstat -u output I don't see > any session waiting on a lock.... The SELECT that I am running is not > waiting on anything either. > > Whats the catch ? Any ideas will be greatly appreciated ! I'd guess not enough buffers to handle your query plus the peak load. Post onstat -p, onstat -g iof, onstat -g iov, your ONCONFIG file. -- Art S. Kagel & Family kagel@erols.com