Re: Interpretation of Statspack reports

"joel garry" <joel-garry_at_home.com> wrote in message news:1187821927.693303.246260_at_j4g2000prf.googlegroups.com...
> On Aug 22, 7:24 am, Helma <bad_elef..._at_hotmail.com> 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.
>
> http://www.oaktable.net/getFile/67
> http://jonathanlewis.wordpress.com/2006/11/29/analysing-statspack-pt1/
> http://www.oracle.com/technology/deploy/performance/pdf/statspack_tuning_otn_new.pdf
>
>>
>> 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: http://www.dbaoracle.net/readme-cdos.htm
>
> jg
> --
> @home.com is bogus.
> Just one little NIC...
> http://www.latimes.com/news/nationworld/nation/la-me-lax15aug15,1,6802259.story?coll=la-headlines-nation&ctrack=1&cset=true
>

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