How to Identify a Buffer Cache Chain Latch Problem Internally, Oracle uses various types of structures to control access to elements of the databases including latches and various types of enqueuein...
Reducing lock contention Lock, or enqueue waits occur when a session waits to obtain a lock. In most cases, this occurs because of a lock on a table or row that the session wants to lock or modify....
Analyzing the Execution Plan When examining an execution plan, look for inefficiencies and opportunities to improve performance. When you see any of the following, you have an opportunity to tune the ...
Adding Logfiles To add a redo log, the following command is used: ALTER DATABASE database name ADD LOGFILE THREAD y GROUP n (file specification, file specification) SIZE x; or: ALTER DATABASE dat...
The database writer process (often referred to as DBWR from the times when only one was allowed, but more properly referred to as DBWn) is responsible for asynchronously writing out dirty buffers in t...
Sometimes, data blocks are read in the database, and are infrequently reused. Often in a database we also have data blocks that are of frequent use (hot blocks). It is for these types of database bloc...
Processing SQL Statements The first time a select query is executed inside the Oracle engine, the statement will generally need to proceed through 4 different stages in order to return results back to...
Log file space/switch waits Log file space/switch waits occur when a redo log entry cannot be made. A redo log cannot be written to when: there is no free space in the redo log buffer, or it is i...
Once the application and memory areas have been tuned, the next performance bottleneck can be the disk subsystem. A number of performance indicators can suggest a need to improve Oracle disk I/O. A ma...
Isolating and Tuning Problem SQL Spotlight on Oracle's Top SQL drilldown allows you to identify the SQL that is consuming the most resources on your system. Tuning this SQL can be one of the most e...
RAC uses cluster interconnects to transfer blocks between the nodes participating in the cluster. A block transfer occurs when a user session connected to one instance requests a block being held in t...
Improving Redo Log Writer Performance Redo logs contain details of transactions that may not yet have been written to the data files. The primary purpose of the redo logs is to allow for the recove...
