RE: ** high water mark for small tables
Date: Mon, 27 Jul 2009 08:15:42 -0400
Message-ID: <1B7416A9A6594479A6E49865481716D4_at_rsiz.com>
Others already mentioned some potential benefits. It bears repeating (though I suspect from context you already are aware) that it is pretty important to avoid getting on a treadmill about this. Even though there are benefits to be gained in some cases, the disruption of repetitive reorganization is rarely justified (rarely is non-zero, make no mistake) by the payback. Once, or once per pretty long period, is a net benefit more often, but it is still worthwhile trying to figure out whether it actually is a winner for you.
Still, the effects of a rebuild of a small table might surprise you, especially if you are judging size from statistics rather than the used extents. If the insert/delete history of the table has left you with a large empty front (that is the blocks read first in an FTS are completely empty) then you may be reading a lot of blocks to get a few rows. I'm pretty sure empty blocks do not get cached, and there is no LOW water mark. So if you have an itsy bitsy table and it is read a lot by FTS inside loops that can really add up. You can vote for the LOW water mark enhancement on MIX where I posted it. It is explained better there. Finally, if you're going to rebuild a bunch of tables, it is well worth while to determine if there exists a physical order in which the rows will serve you better. Of course if the tables are still subject to insertion and deletion, that ordering may be perishable and please do avoid the aforementioned treadmill.
mwf
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of A Joshi
Sent: Sunday, July 26, 2009 8:46 PM
To: oracle-l_at_freelists.org
Subject: ** high water mark for small tables
Hi,
I am looking at high water mark for tables and doing exp/imp or truncate or
move of tables to improve performance by eliminating fragmentation/lowering
HWM.
For smaller tables : I am assuming exp/imp or move need not be done since
the table is small anyway and will not make much improvement. Especially
tables less than 1 MB. Please let me know if any benefit to do this for
small/tiny tables. Any other things to keep in mind before doing this or
particular tables to avoid. I am saving the statistics. I also think
sometimes if the small table is in join with other big tables : it could
degrade performance after this if explain plan chooses to use index for
small table instead of big or changes plan in some other ways. Any such
experiences?
The reason to do this is : when this was done for some big tables : there was improvement :especially for full table scan. There was improvement for some index with rebuild for access when single sql had multiple index access. So in some cases for big table/index : there is improvement.
In any case : the question here is about tiny tables (less than 1 MB) : is there any gain to do rebuild for tiny table? Has anyone seen any improvement with rebuild of tiny table and what is exact scenario. Thanks a lot. Thanks
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Jul 27 2009 - 07:15:42 CDT