Tuning Assistance

From: <rspeaker_at_my-deja.com>
Date: Tue, 22 Jun 1999 20:05:13 GMT
Message-ID: <7koq9h$in3$1@nnrp1.deja.com>

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