Re: concepts document part about separating indexes and tablspaces
- From: "Andrew Kerber" <andrew.kerber@xxxxxxxxx>
- To: pythianbrinsmead@xxxxxxxxx
- Date: Thu, 15 May 2008 21:16:26 -0500
I think it would be more correct to say that the storage should be
configured so that it should not be necessary for the DBA to stripe storage
by the manual location of files, ie physically distinct disks. However, the
DBA still has to make sure that the people handling the storage are
configuring properly to stripe and mirror the Oracle data files and redo
logs. I have run into issues where the storage was not sliced properly, and
I have had to have it reallocated on occasion.
On Thu, May 15, 2008 at 8:25 PM, Mark Brinsmead <pythianbrinsmead@xxxxxxxxx>
wrote:
> I remember, all too well, this particular advice. In fact, there was a
> little more to it than this. The idea -- as I recall -- was that you would
> use something like:
>
> (*) One disk for the operating system
> (*) One disk for swap space
> (*) One disk for Oracle software
> (*) One set of disks for data
> (*) One set of disks for index
> (*) One set of disks for rollback segments
> (*) One set of disks for archive logs
> (*) Two sets of disks for online redo logs
>
> It is very important to remember, though, that this advice goes back to (at
> least) version 7.0, maybe farther.
>
> It was probably around 1993 when I first saw this. At that time, software
> RAID was rare, hardware RAID was almost non-existent, and 1 GB of RAM (if
> you could find a server that would take that much) cost a significant part
> of a million dollars. (More than a million if you were purchasing from
> certain vendors.)
>
> Most Oracle databases were built on JBOD storage, and ran on servers with
> maybe 64 *mega*bytes of RAM. Disk drives spun at around 3600 RPM, and
> were maybe one to two gigabytes in capacity. On the bright side, though,
> any database of significant size had a *lot* of them.
>
> The general lack of RAID motivated the methodology above, whose goal was
> not so much intended to encourage a "best practice" (distributing I/O as
> evenly as possible across as many disks as possible) so much as it was meant
> to discourage "worst practices" that would lead to appalling database
> performance or risk unrecoverable data loss by placing too many critical
> pieces of data on a single disk.
>
> As Riyaj points out, things are very different today. Anybody who spends
> as much on hardware as they do on database licenses (you'd be surprised how
> many do not) can easily have hardware-based RAID with large NVRAM caches and
> anywhere from 8GB to 64GB of system memory. Today, with resources like this
> *typically* at our disposal, there are much better ways to configure our
> storage. Its rather a shame that the Oracle documentation seems not to have
> kept pace with the improving hardware.
>
> I don't think I would go so far as to say that it is *inherently wrong* to
> place indexes and data in separate tablespaces. However, it is probably
> safe to say that is it no longer considered a "best practice" to place them
> on physically distinct disks.
>
>
> On Thu, May 15, 2008 at 3:34 PM, Riyaj Shamsudeen <
> riyaj.shamsudeen@xxxxxxxxx> wrote:
>
>> Mark
>> Respectfully, I disagree. I think, separating tables and indexes is
>> simply incorrect advice, if done for performance reasons. If it is done for
>> ease of administration, I can understand that.
>> In OLTP applications, typical pattern is access index, then access
>> table. They are kind of sequential..
>> In DSS applications, it is Full table scan or full index scan, collect
>> rowids and then access table.
>>
>> Now, throw in (big enough) buffer cache and then unix buffer cache or OS
>> buffer cache (don't have a clue about NT).
>>
>> Pattern completely changes. And then touch count based algorithm kicks
>> in !
>>
>> Throw in multiple sessions performing similar operations, pattern gets
>> complicated. So, this advice is not applicable even if there is just two
>> disks. Better approach would be to use ASM or add both disks to one
>> tablespace in the form of multiple files and muck up with extent size etc..
>>
>> One exception is that there is just one table and one index and only one
>> session ever doing any activity and every logical I/O goes physical !
>>
>> --
>> Cheers
>> Riyaj Shamsudeen
>> The Pythian Group - www.pythian.com
>> orainternals.wordpress.com
>>
>>
>> On Thu, May 15, 2008 at 4:01 PM, Powell, Mark D <mark.powell@xxxxxxx>
>> wrote:
>>
>>> The way the statement is written appears to imply that the tablespace
>>> in question is on one real physical disk and that when the indexes are
>>> separated into a different tablespace the index tablespace will be put on a
>>> different physical disk. In this particular case separating the tables and
>>> indexes probably would help performance providing there are multiple
>>> sessions on the database that access other objects in the same tablespace
>>> concurrently. Two disks are normally better than one. The problem is just
>>> about no one has this type of disk arrangement any more. The world is
>>> logical disks made up of multiple physical devices arrayed in RAID-0,
>>> RAID-5, or RAID-10 collections.
>>>
>>> -- Mark D Powell --
>>> Phone (313) 592-5148
>>>
>>>
>>> ------------------------------
>>> *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
>>> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *David Aldridge
>>> *Sent:* Thursday, May 15, 2008 4:32 PM
>>> *To:* davidsharples@xxxxxxxxx; ricks12345@xxxxxxxxx
>>> *Cc:* oracle-l@xxxxxxxxxxxxx
>>> *Subject:* Re: concepts document part about separating indexes and
>>> tablspaces
>>>
>>> It's actually in the DBA Guide, and it's still there now:
>>> http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/indexes.htm#i1006372
>>>
>>> "Using different tablespaces (on different disks) for a table and its
>>> index produces better performance than storing the table and index in the
>>> same tablespace. Disk contention is reduced."
>>>
>>> ----- Original Message ----
>>> From: David Sharples <davidsharples@xxxxxxxxx>
>>> To: ricks12345@xxxxxxxxx
>>> Cc: oracle-l@xxxxxxxxxxxxx
>>> Sent: Thursday, May 15, 2008 3:18:48 PM
>>> Subject: Re: concepts document part about separating indexes and
>>> tablspaces
>>>
>>> because it doesn't
>>>
>>> 2008/5/15 Rick Ricky <ricks12345@xxxxxxxxx>:
>>>
>>>> i was talking to someone about this today. I cannot remember where in
>>>> the Concepts document that it says that separating data from indexes
>>>> improves performances?
>>>
>>>
>>>
>>>
>>
>>
>>
>
>
> --
> Cheers,
> -- Mark Brinsmead
> Senior DBA,
> The Pythian Group
> http://www.pythian.com/blogs
--
Andrew W. Kerber
'If at first you dont succeed, dont take up skydiving.'
Other related posts:
