Buffer Nowait

If this ratio is low, check the Buffer Wait Stats section for more detail on which type of block is being contended for.

A value close to 100% indicates minimal time spent waiting for redo logs to become available, either because the logs are not filling up very often or because the database is able to switch to a new log quickly whenever the current log fills up.

A low buffer hit ratio does not necessarily mean the cache is too small: it may very well be that potentially valid full-table-scans are artificially reducing what is otherwise a good ratio. A too-small buffer cache can sometimes be identified by the appearance of write complete waits event indicating hot blocks (i.e. blocks still being modified) are aging out of the cache while they are still needed; check the Wait Events list for evidence of this event.

A too low ratio indicates too many disk sorts appearing. One possible solution could be increasing the sort area/SGA size.

A low library hit ratio could imply that SQL is prematurely aging out of a too-small shared pool, or that non-shareable SQL is being used. If the soft parse ratio is also low, check whether there is a parsing issue.

A soft parse occurs when a session attempts to execute a SQL statement and a usable version of the statement is already in the shared pool, so the statement can be executed immediately. The hard parse is the opposite and an expensive operation. When the soft parse ratio falls much below 80%, investigate whether you can share SQL by using bind variables or force cursor sharing by using the init.ora parameter cursor_sharing (new in Oracle8i Release 8.1.6). But before drawing any conclusions, compare the soft parse ratio against the actual hard and soft parse rates shown in the Loads Profile. If the rates are low, parsing may not be a significiant issue in your system. Furthermore, investigate the number of Parse CPU to Parse Elapsed below. If this value is low, you may rather have a latch problem.

Start with these topmost events and find out about details in the corresponding block, e.g. for "db file * read" check the TableSpace IO (and File IO) blocks to identify the possibly affected schemas, and then the SQL Statements by Reads (and Wait Objects) to find out what statements (and/or objects) may need some tuning , for "enqueue" waits look up the Enqueue Activity section of this document. If the CPU is indicated as a bottleneck, check the SQL Statements by Gets. Then continue the same with the next block, All Wait Events

If the value for Avg Blks/Rd is higher than 1, this indicates full table scans. If it grows higher than DB_FILE_MULTIBLOCK_READ_COUNT we must assume that almost every operation on this TS is executed as full table scan instead of using an index first, so you should consider creating appropriate indices or, maybe, increasing the DB_FILE_MULTIBLOCK_READ_COUNT.

If the value for Avg Blks/Rd is higher than 1, this indicates full table scans. If it grows higher than DB_FILE_MULTIBLOCK_READ_COUNT we must assume that almost every operation on this TS is executed as full table scan instead of using an index first, so you should consider creating appropriate indices or, maybe, increasing the DB_FILE_MULTIBLOCK_READ_COUNT.
Average Read Times (AvgRd) of greater than 20..40ms should be considered slow for single block reads. So if this is the case, you should check whether the disks are capable of the required IO rates. If they are, your file-to-disk layout may be causing some disks to be underused while others are overly busy. Furthermore, if the temporary TableSpaces have the most write activity, this may indicate that too much of the sorting is to disk and may require optimization.

If Waits/s are high for a given class, you may consider some tuning: For the undo headers/blocks, adding more rollback segments can help. With data blocks, increasing the size of the database buffer cache can reduce these waits. Segment header waits generally point to the need to add freelists to the affected table. Freelist block waits indicate that the affected segment needs a higher number of freelists - for the Oracle Parallel Server, make sure each instance has its own freelist groups.

RBS stats may not be accurate between begin and end snaps when using Auto Undo Management, as RBS may be dynamically

Potential Fixes for indicated Latch problems are: for the library cache and shared pool latches: adjusting the shared_pool_size and use of bind variables / set cursor_sharing parameter in your init.ora; for redo allocation latches: minimize redo generation and avoid unnecessary commits; for redo copy latches: increase the _log_simultaneous_ copies; for row cache objects latches: increase the shared pool; for cache buffer chain latches: adjust _db_block_hash_buckets; for cache buffer latches: use _db_block_lru_lru_latches or multiple buffer pools. Again, these are potential fixes, not general solutions.