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