Exam 11: Database Performance Tuning and Query Optimization

A system will perform best when its hardware and software resources are .

Most current-generation relational DBMSs perform automatic query optimization at the client end.

is a measure of the likelihood that an index will be used in query processing.

The data cache or is a shared, reserved memory area that stores the most recently accessed data blocks in RAM.

How should storage volumes be allocated for indexes, system, and high-usage tables?

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

A cost-based optimizer uses a set of preset rules and points to determine the best approach to execute a query.

To work with data, a DBMS must retrieve the data from and place them in .

In standard SQL, the optimizer hint ALL_ROWS is generally used for mode processes.

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.

The majority of primary memory resources will be allocated to the cache.

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

Fully equivalent means that the optimized query results are always the same as the original query.

is the central activity during the parsing phase in query processing.

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

DBMS performance tuning includes global tasks such as managing the DBMS processes in primary memory and managing the structures in physical storage.

Good database performance is easy to evaluate.

The cache stores the most recently executed SQL statements.

On the client side, the objective is to generate an SQL query that returns a correct answer in the least amount of time, using a minimum amount of resources at the server end. The activities required to achieve this goal are commonly referred to as tuning.

A is good for simple and fast lookup operations based on equality conditions.