Oracle Tuning Concepts - Glossary

• RelationalDB
• Oracle SQL Tuning - Glossary
• Performance Tuning -Glossary
• Tuning Oracle Instance -Glossary

Active Server Pages (ASP)

Is a dynamically created web page which uses ActiveX (usually VB script or Javascript). ASP pages are similar to CGI scripts, but allow Visual Basic programmers to work with familiar tools.

alert log

A common repository file for all messages that are produced in the Oracle software

all_rows hint

The default cost-based optimizer method, all_rows maximizes overall execution time.

alter index rebuild command

Command that rebuilds the index tree by reading the existing tree

ANSI standard

The American National Standards Institute.

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 archiving process writes the redo log data files that are filed into the archive log data files.

b-tree index

Oracle's implementation of the standard tree node index structure

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.

bequeath

To create a process on behalf of an incoming request.

BIF

Extensions to standard Oracle SQL.

bitmapped index

An index structure that is ideal for low-cardinality table columns

bounce

Stop and restart the database

bstat

Oracle's utlbstat.sql utility that grabs "begin statistics".

buffer hit ratio

The ratio of logical to physical disk reads

built-in functions

Extensions to standard Oracle SQL.

chained rows

Rows that span more than one data block.

choose optimizer

A default optimizer_mode in the init.ora file. If statistics exist for any table in a query, CHOOSR will invoke the cost-based optimizer.

cluster tables

An Oracle structure whereby subordinate rows share the same data block as the owner.

clustering factor

The relative sequence of the table rows to the index.

coexistence methods

A technique for ensuring coexistence between two methods for access to the Oracle database.

concatenated index

An Oracle index with multiple table columns

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.

connection pooling

A process that allows Oracle to maximize the number of physical network connections to a multi-threaded server by sharing or pooling a dispatcher's set of connections among multiple client processes.

core dump

A core dump is the printing or the copying the contents of random access memory at one moment in time. One can think of it as a full-length "snapshot" of RAM.

cost-based optimizer

The latest SQL optimizer that uses object statistics to make intelligent table access decisions.

CPU bottleneck

A condition characterized by tasks waiting for CPU services.

cron

The cron daemon lets you automate the running of processes and applications and lets you schedule a particular time for a process to run.

crontab

A UNIX utility that allows task scheduling CRONologically. Also called cron.

CTAS

Create Table as Select

data block

The smallest unit of I/O used by a database. Oracle requests data in Oracle data blocks.

data block header

Contains general block information, such as the segment type [data, index or rollback] and the block address.

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 ensure consistent, high-quality communication.

Database Performance Techniques

data dictionary cache

The data dictionary cache is used to hold rows from the internal Oracle metadata tables, including SQL stored in packages.

data striping

Distribute a hot data file across numerous physical disks.

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.

database schema

A database schema is a logical organizational unit within the database. Other logical structures in the schema often include the following: 1) Views, 2) Sequences, 3) Stored procedures, 4)Synonyms, 5) Indexes, 6) Database links

db_block_size

This is a parameter to determine data block size.

DBA_TAB_PARTITIONS

This is a view describing partition level partitioning information (for each table partition). Information captured in this view includes storage parameters and partition statistics determined by ANALYZE.

dbms_support package

An Oracle-supplied package to provide Oracle internal data for technical support

de-normalized database design

A database design that has re-introduced redundancy for improved performance

Deadlock

A situation that occurs when two processes are dependent on each other, resulting in an impasse.

dedicated listener

A listener that creates separate processes for each incoming connection.

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.

deleted leaf nodes

These are "dead" index tree nodes that are left in the index and flagged as deleted.

dictionary cache miss

Oracle registers a dictionary cache miss when a package is not in memory when it is requested.

Discover New Services wizard

A wizard with Oracle that interrogates remote nodes for databases

disk I/O

An read or a write against a disk

disk sorts

Occurs when sorts exceed sort_area_size and Oracle will sort the result set in the TEMP tablespace.

dispatcher process

A component of the multi-threaded server that routes incoming requests

dispatching priority

Order in which CPU cycles within the database server are assigned to the run queue. Those tasks with a high dispatching priority will move to the top of the CPU run queue while use tasks with a lower dispatching priority will wait their turn inaqueue.

distributed lock manager (DLM)

A component of Oracle Parallel Server that manages distributed locks across instances. The DLM is the software component that links the Oracle instances together as if the instances shared a single memory region.

dmbs_lock

An Oracle packages that makes Oracle kernel's lock management services available to user-written applications.

driving table

The table used by the SQL optimizer in the initial step of execution.

DSS (Decision Support System)

These are databases designed for very complex queries that access and process large amounts of data. DSS applications define performance in terms of response time.

dynamic performance views

Mechanisms for viewing the internal structures within the Oracle memory region.

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.

environmental tuning

Manipulating RAM, CPU or the network to improve database performance.

equi-partitioned index

A local index is said to be equi-partitioned because it shares identical logical partitioning attributes with the underlying table.

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.

event

With respect to classes, a notification that is published by a class.

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.

execution plan

The compiled execution path to Oracle data.An execution plan is the combination of steps Oracle uses to execute a DML statement.

EXPLAIN PLAN

An Oracle utility to show the access path.

export/import utility

The export/import utility is generally used to reorganize a large number of tables that have referential integrity constraints

Extent

SQL Server allocation unit. An extent is 8 data pages.

external parallelism

The concept of running multiple tasks at the same time

False pinging

A false ping happens when blocks covered by the same hashed DLM lock are accessed by different instances.

FIFO

Stands for "first in, first out."

first_rows hint

A cost-based SQL optimizer hint that delivers rows as quickly as possible.

freelist

Also known as multiple freelists. A freelist is a list of the data blocks that are available for inserts; the data blocks on the free list have free space greater than PCTFREE, and have been set aside for that segment's extents.

freelist merge

This is the process where Oracle moves blocks from the master freelist to the process freelist, usually in chunks of five blocks.

full-table scan

An execution plan that accesses a table without an index, reading each block of the table.

grep utility

Generalized regular expression parser: A UNIX command that is used to search one or more files for a given character string or pattern and, if desired, replace the character string with another one.

hash clusters

A hash cluster is a construct that works with Oracle clusters and uses the HASHKEYS command to allow fast access to the primary key for the cluster. Oracle relies on a hashing algorithm, which takes a symbolic key and converts it into a ROWID.

hash join

An execution plan that creates a hash table in SGA memory and uses this memory structure to join the tables.

heap

The name for the memory region on the host.

heuristic

A rule or set of rules used to describe a process.

high water mark

The boundary between used and unused space in a segment.

hint

A SQL compiler directive that tells Oracle to change an execution plan.

histogram

A range of values within an index.

host variables

A variable that is used inside PL/SQL.

hot disk

A disk that is having a disproportional amount of I/O compared to the other disks.

Hypertext Markup Language (HTML)

Short for HyperText Markup Language. HTML is an authoring language which uses tags and attributes to create documents on the World Wide Web.

immediate mode

An immediate mode request terminates the transaction if it does not occur immediately.

immediate remedy problems

Serious problems with a database that must be fixed immediately.

in-memory sorts

Sorts that are performed very quickly in the memory allocated to sort_area_size.

index

A data structure used to facilitate fast access to table rows in a specified sequence. Indexes are associated with tables and clusters. They are created, altered, and dropped using SQL statements to speed SQL statement execution. An Oracle index provides a faster access path to table data. A properly constructed index is often the most effective way to reduce disk I/O.

index browning

A condition where Oracle indexes may become out of balance and require additional I/O to traverse the index tree.

index hint

A directive made in an SQL statement to force the use of an index when servicing a query.

index range scan

A scan of a table using an index.

index trees

The physical node structure of a b-tree index

internal parallelism

The idea is running multiple Oracle processes to speed a query

iostat

A utility that provides a time measure of the amount of physical I/O that has been addressed to the disk devices.

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.

latency

The time required to service an I/O.

library cache

A component of the SGA that manages SQL and locks

listener load balancing

A process of ensuring that all incoming connections do not have to wait for a listener connection by distributing connections among Oracle listeners.

listener log file

The file containing listener activity records.

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 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 database design

The logical structure of the tables and the number of SQL join operations that are required to service a request.

logical database design tuning

Eliminating extra SAL join operations in order to increase speed of SQL queries.

logical disk read

A logical read is a request from a program for a record.

logical volume striping

Striping a data file across numerous UNIX logical disk pieces.

Low cardinality index

This an index with fewer than 100 distinct values. For example a REGION index has only four distinct values (north, south, east, west) and is therefore a low cardinality index.

LRU latches

Special latches that manage the memory within the Oracle data buffers.

memory paging

The process of transferring the contents of RAM to disk.

N-way table joins

An n-way join is where the optimizer processes substantial amounts of data from the FACT table, multiple times; early query optimizers often chose to do this. The optimizer would first join the smallest DIMENSION table against the large FACT table. Thereafter, they would join each of the other DIMENSION tables in turn against the intermediate table. These n-way table joins are performed very slowly. The STAR hint was developed to facilitate large n-way table joins in data warehouses.

nested loop

A join method that queries the driving table and then probes the other tables via an index.

Network Services

All Oracle databases in a distributed database system use Oracle's networking software, Oracle Network Services, to facilitate inter-database communication across a network. Just as Network Services connects clients and servers that operate on different computers of a network, it also allows database servers to communicate across networks to support remote and distributed transactions in a distributed database.

nice command

UNIX command that controls CPU dispatching priorities.

non-correlated subquery

A SQL subquery that does not have a WHERE condition that references values in the outer query.

non-immediate remedy problems

Problems with database performance that do not need to be fixed until there is an opportunity to stop and restart the database.

Object ID

A unique identifier for an object.

OEM Event Manager

A Windows interface in Oracle that allows you to create and register event sets, view the status of services being monitored, and get information about events that have occurred.

OEM top session monitor

Enables you to view top processes sorted by any number of redo log statistics.

OLTP (Online Transaction Processing)

These are databases designed for very complex queries that access and process large amounts of data. These applications define performance in terms of throughput.

optimizer

An Oracle tool to determine Oracle SQL execution plans.

optimizer mode

The default optimizer as set in the init.ora file.

Oracle intelligent agents

A component of Oracle Enterprise Manager that runs as a process on each Oracle server to communicate with the event manager

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.

oradebug

The Oracle debugger that is used to enable tracing. The debugger must always be executed from within Oracle'sServer Manager.

outer join

A join whereby rows that do not have a match in both tables are also displayed.

page in

Re-loading swapped-out memory from disk to RAM.

parallel database

An instance within Oracle parallel Server (OPS)

parallel hint

A SQL directive to assign multiple processes to a full-table scan

parallel query

A method for using multiple processes to read a table via a full-table scan.

parallel table create as select

The process of running CREATE TABLE AS SELECT in parallel

partitioned table

An Oracle structure that divides a logical table into physical pieces.

PCTFREE parameter

The Oracle space management table parameter that causes a data block to be un-linked from the freelist.
PCTFREE sets the minimum percentage of a data block to be set aside as free space for possible updates to rows that already exist in that block.

performance metrics

Specific ways to measure various aspects of database performance

performance thresholds

Measures of database performance that are expressed in terms of values in a certain range.

physical database design

The implementation details behind the database structure, including the sizing of the table parameters, the placement of indexes, and the physical configuration of the Oracle instance.

physical disk reads

A request from a database for a record.

Physical table design tuning

This 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/ pinning

The process of keeping packages inside the library cache

PLAN TABLE

An internal table that contains the display of the execution plan.

Pointer

A variable or reference that holds address information and points or refers to a location (usually in another table).

pre-emptive locks

Exclusive locks issued at the time of retrieval and held until the task has committed or ended.

proactive tuning

Tuning a database systematically, according to a plan

process memory

The SGA memory allocated on behalf of an Oracle process.

ps command

A command that lists processes; ps can be used to determine which processes are consuming an inordinate amount of the CPU cycle.

query

(v.) To extract data from a database; (n.) a set of SQL statements for extracting particular data from a database.

RAID0+1

Also called RAID 10, it is a combination of mirroring and striping.

ranking scheme

A method for determining the relative costs among candidate execution plans.

Raw device

A raw file or raw device is a Unix file that can be read by numerous Unix kernels on different processors.

reactive tuning

Tuning in response to immediate database problems

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.

referential integrity

The concept of Oracle constraints to enforce business rules

renice command

UNIX command that controls the dipatching priority of tasks currently executing.

Replicated

When copies of the database are made, and these are synchronized so that changes made to one are reflected in the others. Allows many users to work with their own local copy, but the database is updated as if it were a single, centralized database.

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 haven't 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.

rule-based optimizer

The first Oracle SQL optimizer, it uses general rules to formulate execution plans.

sargeable predicate

An SQL predicate that will influence the SQL access path

seek latency

The time require for read-write head movement.

segment

A unit of logical database storage.

session memory

The SGA memory allocated on behalf of an individual Oracle session.

SGA

An abbreviation for System Global Area.

SGA tuning

Tuning the memory regions of an Oracle database.

shared locks

In shared locking, whenever a unit of data is retrieved from the database, an entry is placed in the database storage pool.

Shared pool

An area of shared memory that contains information related to the parsing and execution of SQL statements and PL/SQL code.

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.

SID

Session ID

SMON

The Oracle system monitor process.

sorting

The process of re-sequencing result sets from Oracle queries.

spawn

The process of creating a new index level.

spinning

Spinning means that the process continues to look for the availability of the latch after fixed intervals of sleep time.

SQL tuning

Improving the access method used by the SQL optimizer to get requested data.

STAR Query

A typical query in the access layer is a join between the fact table and some number of dimension tables and is often referred to as a star query. In a star query each dimension table is joined to the fact table using a primary key to foreign key join.

STAR Transformation

The STAR transformation is a cost-based optimizer operation to execute STAR queries more efficiently. The STAR transformation has certain requirements. Oracle also calls the STAR transformation execution plan the "parallel bitmapped star query transformation."

swap disk

Special disk reserved for memory paging operations.

symmetric multiprocessing configuration

A configuration where the machine (the Computer box) contains multiple processors, configured in a way to share processing work.

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's 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.

tail command

Displays the last part (10 lines by default) of each given file. The tail command reads from standard input if no files are given or when a filename of - is encountered.

third normal form

A set of rules specifying how tables and columns in a relational database relate to one another.

tkprof

An Oracle tool for interrogating trace files

TNS listener

The transparent network substrate listener software task.

trace file

A detailed record of everything that occurs within the database that is generated when an internal error is detected by a process.

Two-phase commit

A process used by distributed transactions to successfully roll back or commit transactions over multiple computers. This is done in two phases, prepare and commit.

UNION operator

A SQL operator that joins the result sets of multiple SELECT statements.

update transaction

A transaction that makes changes to the Oracle database.

utlbstat-utlestat

Elapsed time reports that can be run for any Oracle database.

V$ views

The V$ views are built upon the X$ views. Oracle provides these V$ views to capture information about the overall database status.

VARRAY

Is a repeating group that allows us to dramatically improve the performance of Oracle queries, because subordinate tables are no longer required in order to represent a one-to-many relationship.

virtual memory

Memory that is in excess of the physical RAM memory capacity of the database server.

Virtual Storage Device (VSD)

A raw disk partition that consists of a collection Physical Partitions (PP) on the disk.

vmstat utility

A UNIX utility that will show the total CPU consumption for all of the CPUs on your database server.

X$ views

The X$ structures are the base views against the internal C structures in memory.