Re: Interpretation of Statspack reports

On 23 Ago, 00:32, joel garry <joel-ga..._at_home.com> wrote:
> 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/67http://jonathanlewis.wordpress.com/2006/11/29/analysing-statspack-pt1/http://www.oracle.com/technology/deploy/performance/pdf/statspack_tun...
>
>
>
> > 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,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.

regards

Cristian Cudizio
http://oracledb.wordpress.com
http://cristiancudizio.wordpress.com Received on Thu Aug 23 2007 - 00:46:59 CDT