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

Jon Fife wrote:
> 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

Oracle will not effectively use the memory in the system, unless you as the DBA allow it to use the memory. The initialization parameters that you specify determine the upper bound or minimum memory used for various activities in the database (this is Oracle version specific).

If you don't mind gathering statistics on all tables and indexes in the schema (all rows, even if updated statistics are not needed), a command like the following can be used without the need to enable table monitoring (perform during off hours):
dbms_stats.gather_schema_stats(ownname=> 'SYSMAN', CASCADE=> true);

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Mon Jul 17 2006 - 09:20:44 CDT