Re: Tuning Assistance

at this point, this is more of a proactive approach. The server is going to continue to grow from a user standpoint, and I'd rather head off any problems before the arise than deal with them after the fact.

In article <7korl9$jak$1_at_nnrp1.deja.com>,   mpir_at_compuserve.com wrote:
> A single tablespace is just suspicious, not automatically bad.
>
> The real issue is how many datafiles and disk drives and how is the
> data distributed accross the files and drives. Multiple tablespaces
> make that analysis easier.
>
> What exactly is the problem? What are your performance snags? If the
> response time is acceptable, why not leave it alone? If it ain't
broke,
> don't fix it.
>
> Although more db_buffers and sort space never hurt.
>
> In article <7koq9h$in3$1_at_nnrp1.deja.com>,
> rspeaker_at_my-deja.com wrote:
> > Hi gang,
> >
> > I am looking for a little tuning advice here. Oracle v8.0.4.3.1
> > running on AIX 4.2.1.
> >
> > First point to keep in mind, I am trying to tune a database running
as
> > a backend to a 3rd party front end. Understand upfront that this
will
> > be like trying to teach a gorilla to sing opera. The application
> > stores everything, EVERYTHING, in a single tablespace. Anyhow, here
> is
> > what I get from utlbstat/utlestat.
> >
> > Library Cache:
> > -------------
> > all of my 'gethitratio' and 'pinhitratio' stats are 99.8% or higher,
> > except for the SQL AREA. It is at 54.5% and 61.6% respectively.
> > Reloads are about 1% of pins, and invalidations are near zero.
> > Currently my shared_pool size is set at 128 MB, but if I issue the
> > command "select * from v$sgastat where name = 'free memory'"
> throughout
> > the day, it reports only a few meg of free memory. Is that normal?
> > What else could be causing such a low hit ratio in just the SQL
AREA?
> > I realize it may be poor SQL statements but I'm not sure if there
is a
> > way in the application to trace the individual statements .. I may
> have
> > to do it at the session or system level.
> >
> > My logical hitratio (db block gets + consistent gets / dbbg + cg +
> > physical reads) is 89% for JFS. To me that is pretty good, but
could
> > probably be better. Would increasing DB_BLOCK_BUFFERS help?
> >
> > My FREE BUFFER SCAN RATIO (free buffers inspected / DBWR buffers
> > scanned) seems to be good, less than 1%. But my average number of
> > reusable buffers (dbwr free buffers found / dbwr make free requests)
> is
> > only 49, and I don't know how to tell if that is good or bad,
> > especially since my DIRTY BUFFERS INSPECTED is nearly 50,000. The
> > tuning class told us that high avg reusable buffers and low dirty
> > buffers inspected means DBWR is performing efficiently ... but what
do
> > I compare these numbers to to determine if they are "high" or "low"
?
> >
> > I am doing over 99% of my sorts in memory.
> >
> > The 'per transact' value of my table scans (long tables) is 0.33
> >
> > The value in my 'buffer is not pinned count' is over 1/2 of that in
> the
> > 'buffer is pinned count' ... should that be a concern? If so, how
do
> I
> > rectify it?
> >
> > I do have some noticeable wait times for various events, but which
> ones
> > should I be most concerned about?
> >
> > Latch hit ratios, both "wait" and "no-wait" are all 99% or higher.
> >
> > Dictionary cache hitratio over 99%.
> >
> > No TRANS_TBL_WAITS for any of the rollback segments.
> >
> > Low usage in SYSTEM and TEMP tablespaces.
> >
> > I'll appreciate any comments or suggestions offered.
> >
> > Thanks,
> > Roy
> >
> > Sent via Deja.com http://www.deja.com/
> > Share what you know. Learn what you don't.
> >
>
> --
> Joseph R.P. Maloney, CCP,CSP,CDP
> MPiR, Inc.
> 502-451-7404
> some witty phrase goes here, I think.
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Wed Jun 23 1999 - 08:29:34 CDT