04_11 Database Performance Tuning & Query Optimization

Question 1

A set of instructions generated at application compilation time that is created and managed by a DBMS.

Answer 1

access plan

The access plan predetermines how an application’s query will access the database at run time.

Question 2

A method by which a DBMS finds the most efficient access path for the execution of a query.

Answer 2

automatic query optimization

Question 3

An index that uses a bit array to represent the existance of a value or condition.

Answer 3

bitmap index

Question 4

An ordered data structure organized as an upside-down tree.

Answer 4

B-tree index

Question 5

A shared, reserved memory area that stores the most recently accessed data blocks in RAM.

Answer 5

data cache (buffer cache)

Question 6

In a DBMS

A type of table storage organization that stores end-user data and index data in consecutive locations in permanent storage.

Answer 6

index organized table (cluster-indexed table)

Question 7

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.

Answer 7

cost-based optimizer

Question 8

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.

Answer 8

database performance tuning

Question 9

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.

Answer 9

database statistics

Such statistics provide a snapshot of database characteristics.

Question 10

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.

Answer 10

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.

Question 11

A column distribution of values or the number of different values a column can have.

Answer 11

data sparsity

Question 12

Activities to ensure that clients’ requests are addressed as quickly as possible while making optimum use of existing resources.

Answer 12

DBMS performance tuning

Question 13

The process of determining the SQL access strategy at run time.

Uses the most up-to-date information about the database.

Answer 13

dynamic query optimization

Question 14

In a DBMS

The capability to automatically evaluate and update the database access statistics after each data access operation.

Answer 14

dynamic statistical generation mode

Question 15

In a DBMS environment

Refers to the ability of data files to expand in size automatically using predefined increments.

Answer 15

extents

Question 16

In a DBMS

A logical storage space used to group related data.

Answer 16

tablespace (file group)

Question 17

A type of index based on a specific SQL function or expression.

Answer 17

function-based index

Question 18

An index based on an ordered list of hash values.

Answer 18

hash index

Question 19

A database optimized to store large portions (if not all) of the database in primary (RAM) storage rather than secondary (disk) storage.

Answer 19

in-memory database

Question 20

A measure of how likely an index is to be used in query processing.

Answer 20

index selectivity

Question 21

A low-level data access operation that reads or writes data to and from computer devices.

Answer 21

input/output (I/O) request

Question 22

A device used to improve throughput for I/O operations.

Answer 22

input/output (I/O) accelerator

Question 23

An operation mode that requires the end user or programmer to define the access path for the execution query.

Answer 23

manual query optimization

Question 24

Special instructions for the query optimizer that are embedded inside the SQL command text.

Answer 24

optimizer hints

Question 25

A shared, reserved memory area that stores the most recently executed SQL statements or PL/SQL procedures.

Includes triggers and functions

Answer 25

SQL cache (procedure cache)

Question 26

A DBMS process that analyzes SQL queries and finds the most efficient way to access the data.

Answer 26

query optimizer

The query optimizer generates the access or execution plan for the query.

Question 27

In query optimization

A delay introduced in the processing of an I/O operation that causes the overall system to slow down.

Answer 27

query processing bottleneck

Question 28

A system that uses multiple disks to create virtual disks (storage volumes) from several individual disks.

Answer 28

Redundant Array of Independent Disks (RAID)

RAID systems provide performance improvement, fault tolerance, and a balance between the two.

Question 29

A query optimization mode based on the rule-based query optimization algorithm.

Answer 29

rule-based optimizer

Question 30

A query optimization technique that uses preset rules and points to determine the best approach to executing a query.

Answer 30

rule-based query optimization algorithm

Question 31

A query optimization mode in which the access path to a database is predetermined at complition time.

Answer 31

static query optimization

Question 32

A query optimization technique that uses statistical information about a database.

The DBMS then uses these statistics to determine the best access strategy.

Answer 32

statistically based query optimization algorithm

Question 33

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.

Answer 33

SQL performance tuning