Keeping your Oracle database tuned for peak performance requires an understanding of a number of issues.
After you have tuned the server and network, the next place to look is within the Oracle instance itself. An Oracle instance consists of the memory region of the database, called the System Global Area or SGA, and the Oracle background processes.
Once the instance has been tuned, we look at tuning individual objects (tables and indexes) within the database, and finally we tune the SQL statements that are run against the database
archive monitor process
This is a process on Unix that monitors the archive process and writes the redo logs to the archives.
archiver process
This process is only active if archive logging is in effect. This process writes the redo log data files that are filled into the archive log data files.
before image
The image of an Oracle row that exists in the rollback segment and shows the values of the row before it was updated.
buffer hit ratio
The ratio of logical to physical disk reads.
cluster
A cluster is a schema object that contains data from one or more tables, all of which have one or more columns in common. Oracle Database stores together all the rows from all the tables that share the same cluster key.
coexistence methods
A technique for ensuring coexistence between two methods for access to the Oracle database.
concurrency management
This is the process of ensuring that all database changes are single-threaded, and that no two tasks can ever update a row at the same moment in time.
Data dictionary
A data dictionary defines the vocabulary of the problem domain. Entries in the data dictionary can and will change over the course of a project, but they should change only when everyone involved in the project agrees to the change. Use a data dictionary to ensureconsistent, high-quality communication.
data dictionary cache
The data dictionary cache is used to hold rows from the internal Oracle metadata tables, including SQL stored in packages.
database buffer cache
An area in memory where Oracle keeps recently used data blocks so that they do not need to be constantly reread from disk.
Deadlock
A situation that occurs when two processes are dependent on each other, resulting in an impass.
default buffer pool
A pool of buffers in the database buffer cache which are managed using a least recently used algorithm. As new data is read in, the oldest data is aged out.
dictionary cache miss
Oracle registers a dictionary cache miss when a package is not in memory when it is requested.
disk sorts
Occurs when sorts exceed sort_area_size and Oracle will sort the result set in the TEMP tablespace.
dbms_lock
An Oracle packages that makes Oracle kernel's lock management services available to user-written applications.
enqueues
An enqueue is a more sophisticated mechanism, which permits several concurrent processes to have varying degrees of sharing of "known" resources. Any object that can be concurrently used may be protected with enqueues.
estat
Oracle's utlestat.sql utility that grabs "end statistics".
estat-bstat utility
Oracle begin statistics (utlbstat.sql) and end statistics (utlestat.sql). These are used to create an elapsed-time snapshot of Oracle activity.
exclusive locks
Exclusive locks are issued for the duration of all SQL UPDATE or DELETE statements to ensure that all changes are single-threaded through the database.
exclusive table locks
The most restrictive of the table locks. Prevents everything except queries against the affected table. Exclusive locks are used when the programmer desired exclusive control over a set of rows until their operation has completed.
Grid
Oracle 10G makes use of the Grid.
immediate mode
An immediate mode request terminates the transaction if it does not occur immediately.
in-memory sorts
Sorts that are performed very quickly in the memory allocated to sort_area_size.
index hint
A directive made in an SQL statement to force the use of an index when servicing a query.
joins
A type of query in which two or more tables are connected, or joined, together.
latches
Low-level mechanisms that are used to ensure single-threaded access to shared data structures in the SGA.
library cache miss ratio
Represents the ratio of the sum of library cache reloads to the sum of pins.
lock contention
A situation where a task is waiting for another task to release a locked resource.
lock hit ratio
Used to identify excessive lock conversion by the DLM.
Locks
Ways of controlling access to specific data by allowing limited access to a table when another user is accessing the table.
locks
Locks are used by the Oracle database to ensure that database rows are not inadvertently written-over by concurrent tasks.
log writer process
This process transfers data from the redo log buffers to the redo log database files.
logical disk read
A logical read is a request from a program for a record.
Low cardinality index
The is an index with less than 100 distinct values. For example a REGION index has only four distinct values (north, south, east, west) and is low-cardinality.
LRU latches
Special latches that manage the memory within the Oracle data buffers.
LRUW List
LRUW is an acronym for Least Recently Used Write.
The LRUW list consists of dirty buffers which need to be written to disk. While searching for a particular data buffer, Oracle does not search through the entire list of buffers, but uses a hashing algorithm to determine the bucket in which the block will reside and then retrieves data from the desired block or blocks. The dirty list is also called the write list or LRUW.
OEM top session monitor
Enables you to view top processes sorted by any number of redo log statistics.
Oracle Parallel Server (OPS)
An Oracle add-on product that allows for multiple Oracle instances to access the same database. This is especially useful for massively parallel servers where hundreds of processors can simultaneously access the same database.
physical disk reads
A request from a database for a record.
Physical Read Factor
The physical read factor is the ratio of the number of estimated reads to the number of reads actually performed by the real buffer cache during the measurement interval.
Physical table design tuning
Physical design tuning refers to tuning the database structures. such as the sizing of the table parameters, the placement of indexes, and the physical configuration of the Oracle instance.
ping
The UNIX utility used to test connectivity to remote hosts.
pinning packages
The process of keeping packages inside the library cache
pre-emptive locks
Exclusive locks issued at the time of retrieval and held until the task has committed or ended.
process memory
The SGA memory allocated on behalf of an Oracle process.
read consistency
The ability of Oracle to read row "before" images from the rollback segments to ensure that a long running return data values as of the time that the query started to execute.
redo latch contention
This occurs when Oracle allocates memory in the log_buffer areas for redo logs or when Oracle copies redo images from the log buffer into the online redo log files.
redo log buffer
An area in the SGA that Oracle uses to hold redo log entries until they can be written to the log files.
rollback segment
A database structure that Oracle uses to keep track of changes made by a transaction. The information is used to undo those changes in the event that the transaction is rolled back. The information is also used to provide other database users with a consistent view of the database. Other users do not see your changes until you have committed them. If someone retrieves a row that you have changed, and you have not
committed that change yet, Oracle will send back the original version of that row using the information in the rollback segment.
row exclusive locks
A type of lock issued automatically against a table when an UPDATE, DELETE, or INSERT statement is issued against the table.
row locks
Locks placed on individual rows within an Oracle table.
row share table locks
A type of lock issued when an SQL transaction has declared its intent to update the table in row share mode.
Server Manager
Windows NT Server administration tool. Allows remote server management.
session memory
The SGA memory allocated on behalf of an individual Oracle session.
SGA
An abbreviation for System Global Area.
shared locks
In shared locking, whenever a unit of data is retrieved from the database, an entry is placed in the database storagepool.
shared row exclusive table locks
Type of lock issued with the
LOCK TABLE Customer
IN SHARE ROW EXCLUSIVE MODE
command.
shared SQL area
The shared SQL area is used to keep and process SQL statements and PL/SQL code.
Shared-nothing parallel server configuration
Within Oracle, several Oracle instances run within RAM memory, and each independent instance shares access to the same Oracle tables.
SMON
The Oracle system monitor process.
sorting
The process of re-sequencing result sets from Oracle queries.
spinning
Spinning means that the process continues to look for the availability of the latch after fixed intervals of time, during which it sleeps.
Symmetric multiprocessor configuration
A configuration where a number of processors share common memory and disk resources.
System Global Area
A memory area that Oracle uses for a multitude of purposes such as the buffering data blocks, buffering redo log records, holding SQL statements, and so forth. It is called shared because multiple Oracle process access it at the same time.
table locks
A single lock that encompasses an entire Oracle table.
table share locks
Type of lock issued when the LOCK TABLE command is issued against the table.
Two-phase commit
Aprocess used by distributed transactions to successfully roll back or commit transactions over multiple computers. This is done in two phases, prepare and commit.
update transaction
A transaction that makes changes to the Oracle database.
willing-to-wait mode
If the process fails to obtain the latch on the first attempt, it will continue trying until it gets the latch.