RE: ** high water mark for small tables
- From: "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>
- To: <ajoshi977@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 26 Jul 2009 20:11:21 -0500
Hi Joshi,
Any table that that occupies fewer blocks after a rebuild type operation
will have improved performance for a full table scan since fewer blocks
have to read for the full table scan. It's likely on smaller tables the
improvement wouldn't be enough for most folks to notice. Of course if
you have a small table that is being hammered by many queries all doing
full table scans, then the individual query improvement might be small
but the fact that each query is doing less work (fewer LIOs) then the
over all system is likely to improve.
Of course some times it's good to have data spread over many blocks too.
RAC environments can benefit from data being sparsely packed data since
this can mean that there is less pressure for the blocks to be pinged
between nodes. But this has more to do with the setting of PCTFREE and
not the HWM, so that is likely a different discussion anyway.
As a side note, I find it funny in a sick way that these days we talk
about table less then a Meg as Tiny...
-----------------------
Ric Van Dyke
Hotsos Enterprises
-----------------------
Hotsos Symposium
March 7 - 11, 2010
Be there.
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of A Joshi
Sent: Sunday, July 26, 2009 8:46 PM
To: oracle-l@xxxxxxxxxxxxx
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
Other related posts:
