Oracle anomalies

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)

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)

Enqueue 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.

Free buffer wait time (% of total non-idle waits time)

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)

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.

Log switch 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

Write complete wait time (% of total non-idle waits time)

"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.

Log file parallel write

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.

CPU

Percentage of CPU used

Dispatcher busy

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.

Parse/Exec ratio

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

Active sessions

Number of sessions that are currently active.





Go back to the Learning center:


Copyright © AgileLoad. All rights reserved.
Agile Load testing tool| Contact AgileLoad | Terms of Use | Sitemap