RE: Contention on a table

What I know. 1. One table seems to take 4-5 times as normal to access under heavy load. 2. Other tables are still accessed quickly. 3. The table which is accessed slowly has 400,000 rows What might be nice to know. 1. Version 2. Platform 3. Hardware. 4. Quantitative response times. 5. Query and query plan for slow query. 6. Size of tables which still have quick access times. 7. Queries and query plans for access of tables with quick access time. 8. onstat -p. 9. onstat -c. 10. vmstat during heavy and light loads. 11. # of active users during heavy and light loads. I am just trying to rule out or narrow down the possibilities of what could be happening, I am not good enough to work off what I know right now. Will >===== Original Message From ibogorad@yahoo.com ===== >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. ------------------------------------------------------------ This e-mail has been sent to you courtesy of OperaMail, as a free service from Opera Software, makers of the award-winning Web Browser, Opera. Visit us at http://www.opera.com/ or our portal at: http://www.myopera.com/ Your free e-mail account is waiting at: http://www.operamail.com/ ------------------------------------------------------------