Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Fundamental database design question

Re: Fundamental database design question

From: Andrew Mobbs <andrewm_at_chiark.greenend.org.uk>
Date: 15 Aug 2000 01:12:04 +0100 (BST)
Message-ID: <SJD*1kIzo@news.chiark.greenend.org.uk>

Dave Bottom <brooktrout_at_ctel.net> wrote:
>
>I've put oracle home on one disk (not stiped).
>I've got data, hopefully load balanced, on 7
>I've put my indexes on 2
>I've put my temp space on 2 of these with a large stripe size (64K)
>I've got my rollbacks and redologs on 2
>
>I still experience disk I/O bottlenecks occasionally.
>Some of this is poor SQL tuning, we're working on that.
>Some of this is poor archetecture, we're working on that. What I've recently
>done is take some tablespaces where multiple tables exist that get
>simultaniously read and written and broken them up into multiple
>tablespaces, migrated the tables, and spread them over multiple disks. It
>helps.
 

A good caching controller can really help for read/write intensive tables.

If you can identify tables which don't contend, then putting them on as large a stripe as you reasonably can is useful. However, the original poster only had 10 disks, so there's less flexability there.

I'm surprised that you seperate all indices from all tables. Is there not more to be gained by having two or more stripe sets, each containing both indices and data (not to the same table), such that potentially contending accesses to data happen on different stripe sets? Or is it as good, and much easier, to just lump all data in One Big Stripe?

I'm interested that you put your redo & rollback on the same stripe set. I've tried this and found no problem, but most of the literature seems to be against this sort of thing.

>I guess what I'm trying to say is theory is great but in practice you need
>to monitor your database load, and make changes based on what is happening.

Definitely, even if it's just looking at the output of iostat once in a while, or running it through a report script, monitoring a database is required.

-- 
Andrew Mobbs - andrewm_at_chiark.greenend.org.uk 
Received on Mon Aug 14 2000 - 19:12:04 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US