Re: Tablespace/Datafile Layout for Datawarehouse

On Jun 8, 8:08 pm, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
> Oracle 10.2.0.3.0, 8 GB memory, Windows 2003 Server
>
> Below is the layout of tablespaces and datafiles for the datawarehouse where
> I work (the X's in the directory path are to protect the innocent/guilty).
> In a previous post here it appears that the drives could be mount points.
>
> Does the layout make sense? Having so many tablespaces on the same
> drive/mount points?
>
> I'm questioning this because we've been experiencing disk utilization rates
> of 98%+ on the F and G drives in the past few weeks.
>

One thing I don't think I've seen yet in this thread: When and what kind of updating is going on in this DW?

If you are seeing this kind of utilization with no updating going on, you may have some dueling queries going on. (See Jonathan's mention in a recent thread here). What sort of waits does Oracle think it has?

How often are you checkpointing? If you have lots of checkpointing going on, the headers of each file are being updated. If that is the case, using fewer files could make a big difference when under stressful load. Are you seeing "checkpoint not complete" errors in the alert log?

Is windows doing anything non-oracle on those drives?

What changed when this started? Have you checked the hardware? Do you have a new person or "dashboard" running lots of unbounded queries?

Also see http://jonathanlewis.wordpress.com/2007/04/30/system-statistics/

jg

--
@home.com is bogus.
Stop fighting, I want my Dick Tracy video wristphone!
http://www.signonsandiego.com/uniontrib/20070613/news_1b13qcom.html