Deck 11: Database Performance Tuning and Query Optimization

Question 1

Using index characteristics,a database designer can determine the best type of index to use.

Answer 1

True

Question 2

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

Answer 2

True

Question 3

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.

Answer 3

True

Question 4

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

Question 5

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

Question 6

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

Question 7

Indexes do not facilitate join operations.

Question 8

A data file can contain rows from a single table alone.

Question 9

The SQL execution activities are performed by the query optimizer.

Question 10

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

Question 11

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

Question 12

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

Question 13

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

Question 14

Good database performance is easy to evaluate.

Question 15

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

Question 16

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

Question 17

Good database performance starts with good database design.

Question 18

The SQL cache stores the end-user written SQL.

Question 19

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

Question 20

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

Question 21

Character field comparisons are faster than numeric,date,and NULL comparisons.

Question 22

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

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

Question 23

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

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

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 26

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 27

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 28

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 29

Indexes are very useful in small tables or tables with low sparsity.

Question 30

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

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

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

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 33

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

Question 34

RAID systems use a single disk to create storage volumes.

Question 35

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 36

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 37

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)extends

Question 38

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 39

In-memory database systems are optimized to store small portions of the database in disk storage alone.

Question 40

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 41

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 42

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

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

Question 43

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

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

Question 44

The table space is used for transaction-recovery purposes.

A)system
B)user data
C)temporary
D)rollback segment

Question 45

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

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

Question 46

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

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

Question 47

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

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

Question 48

In RAID level 5,:

A)the data and the parity data are striped across separate drives.
B)the data blocks are spread over separate drives and are duplicated.
C)the array requires a minimum of two drives and is known as a striped array.
D)the array requires a minimum of five drives and is known as duplexing.

Question 49

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

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

Question 50

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

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

Question 51

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 52

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 53

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 54

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 55

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

A)system
B)user data
C)temporary
D)rollback segment

Question 56

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

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

Question 57

The LIKE conditional operator is used by the OPERAND1.

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

Question 58

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 59

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 60

In the context of RAID levels,striped arrays provide:
A)increased read performance and fault tolerance.

A)increased read performance but no fault tolerance.
B)increased fault tolerance but decreased performance.
D)neither fault tolerance nor good performance.

Question 61

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

Question 62

DBMS query processing has phases.

Question 63

_________is evaluated based on client perspective.

Question 64

List and describe some typical DBMS processes.

Question 65

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

Question 66

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

Question 67

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

Question 68

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

Question 69

How can queries be written to perform the fastest when equality and inequality comparisons are needed?

Question 70

helps provide a balance between performance and fault tolerance.

Question 71

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

Question 72

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

Question 73

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

Question 74

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

Question 75

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

Question 76

is another name for table space.

Question 77

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

Question 78

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

Question 79

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

Question 80

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