Re: partioning

  • From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Tue, 15 Nov 2011 14:12:35 +0100

On 11/15/2011 01:25 PM, Joel.Patterson@xxxxxxxxxxx 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
>
> --
> //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>


--
//www.freelists.org/webpage/oracle-l


Other related posts: