Re: partioning

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Tue, 15 Nov 2011 14:12:35 +0100
Message-ID: <4EC26543.1070000_at_roughsea.com>



On 11/15/2011 01:25 PM, Joel.Patterson_at_crowley.com wrote:
>
> Someone has requested that we cost out some thing including portioning and suggested that to get the "performance benefit out of partitioning you needed separate physical disks".
>
> It would be 11.2.0.3, we use a SAN, and have a variety of RAID groups including 10, 5, and 6. I don't expect this to ever get to that size, right now it is about 100Gb, but size hasn't been discussed yet. (we are meeting in the future).
>
> I wonder just how much data is needed to realize a cost/benefit, and other considerations such as channels etc., and basically, is that true and accurate? I have asked him for some supporting docs.
>
> I remember hearing that raw disks could give you 10% more -- until you dug a little further and realize that you would need about a Terabyte of data to realize such an improvement - in reality.
>
> Any comments or experience is appreciated, (including supporting docs if handy).
>
>
> Joel Patterson
> Database Administrator
> 904 727-2546
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

Joel,

   I don't think that one should first think "disks" when one think about partitioning. If you consider a table or a partition as a kind of pool of Oracle blocks, whenever you partition, somehow you redirect your inserts to a smaller pool - you are, if I dare say, lowering entropy. That means that parallel inserts may conflict more if they go to the same partition, and also that selects may perform fewer logical I/Os if there is in your query something that allows partition pruning. I am not sure that the comparison is very good, but for me it's akin to the clustering factor of indexes, primarily more a question of how rows are distributed among Oracle blocks than how Oracle blocks are stored on disk.

HTH,

-- 
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>


--
http://www.freelists.org/webpage/oracle-l
Received on Tue Nov 15 2011 - 07:12:35 CST

Original text of this message