Re: Tuning Assistance

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. Received on Tue Jun 22 1999 - 15:28:39 CDT