Oracle includes numerous data structures to improve the speed of Oracle SQL queries. Taking advantage of the low cost of disk storage,
Oracle includes many new indexing algorithms that increase the speed with which Oracle queries are serviced.
Oracle uses indexes to avoid the need for large-table, full-table scans and disk sorts, which are required when the SQL optimizer cannot find an efficient way to service the SQL query.
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.
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.
