Re: Optimal values in init.ora file

ejsanchez_at_gmail.com wrote in message news:<1101914444.467079.255630_at_z14g2000cwz.googlegroups.com>...
> Mark D Powell wrote:
> > Rather than just take wild guesses about what your settings should be
> > I think you should run a statspack report and look to see if any
> areas
> > appear to be a problem.
>
> OK, I'm begining to read about the statpack...
>
> > The selected parameters you show indicate that you have a large pool
> > allocated larger than the shared pool but you do not display any MTS
> > or PQO parameters that would require use of the large pool parameter
> > at other than the calculated value. Incomplete information often
> > results in incorrect advice.
>
> We don't use any MTS or PQO, so, that means I can delete the value? or
> set it to 0?
>
> >
> > What kind of application do you have that needs a session to be able
> > to hold a 1,000 open cursors?
>
> it's just a client/server application with 40 concurrent users, Forms
> Developer
> in the client side.
> How many open cursors I should have so?
>
> >
> > Since you apparently rebuilt the database I hope you recalculated the
> > object statistics.
> >
> Sorry if this are dumb questions, but how do I calculate the object
> statistics?
> thanks in advance.
> regards,
> -eduardo s.m.

For open cursors I would guess that most shops have a value between 100 and 512.

I think in the absence of MTS (shared server) and PQO that you should let the large pool value default.

Your shared pool value looked a little small. Oracle recommends at least 64 meg for their 64 bit systems in the 9.2 documentation (Reference Manual). You might not need that much but if you have very many stored procedures/functions/packages I would definitely bump the value up from the 18M listed.

If you have a shared pool allocation problem it will probably show up in the Statspack reports as latch waits. You do not need to understand every value on the report for the report to be of value to you. Just run a sample, maybe 10 minutes between snapshots, at prime time then look to see what stands out in the report.

You generate object statistics using the ANALYZE command or dbms_stats procedure. For 8.1 I would probably still use analyze with a moderate sample size.

HTH -- Mark D Powell -- Received on Wed Dec 01 2004 - 15:08:22 CST