Thanks for the question, Rob.
Asked: February 15, 2013 - 1:12 pm UTC
Last updated: February 15, 2013 - 1:34 pm UTC
Version: 11.2.0
Viewed 1000+ times
You Asked
Hello Tom,
I support 11gR2 on AIX 7.1 and the application throws a lot of Select for Update queries to the database (the vendor acknowledges this but says they can't do anything about it.)
My question is, if I place all the tables with a avg_row_len of less than, let's say, 200 into a tablespace with a small block size - maybe 2k - do you think that would reduce the occurrences of DB blocking/locking?
Currently, the tables are in a tablespace with an 8k block size.
My theory is if the block size is smaller there will be less rows in the block and that would result in less locking and maybe speed up the application some.
Thanks.
and Tom said...
the select for updates block because they are going after rows that are already locked - nothing to do with the block really (unless you are suffering from large ITL waits, not TX-enqueue waits).
So, if you had one row per block and two sessions tried to lock it - they would still block, just the same as if you have 100 rows per block.
we don't lock at the block level, it is done at the row level. and it wouldn't matter if you have 1 row/block or 10000000 rows/block.
Is this answer out of date? If it is, please let us know via a Comment