Re: Interpretation of Statspack reports

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