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: Gerry Sinkiewicz <>
Date: Thu, 23 Aug 2007 10:17:30 GMT
Message-ID: <_8dzi.47378$>

"joel garry" <> wrote in message
> 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...

Yes indeed it does sound like an old IBM design. I have seen some DB2 make-overs to Oracle that are one tablespace per table. None done that way lately though, not even from IBM. Received on Thu Aug 23 2007 - 05:17:30 CDT

Original text of this message