RE: SAME and separating disk and index tablespaces

Maybe we have been lucky. But we use the SAME methodology. We have removed a considerable amount of "human effort" in regards to layout of datafiles and disk layout. And based on the stats that I have seen from the Storage team/SA's, we have *maybe* seen a 2-5% performance hit in access time. That info needs to be confirmed-but the disk technology has been improved so dramatically-that older "rules of thumb" aren't necessary for *specific* environments. There is still logical separation of tables/indexes as mentioned below.. But we typically present a single file system for the datafiles....

Does this work in all cases for us - nope! But it covers a majority of the environments and we address the *unique* environments accordingly. But considering the number of databases and the volume of disk space - more effective for us.

just a comment:-)
greg

-----Original Message-----
Sent: Wednesday, October 08, 2003 8:29 PM To: Multiple recipients of list ORACLE-L

Great responses ! Thanks very much ..

-----Original Message-----
Dave Hau
Sent: Wednesday, October 08, 2003 3:19 PM To: Multiple recipients of list ORACLE-L

Hi Gaja,

I agree that throughput can always be improved by adding more drives to the striped array. However, this does not improve access time. If you have your tables and indexes on the same striped array, necessarily the two I/O's have to be done sequentially, incurring two times access time at a minimum. However, if you separate the two into different arrays, then you can access them in parallel, starting to get data from each disk array in 1* access time. This makes sense esp. in scenarios where response time is more important than throughput, and also in use cases where your access pattern is random rather than sequential.

So I feel that there's a tradeoff between access time and throughput. If you have ten drives, and you stripe all of them into a single array and put both your data and indexes onto this array, you get maximum throughput but you're sacrificing access time for throughput. However, if you build two arrays each consisting of five drives, and put your data and indexes onto each array, you get half of the previous throughput, but you get better access time because now your data and index access can be truly in parallel.

Regards,
Dave

oraperfman_at_yahoo.com wrote:
> Hi Hans/Vikas,
>
> I tend to agree that the old draconian rule that "thou
> shalt always separate indexes from tables" may not
> apply any more. We used to apply that principle in the
> past when the number of available spindles was not
> adequate. Seems like with 256G drives in the market,
> we are being pushed back in time, in some way!!!
>
> The way I look at the problem is purely from an IOPS
> perspective. For example, if each physical disk is
> capable of 256 IOPS (ignore the cache configured here)
> and you have 10 disks in your volume, then the total
> I/O capacity on this volume is 2560 IOPS. Separation
> of objects across multiple volumes may becomes an
> issue, only when the demand for I/O outstrips the
> supply (in this case 2560 IOPS).
>
> Even then, you can always add more drives to the
> existing volume and restripe, i.e., adding 5 more
> drives to 10 drives increases the I/O capacity by 50%.
> At the end of the day, the I/O sub-system does not
> care, whether it is servicing a data segment, index
> segment or undo segment.
>
> But, in certain environments, that I have dealt with,
> there has been a need to separate heavily and
> concurrently accessed objects (does not matter whether
> these objects are all indexes or tables or both). This
> may be true only for certain objects and certain
> queries. So, please don't apply this in a blanket
> fashion.
>
> Empirical data is always the best justification
> mechnism for a configuration exercise such as this.
> Plus, you may have partitioning and other requirements
> such as parallelism that impact the placement and
> availability of your data. This in turn will control
> the number of logical volumes that need to be created.
>
> I think the idea and philosophy behind SAME is noble -
> Use all available drives, so that you do not have
> localized hot-spots. But the implementation of SAME
> and how many volumes you need in your enviroment, is a
> function of your custom needs based on your system and
> application demands. When you over-simplify something,
> you lose the flexibility. The art factor here (which
> requires some planning) is in achieving a balance
> between simplicity, flexibility, performance,
> manageability and availability.
>
>
> Hope that helps,
>
>
> Gaja
> --- Hans de Git <hansdegit_at_hotmail.com> wrote:
>
>>Vikas,
>>
>>Spend an hour on reading this usenet thread:
>>
>>
>
>

http://groups.google.nl/groups?hl=nl&lr=&ie=UTF-8&oe=UTF-8&threadm=brjz8 .15%24707.245%40news.oracle.com&rnum=1&prev=/groups%3Fhl%3Dnl%26lr%3D%26 ie%3DUTF-8%26oe%3DUTF-8%26q%3Drogers%2Bseparate%2Bdata%2Bindex
>
>>It will open your eyes about separating data/index.
>>
>>Still not sure about the redolog stream...Because of
>>the sequential nature
>>of redologfiles. I've read tests that 'prove' it
>>doesn't matter much
>>whether you separate your redolog from 'ordinary'
>>datafiles or not. It does
>>simplify things when you pure SAME.
>>
>>Regards,
>>Hans
>>
>>
>>Reply-To: ORACLE-L_at_fatcity.com
>>To: Multiple recipients of list ORACLE-L
>><ORACLE-L_at_fatcity.com>
>>Date: Wed, 08 Oct 2003 09:54:30 -0800
>>
>>Thanks Gaja ! Does it also make sense from a
>>performance perspective
>>(I/O issues due to concurrent access of index and
>>data ) to separate
>>them or is that point moot once you apply the SAME
>>methodology ?
>>
>>-----Original Message-----
>>Gaja Krishna Vaidyanatha
>>Sent: Wednesday, October 08, 2003 9:24 AM
>>To: Multiple recipients of list ORACLE-L
>>
>>Vikas,
>>
>>The answer is an enthusiastic yes. This is purely
>>from
>>an administrative and manageability standpoint. For
>>example, if you have INDEX and DATA segments
>>separated
>>in 2 different tablespaces, the backup of these
>>tablespaces can be done INDEPENDENTLY. This is
>>relevant, as if you were to rebuild your indexes
>>using
>>the NOLOGGING option between 2 backup jobs. If that
>>were the case, then all you will need to do after
>>the
>>rebuild is complete, is to backup only the INDX
>>tablespace.
>>
>>This is a best practice (if not a requirement) in
>>most
>>production shops, unless you think you can
>>re-re-build
>>your indexes in the event of media failure and you
>>lose your INDX tablespace.
>>
>>
>>Hope that helps,
>>
>>
>>Gaja
>>--- vikas kawatra <vkawatra_at_comcast.net> wrote:
>> > Guys,
>> >
>> > Does it make sense to separate data and index
>> > segments into separate
>> > tablespaces if you create a single logical volume
>> > and all files are
>> > striped using the SAME methodology ?
>> >
>> > Thanks
>> >
>> > vikas
>> >
>> >
>> > --
>> > Please see the official ORACLE-L FAQ:
>> > http://www.orafaq.net
>> > --
>> > Author: vikas kawatra
>> > INET: vkawatra_at_comcast.net
>> >
>>
>>
>>=====
>>Gaja Krishna Vaidyanatha
>>Principal Technical Product Manager,
>>Application Performance Management, Veritas
>>Corporation
>>E-mail : gaja_at_veritas.com Phone: (650)-527-3180
>>Website: http://www.veritas.com
>>
>>__________________________________
>>Do you Yahoo!?
>>The New Yahoo! Shopping - with improved product
>>search
>>http://shopping.yahoo.com
>>--
>>Please see the official ORACLE-L FAQ:
>>http://www.orafaq.net
>>--
>>Author: Gaja Krishna Vaidyanatha
>> INET: oraperfman_at_yahoo.com
>>
>>Fat City Network Services -- 858-538-5051
>>http://www.fatcity.com
>>San Diego, California -- Mailing list and web
>>hosting services
>>
>
> ---------------------------------------------------------------------
>
>>To REMOVE yourself from this mailing list, send an
>>E-Mail message
>>to: ListGuru_at_fatcity.com (note EXACT spelling of
>>'ListGuru') and in
>>the message BODY, include a line containing: UNSUB
>>ORACLE-L
>>(or the name of mailing list you want to be removed
>>from). You may
>>also send the HELP command for other information
>>(like subscribing).
>>
>>
>>--
>>Please see the official ORACLE-L FAQ:
>>http://www.orafaq.net
>>--
>>Author: vikas kawatra
>> INET: vkawatra_at_comcast.net
>>
>>Fat City Network Services -- 858-538-5051
>>http://www.fatcity.com
>>San Diego, California -- Mailing list and web
>>hosting services
>>
>
> ---------------------------------------------------------------------
>
>>To REMOVE yourself from this mailing list, send an
>>E-Mail message
>>to: ListGuru_at_fatcity.com (note EXACT spelling of
>>'ListGuru') and in
>>the message BODY, include a line containing: UNSUB
>>ORACLE-L
>>(or the name of mailing list you want to be removed
>>from). You may
>>also send the HELP command for other information
>>(like subscribing).
>>
>>
>
> _________________________________________________________________
>
>>Chatten met je online vrienden via MSN Messenger.
>>http://messenger.msn.nl/
>>
>>--
>>Please see the official ORACLE-L FAQ:
>>http://www.orafaq.net
>>--
>>Author: Hans de Git
>> INET: hansdegit_at_hotmail.com
>>
>>Fat City Network Services -- 858-538-5051
>>http://www.fatcity.com
>>San Diego, California -- Mailing list and web
>>hosting services
>>
>
> ---------------------------------------------------------------------
>
>>To REMOVE yourself from this mailing list, send an
>>E-Mail message
>>to: ListGuru_at_fatcity.com (note EXACT spelling of
>>'ListGuru') and in
>>the message BODY, include a line containing: UNSUB
>>ORACLE-L
>>(or the name of mailing list you want to be removed
>>from). You may
>>also send the HELP command for other information
>>(like subscribing).
>
>
>
> =====
> Gaja Krishna Vaidyanatha
> Principal Technical Product Manager,
> Application Performance Management, Veritas Corporation
> E-mail : gaja_at_veritas.com Phone: (650)-527-3180
> Website: http://www.veritas.com
>
> __________________________________
> Do you Yahoo!?
> The New Yahoo! Shopping - with improved product search
> http://shopping.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Dave Hau
  INET: davehau123_at_netscape.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: vikas kawatra
  INET: vkawatra_at_comcast.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Loughmiller, Greg
  INET: Greg.Loughmiller_at_cingular.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).