Exam 11: Database Performance Tuning and Query Optimization

List and describe some typical DBMS processes.

A conditional expression is normally expressed within the _____ or HAVING clauses of a SQL statement.

The _____ must be set large enough to permit as many data requests to be serviced from cache as possible.

The _____ process analyzes SQL queries and finds the most efficient way to access data.

Good database performance is easy to evaluate.

During the ____ phase, the DBMS retrieves the data and sends the result set back to the client.

The _____ cache is used as a temporary storage area for ORDER BY or GROUP BY operations, as well as for index-creation functions.

The _____ table space is the most frequently accessed table space and should be stored in its own volume.

Why do we need to optimize a DBMS with SQL performance tuning, even though they automatically optimize SQL queries?

Maximizing disk contention is one of the general recommendations for the physical storage of databases.

Automatic query optimization means that the:

DBMS query processing has _____ phases.

A(n) _____ request is a low-level read or write data access operation to or from computer devices.

When setting optimizer hints, _____ instructs the optimizer to minimize the overall execution time, that is, to minimize the time it takes to return the total number of rows in the query result set. This hint is generally used for batch mode processes.

Knowing the sparsity of a column helps you decide whether the use of _____ is appropriate.