Re: How long should statspack.snap take to run?

hpuxrac wrote:
> jon.fife_at_gmail.com wrote:
> > Hi all,
> >
> > I've been tossed into a vacant DBA role and I'm trying to pick up the
> > pieces the former DBA left in his wake. It doesn't seem that he had any
> > sort of history for performance baselines, so I've been playing around
> > with statspack. When I do the statspack.snap it takes around 15 minutes
> > to complete and I was just wondering if that was outrageous or typical.
> > It is a 9.2.0.4 server with roughly 150GB of data and 150 concurrent
> > users during the day doing OLTP. I've done some googling to see if the
> > time spent was sane, but didn't find much there. It seems some people
> > set up statspack to run every 30 minutes or so, which makes 15 minutes
> > seem high...
>
> To answer your question, which the first 2 people responding did not
> bother to do is that yes 15 minutes is a problem doing a level 5 snap.
> It should be much much faster.
>
> Tell us a little more about your system? What os, how much memory, how
> many cpu's ... do you have extra cpu capacity during those times when
> you are taking snapshots or is your system totally maxed out?
>

HP-UX 11.0, 26GB of memory, 4 CPUs.

I have yet to see the CPUs or memory anywhere near maxed out, so I don't think it is a bottleneck there. I suspect that gathering stats on the SYS schema would help, but I'm very wary of doing that due to the ask-tom article posted earlier in the thread. I'm not familiar enough with the applications running on the DB to say that they don't query the dictionary.

A tangential question -- do I have to turn on table monitoring to effectively run the gather_schema_stats procedure? Or is that just if I want to use the stale options?

> You might want to clean up the statspack stuff by dropping and
> re-installing the tables and stuff for statspack.

I did that once already (ran spdrop and spcreate) when I was running into the primary key bug, so the data in there should be clean.

Thanks everyone for the advice,

Jon Received on Mon Jul 17 2006 - 08:45:07 CDT