04_11 Database Performance Tuning & Query Optimization Flashcards
A set of instructions generated at application compilation time that is created and managed by a DBMS.
access plan
The access plan predetermines how an application’s query will access the database at run time.
A method by which a DBMS finds the most efficient access path for the execution of a query.
automatic query optimization
An index that uses a bit array to represent the existance of a value or condition.
bitmap index
An ordered data structure organized as an upside-down tree.
B-tree index
A shared, reserved memory area that stores the most recently accessed data blocks in RAM.
data cache (buffer cache)
In a DBMS
A type of table storage organization that stores end-user data and index data in consecutive locations in permanent storage.
index organized table (cluster-indexed table)
A query optimization mode that uses an algorithm based on statistics about the object being accessed.
Includes number of rows, indexes available, index sparsity, etc.
cost-based optimizer
A set of activites and procedures designed to reduce the response time of a database system.
Ensures that an end-user query is processed by the DBMS in the minimum amount of time.
database performance tuning
In query optimization
Measurements about database objects.
Number of rows in a table, number of disk blocks used, maximum and average row length, number of columns in each row, and number of distict values in each column.
database statistics
Such statistics provide a snapshot of database characteristics.
A named physical storage space that stores a database’s data.
It can reside in a different directory on a hard disk or on one or more hard disks.
data file
AI data in a database is stored in data files. A typical enterprise database is normally composed of several data files. A data file can contain rows from one or more tables.
A column distribution of values or the number of different values a column can have.
data sparsity
Activities to ensure that clients’ requests are addressed as quickly as possible while making optimum use of existing resources.
DBMS performance tuning
The process of determining the SQL access strategy at run time.
Uses the most up-to-date information about the database.
dynamic query optimization
In a DBMS
The capability to automatically evaluate and update the database access statistics after each data access operation.
dynamic statistical generation mode
In a DBMS environment
Refers to the ability of data files to expand in size automatically using predefined increments.
extents
In a DBMS
A logical storage space used to group related data.
tablespace (file group)
A type of index based on a specific SQL function or expression.
function-based index
An index based on an ordered list of hash values.
hash index
A database optimized to store large portions (if not all) of the database in primary (RAM) storage rather than secondary (disk) storage.
in-memory database
A measure of how likely an index is to be used in query processing.
index selectivity
A low-level data access operation that reads or writes data to and from computer devices.
input/output (I/O) request
A device used to improve throughput for I/O operations.
input/output (I/O) accelerator
An operation mode that requires the end user or programmer to define the access path for the execution query.
manual query optimization
Special instructions for the query optimizer that are embedded inside the SQL command text.
optimizer hints
A shared, reserved memory area that stores the most recently executed SQL statements or PL/SQL procedures.
Includes triggers and functions
SQL cache (procedure cache)
A DBMS process that analyzes SQL queries and finds the most efficient way to access the data.
query optimizer
The query optimizer generates the access or execution plan for the query.
In query optimization
A delay introduced in the processing of an I/O operation that causes the overall system to slow down.
query processing bottleneck
A system that uses multiple disks to create virtual disks (storage volumes) from several individual disks.
Redundant Array of Independent Disks (RAID)
RAID systems provide performance improvement, fault tolerance, and a balance between the two.
A query optimization mode based on the rule-based query optimization algorithm.
rule-based optimizer
A query optimization technique that uses preset rules and points to determine the best approach to executing a query.
rule-based query optimization algorithm
A query optimization mode in which the access path to a database is predetermined at complition time.
static query optimization
A query optimization technique that uses statistical information about a database.
The DBMS then uses these statistics to determine the best access strategy.
statistically based query optimization algorithm
Activities to help generate a SQL query that returns the correct answer in the least amount of time, using the minimum amount of resources at the server end.
SQL performance tuning
