Exam 11: Database Performance Tuning and Query Optimization

All transaction management commands are processed during the parsing and execution phases of query processing.

When setting optimizer hints,____ instructs the optimizer to minimize the time it takes to process the first set of rows,that is,to minimize the time it takes to return only the first set of rows in the query result set.This hint is generally used for interactive mode processes.

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

DBMS query processing has ____________________ phases.

List and describe some typical DBMS processes.

The DBMS ____ the data and sends the result set back to the client.

____________________ are ordered sets of values that are crucial in speeding up data access.

All factors must be checked to ensure that each system component operates at its optimum level and has sufficient resources to minimize the occurrence of bottlenecks.

Indexes do not facilitate join operations.

The ____ table space is used to store the data dictionary tables.

Once an SQL statement is transformed,the DBMS creates what is commonly known as a(n)____________________ plan.

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

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

Database ____________________ activities can be divided into those taking place either on the client side or on the server side.

____ refers to the number of different values a column could possibly have.

Working with data in the data cache is many times faster than working with data in the data files.

All data in a database are stored in ____ files.

Most DBMSs operate in one of two optimization modes: cost-based or rule-based.Others automatically determine the ____ based on whether database statistics are available.

Good database performance is easy to evaluate.

Use _________________________ to provide balance between performance and fault tolerance.