Deck 11: Database Performance Tuning and Query Optimization

Question 1

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

Answer 1

False

Question 2

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

Answer 2

True

Question 3

RAID systems use a single disk to create storage volumes.

Answer 3

False

Question 4

A table space has the same meaning as a file extent.

Question 5

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.

Question 6

An index scan is less efficient than a full table scan.

Question 7

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

Question 8

The SQL cache stores the end-user written SQL.

Question 9

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

Question 10

Good database performance is easy to evaluate.

Question 11

One of the main functions of a database system is to provide timely answers to end users.

Question 12

DBMS implementations are typically similar in complexity to two-tier client/server configurations.

Question 13

The B-tree index is used mainly in tables in which column values repeat a relatively small number of times.

Question 14

Indexes do not facilitate join operations.

Question 15

The SQL execution activities are performed by the query optimizer.

Question 16

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

Question 17

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

Question 18

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

Question 19

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

Question 20

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

Question 21

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

A) parse
B) execute
C) fetch
D) process

Question 22

When moving data from permanent storage to RAM, an I/O disk operation retrieves a(n):

A) entire table.
B) entire physical disk block.
C) single row containing the attribute requested.
D) single attribute requested only.

Question 23

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

A) Executing
B) Parsing
C) Fetching
D) Delivering

Question 24

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

A) data files; procedure cache
B) RAM; data cache
C) permanent storage; RAM
D) temporary files; procedure cache

Question 25

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

A) ANALYZE object_name;
B) CREATE object_name;
C) ANALYZE object_name COMPUTE STATISTICS;
D) CREATE object_name COMPUTE STATISTICS;

Question 26

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

A) Database statistics
B) Data sparsity
C) Bitmap index
D) Clustering

Question 27

The data cache is where the data read from the database data files are stored _____ the data have been read or _____ the data are written to the database data files.

A) after; before
B) after; after
C) before; before
D) before; after

Question 28

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

A) parses
B) executes
C) fetches
D) processes

Question 29

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) client SQL
B) database SQL
C) SQL performance
D) DBMS performance

Question 30

Automatic query optimization means that the:

A) optimization takes place at compilation time by the programmer.
B) DBMS finds the most cost-effective access path without user intervention.
C) optimization process is scheduled and selected by the end user or programmer.
D) database access strategy is defined when the program is executed.

Question 31

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

A) optimizer
B) scheduler
C) listener
D) user

Question 32

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 _____.

A) procedure cache
B) buffer cache
C) supplements
D) extents

Question 33

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

A) buffer cache
B) procedure cache
C) SQL cache
D) permanent storage

Question 34

On the server side, the database environment must be properly configured to respond to clients' requests in the fastest way possible, while making optimum use of existing resources. The activities required to achieve this goal are commonly referred to as _____ tuning.

A) client and server
B) database SQL
C) SQL performance
D) DBMS performance

Question 35

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

A) query processing
B) query optimization
C) an index
D) a full table scan

Question 36

If there is no index, the DBMS will perform a _____ scan.

A) loop
B) range
C) row ID table access
D) full table

Question 37

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

A) procedure cache
B) table space
C) data cache
D) listener

Question 38

The _____ is a shared, reserved memory area that stores the most recently executed SQL statements or PL/SQL procedures, including triggers and functions.

A) buffer cache
B) procedure cache
C) data cache
D) permanent storage

Question 39

A system table space, a user data table space, an index table space, and a temporary table space are examples of _____.

A) procedure caches
B) file groups
C) data caches
D) operation modes

Question 40

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

A) hash index
B) sparse index
C) B-tree index
D) reverse index

Question 41

In standard SQL, the optimizer hintFIRST_ROWS is generally used for _____ mode processes.

A) batch
B) interactive
C) transaction
D) real-time

Question 42

In the context of RAID levels, _____ refers to writing the same data blocks to separate drives.

A) striping
B) mirroring
C) partitioning
D) aggregating

Question 43

_____ is another name for table space.

Question 44

End users and the DBMS interact through the use of _____ to generate information.

Question 45

DBMS query processing has _____ phases.

Question 46

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.

A) ALL_ROWS
B) FIRST_ROWS
C) INDEX(P_QOH_NDX)
D) OPTIMIZATION_ROWS

Question 47

In standard SQL, the optimizer hintALL_ROWS is generally used for _____ mode processes.

A) interactive
B) real-time
C) batch
D) transaction

Question 48

From the performance point of view, _____ databases eliminate disk access bottlenecks.

A) RAID
B) distributed
C) index-organized
D) in-memory

Question 49

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

Question 50

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

A) data
B) SQL
C) sort
D) optimizer

Question 51

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

A) Clustering
B) Partitioning
C) Query validation
D) Query optimization

Question 52

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

Question 53

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

Question 54

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

Question 55

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

A) data
B) SQL
C) sort
D) optimizer

Question 56

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

A) data cache
B) SQL cache
C) sort cache
D) optimizer mode

Question 57

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

Question 58

The LIKE conditional operator is used by the _____ OPERAND1.

A) P_PRICE
B) V_STATE
C) P_QOH
D) V_CONTACT

Question 59

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

Question 60

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

Question 61

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

Question 62

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

Question 63

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

Question 64

_____ is evaluated based on client perspective.

Question 65

______ helps provide a balance between performance and fault tolerance.

Question 66

A(n)_____ is an index based on a specific SQL function or expression.

Question 67

In alevel _____ RAID, the data and the parity data is striped across separate drives.

Question 68

List and describe some typical DBMS processes.

Question 69

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

Question 70

The _____ segment table space is used for transaction-recovery purposes.

Question 71

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

Question 72

A(n) _____ tablestores the end-user data and the index data in consecutive locations on permanent storage.

Question 73

Describe query optimization and the modes that an optimizer can operate in.

Question 74

Summarize the steps required to formulate a query.

Question 75

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