What is row locking and chaining in Oracle and what is its impact on performance?
+6
A:
Hi Amitabh,
Row locking and Row chaining are two entirely different concepts.
- Row Locking is a mechanism that enables multiple users to modify the same table in a reliable, consistent fashion. It has no impact on performance since you can't disable it.
- Row Chaining is when a row is too large to fit in a single data block and is split over two or more blocks. Having very large rows may impact performance. Basically Oracle will have to visit more blocks to reconstruct each chained row. In particular, this will add IOs on ACCESS BY ROWID operations.
Vincent Malgrat
2010-02-23 14:51:50
+1. Very large rows are not the only reason for Row Chaining though, updating existing rows with larger values can also lead to that -> http://stackoverflow.com/questions/2020231/slow-update-after-truncate/2021259#2021259
Peter Lang
2010-02-23 15:05:39
@Peter: that would be Row Migrating :)
Vincent Malgrat
2010-02-23 15:19:17
Ah, thanks for pointing that out :)
Peter Lang
2010-02-23 15:26:14