The Oracle collector allows to insert the following types of anomalies in an Anomalies Profile:
Buffer busy wait time (% of total non-idle waits time)
"Buffer busy" waits usually indicate that heavily inserted tables should be recreated with multiple free lists or that there are insufficient rollback segment.
Direct path IO wait time (% of total non-idle waits time)
Enqueue wait time (% of total non-idle waits time)
During Direct Path operations, the data is asynchronously written to the database files. At some stage the session needs to make sure that all outstanding asynchronous I/O have been completed to disk.
(direct path read + direct path write)
Free buffer wait time (% of total non-idle waits time)
"Enqueue waits" occur when a process is waiting to obtain a lock. This may mean contention for specific rows in the database, table locks resulting from unindexed foreign keys for Oracle internal locks.
This event may be an indication that something is either wrong with the code or possibly the physical design.
This wait event indicates that a server process was unable to find a free buffer and has posted the database writer to make free buffers by writing out dirty buffers. A dirty buffer is a buffer whose contents have been modified. Dirty buffers are freed for reuse when DBWR has written the blocks to disk.
If this event occurs frequently, then examine the session waits for DBWR to see whether there is anything delaying DBWR.
Latch free wait time (% of total non-idle waits time)
A latch is a low-level internal lock used by Oracle to protect memory structures. The latch free event is updated when a server process attempts to get a latch, and the latch is unavailable on the first attempt.
Latch free waits can occur for a variety of reasons including library cache issues, OS process intervention, and so on
Log buffer wait time (% of total non-idle waits time)
Log switch wait time (% of total non-idle waits time)
If this value is too high, log buffers are filling faster than being emptied. You then have to consider to increase the number of logfile groups or to use larger log files.
Write complete wait time (% of total non-idle waits time)
Log switch wait indicates that either your ReDo logs are too small or there are not enough log file groups
Log file parallel write
"Write complete" wait occurs when a session tries to modify a block which is currently being written to disk by the database writer process.
"Write complete" waits often indicate inefficiencies in the database write process or untuned disk I/O.
The "log file parallel write" wait indicates how quickly the log writer process flushes the redo buffer. As the wait times on this event become higher, you will notice additional Wait Events such as
log buffer space,
log file switch (archiving needed), etc.
Buffer cache hit ratio
Percentage of time a required data block is found in the buffer cache. A value greater than 90% is desirable.
Increasing the db_block_buffer configuration parameter (number of buffer cache blocks) will improve the buffer cache hit ratio.
Percentage of CPU used
Percentage of busy Dispatchers.
Dispatchers coordonate the allocation of shared servers to client tasks. They receive SQL requests from clients and pass the requests to shared servers.
Shared server busy
Percentage of busy Shared servers.
A shared server process can service multiple user processes. They perform the SQL operations.
Parallel query server busy
Percentage of busy Parallel query servers.
Parallel query servers support execution of parallel queries and DML statements.
SGA Free memory
Amount of SGA free memory.
Library cache hit ratio
The percentage of reads or executions that do not result in reparsing or reloading.
Library cache get hit ratio
The frequency with which a matching SQL statement is found in the shared pool when a SQL parse request is issued by a session. To improve this ratio, you can use bind variables instead of literals in your SQL statements.
Library cache pin hit ratio
The frequency with which a SQL statement executed by a session has been found in the shared pool. A value lower than 99% means that the Shared pool is too small.
Dictionary cache hit ratio
The frequency with which a database object structure is found in the shared pool when a SQL statement is issued. A value lower than 95% means that the Shared pool is too small.
This is an indication of how many times a piece of SQL is executed for each time it is parsed. A repeated execution without reparsing is achieved by holding an open cursor on the statement.
Chained fetch ratio
Rate at which chained rows are encountered. If the value is greater than 0.1%, it means that PCTFREE is too low for a table (amount of space within a block reserved for updates).
Index fetch ratio
Rate at which rows are fetched by index. A low value may mean that there are lots of table scans.
Disk sort ratio
Should be less than 5% - higher values are an indicator to increase
SORT_AREA_SIZE, but you of course have to consider the amount of physical memory available on your machine
Number of sessions that are currently active.
Copyright © AgileLoad. All rights reserved.