Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Interpretation of Statspack reports

Re: Interpretation of Statspack reports

From: Cristian Cudizio <>
Date: Wed, 22 Aug 2007 22:46:59 -0700
Message-ID: <>

On 23 Ago, 00:32, joel garry <> wrote:
> On Aug 22, 7:24 am, Helma <> wrote:
> > This is really interesting :) I hear of such design for a first time.
> > Each table in it's own tablespace, interesting. I wonder who could
> > come up with such an idea?? :) It's no doubt you would have problems
> > with such a system... by the way, what problems do you have exactly?
> > It seems you forgot to tell us.
> > Privyet Andrei,
> > I'm not sure what reasoning is behind the one-to-one relation of
> > tables and tablespaces. It isn't very relevant for the problems
> > encountered with the database, but i wanted to give you a taste of the
> > level of expertise that this application has been built with. Seeing
> > 900 tablespaces is most promising for further finds if you're looking
> > for obvious wrongs in the databasedesign.
> I saw a system that had an installation option for such a
> configuration, I thought it was pretty funny at the time. Somewhen I
> got the idea that it is a common thing from the DB2 world. The one
> plus could be with the granularity of restores (if it is backed up
> properly), since the tablespace can be considered the basic unit of
> restoration. The downside is each data file needing to be touched
> during housekeeping operations by the database - this may account for
> some unnecessary cpu usage, as the blocks of the system tablespace
> that follow datafile information need to be examined so often.
> Does your alert log have any messages like "checkpoint incomplete?"
> That would make the log file waits something to look a little closer
> at. As it is, you may want to do some redo tuning.
> Charles' advice is very good, I would just add you may want to group
> tables into tablespaces by some common characteristics like volatility
> or object size. It's not all that much worth worrying about as long
> as you use LMT's and reasonable PCTFREE and PCTUSED values.
> > The application owner assured me that the database is growing way
> > beyond the prognoses, and that the loading cannot be the cause -
> > please check out what this database is doing. So i ran a statspack to
> > return the top-sql, the loading is indeed the main process.
> Not sure exactly what you mean by the growing statement.
> > But i'm just curious about the report and i am looking into it in
> > order to increase my knowledge on this topic. ( And, i was secretly
> > hoping to find more interesting design features). It's hard to find
> > consistent and at the same time advanced information about this
> > reportreading. Thanks for pointing out that the CPU time needed to be
> > divided over the CPU's - i didn't think about that. And thanks about
> > the link, i'll read it :)
> Start googling, there is plenty of statspack info out there. There
> are even sites (of varying quality) that will take your statspack and
> tell you what you need to do to your system.
> > H.
> > PS I didn't want to present the whole report burb of information
> > because there is not yet an urgent problem at hand, i don't want to
> > burden everyone here with me playing around. ;-)
> Nice of you to ask first!
> The shared server stuff is for scaling upwards in users, explicitly
> not for performance.
> Some more info about this group:
> jg
> --
> is bogus.
> Just one little NIC...,1,680...

Just a question: maybe that Oracle has some problem to manage over 1800 datafiles?
If i correctly rember, default value for parameter datafiles when the controlfiles are created is 100.
In addition to good suggestions of Charles i can only suggest you also the use of permon to see
at OS level what is happening.


Cristian Cudizio Received on Thu Aug 23 2007 - 00:46:59 CDT

Original text of this message