Dealing with Oracle Database bottlenecks

Unclogging Oracle Database bottlenecks can range from simple SQL re-coding to examining CPU use and monitoring the network infrastructure.

Oracle Database performance problems can often be attributed to bottlenecks, a situation in which data has to traverse a process or route that increases response time by forcing traffic to “slow down” due to the processes’ inability to move the data expediently.

Oracle databases can suffer from external or internal bottlenecks or even both. The key to resolving those bottlenecks is to find the root cause and location, which takes some DBA skill. Nevertheless, the first place to start is with internal bottlenecks – ones that can be resolved without significant expense by recoding SQL statements, modifying parameters or massaging the database itself in some other fashion. Only when internal bottlenecks have been identified should a DBA move onto the external bottlenecks.

Typically, a properly functioning Oracle Database is in a status where every connected process is either busy performing work or waiting to do so. Those “waits” are conditions in which a session is waiting for something to happen. Waits can be caused by a number of things, from slow disks to locking situations to various kinds of internal Oracle contention.

Outlining potential internal database bottlenecks
There are several events that can impact internal performance, including:

  • Buffer deadlocks – Internal locking and latching causing contention
  • Buffer busy waits – Segment header contention with data buffers creating bottlenecks
  • Control file waits – Oracle updates the control file very frequently, causing delays
  • Latch and lock waits – Waits resulting from serialized access latches                                 
  • En-queue waits – An indication of internal latch and lock contention
  • Log buffer waits – Events such as redo log space requests
  • Undo segment waits – Undo segments can cause serialization waits

However, having a process that is waiting is not always an indication of a bottleneck. Waits can be a simple case of user-initiated activity, such as a coffee break or focusing on a different activity. Nevertheless, it is still prudent to check the reasons behind a waiting process and its impact on response time, which is the real goal of database tuning – optimizing response time.

The response time of a database operation consists of two factors, the service time (execution) plus the wait time: Response Time = Service Time + Wait Time. Identifying and solving wait time issues can be a tedious process, often referred to as wait-based analysis. One of the first steps of wait-based analysis is to find both the number of waits for a particular event and the times involved for each wait. DBAs can use those two pieces of information to identify the magnitude of wait-initiated performance degradation and guide them onto the next phase of identifying bottlenecks.

Gathering that information requires using a few select commands that reveal the information contained within the database. Basic information can be obtained by viewing V$SYSTEM_WAIT_CLASS. However, that only shows wait times and counts across new wait classes. DBAs can also use system-level wait summaries to get a big-picture view of database waits. Those summaries can be generated by running the awrrpt.sql script from the $ORACLE_HOME/rdbms/admin  directory. That will show the top 5 timed events for the specific interval between AWR snapshots.

For more detailed information, a query will need to be built – one that includes objects such as WAIT_CLASS, TOTAL_WAITS and TIME_WAITED, all of which are derived from V$SYSTEM_WAIT_CLASS. The information returned helps DBAs to determine if wait time is due to system or user I/O waits.

Better yet, DBAs can turn to third party tools that not only identify internal bottlenecks, but also identify SQL statements that can be optimized to improve performance. The key to picking a good third party tool, of which there are dozens, comes from making sure that the tool can focus on the wait events that comprise the total response time, both for the system and for individual transactions.

External database bottlenecks can be tricky
External bottlenecks can be a little trickier to identify, simply because there are processes and traffic generated by elements other than Oracle. Most external bottlenecks fall under a few different categories: CPU/server performance, storage performance, and infrastructure performance.

Identifying those external bottlenecks can be a difficult process and involves the use of third- party tools, as well as the cooperation of network administrators and other IT staffers. There are several detailed areas to focus on when identifying external bottlenecks and a good third-party analysis tool should be able to spot them. That analysis should include:

  • Disk Performance: Monitoring scattered reads, full table scans, sequential reads, index probes and so on
  • CPU: Monitoring processor consumption by thread, total usage, peak utilization, primary processes and so on
  • Network: Monitoring SQL*Net metrics, as well as traffic throughput, QoS derived processes, protocol performance, contention, latency and so on

By targeting those areas, DBAs can get a better picture of where a bottleneck is created and then come up with a plan for mitigation that involves other IT stakeholders.

Solving Oracle Database performance problems simply comes down to a process of identifying bottlenecks and coming up with the appropriate fixes to eliminate them. The challenge comes from knowing where to look and how to interpret the data.

Dig Deeper on Oracle database performance problems and tuning