Exam 11: Database Performance Tuning and Query Optimization

To work with data, the DBMS must retrieve the data from permanent storage and place it in RAM.

The SQL cache stores the end-user written SQL.

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

The DBMS _____ the SQL query and chooses the most efficient access/execution plan.

A(n) _____ is good for simple and fast lookup operations based on equality conditions.

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

A DBA determines the initial size of the data files that make up the database; however, as required, the data files can automatically expand in predefined increments known as _____.

The LIKE conditional operator is used by the _____ OPERAND1.

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

To generate database object statistics manually, following syntax should be used in Oracle:

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

RAID systems use a single disk to create storage volumes.

The purpose of an I/O operation is to move data to and from different computer components or devices.

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(n) _____ tablestores the end-user data and the index data in consecutive locations on permanent storage.

The primary factor in determining the most efficient access plan is the I/O cost.

The data cache caches system catalog data and the contents of the indexes.

Which of the following is the first step of query processing at the DBMS server end?

Bitmap indexes tend to use less space than a _____ because they use bits instead of bytes to store their data.

A(n) _____ is a logical grouping of several data files that store data with similar characteristics.