Inspect Your Database Objects Key Performance Information

So how do you drill down on your objects internal performance metrics?

"Speedway" can provide you with an interactive object manifest where you can sort by various key performance metrics. Follow the three steps below.

Step 1: Generate your object manifest and sort by one of the many key performance metrics

Step 2: Simply drill down on any object by plotting its key performance metrics to graph

Step 3. Object Tuning

Once we have an insight into the objects key performance indicators we can plan the tuning approach. Consider the following tuning methods:

Buffer Busy Waits

Buffer busy waits are due to block contention. ORACLES Active Session History has two fields that can be used to identfify the reason for Buffer Busy Waits. Review DBA_HIST_ACTIVE_SESS_HISTORY.P3TEXT where DBA_HIST_ACTIVE_SESS_HISTORY.event = "buffer busy waits".

 

Reason Codes:

Row Lock Waits

Row lock contention occurs when an update is unable to proceed due to another session update which has not yet been committed. Review application logic to avoid row update contention.

ITL Waits

Consider increasing INITRANS setting if experiencing high transaction slot waits.

Global Cache Buffer Busy

Blocks are moving from one node to another over interconnect. Consider reducing the number of blocks moving across interconnect by using one of the following methods:

  • Table\Index Partitioning

  • Indexing

  • Smarter Workload Delegation Across Clustered Nodes

  • Review Cluster\Network Performance Statistics

 

Chain Row Excess

The objects rows are being chained. Consider reducing the row chaining by performing the following:

  • Table Reorganisation

  • Setting a more appropriate PCTFREE value

Complete Database Performance Management