Deck 11: Database Performance Tuning and Query Optimization

Question 1

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

Answer 1

True

Question 2

RAID systems use a single disk to create storage volumes.

Answer 2

False

Question 3

The SQL cache stores the end-user written SQL.

Answer 3

False

Question 4

The SQL execution activities are performed by the query optimizer.

Question 5

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

Question 6

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

Question 7

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

Question 8

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 9

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

Question 10

Indexes do not facilitate join operations.

Question 11

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

Question 12

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

Question 13

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

Question 14

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

Question 15

Good database performance starts with good database design.

Question 16

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

Question 17

A data file can only contain rows from one single table.

Question 18

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

Question 19

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

Question 20

Good database performance is easy to evaluate.

Question 21

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

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

Question 22

One measure that determines the need for an index is the ____ of the column you want to index.____ refers to the number of different values a column could possibly have.

A) Database statistics
B) Data sparsity
C) Primary keys
D) Query optimization

Question 23

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.

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

Question 24

All data in a database are stored in ____ files.

A) table
B) data
C) RAM
D) system

Question 25

On the server side,the DBMS 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 that goal are commonly referred to as ____ tuning.

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

Question 26

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

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 29

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

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

Question 30

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

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

Question 31

To work with the data,the DBMS must retrieve the data from the ____ and place it in the ____.

A) data files,procedure cache
B) data files,data cache
C) temporary files,RAM
D) temporary files,procedure cache

Question 32

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) ALL_ROWS
B) FIRST_ROWS
C) INDEX(P_QOH_NDX)
D) OPTIMIZATION_ROWS

Question 33

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 34

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) file group
C) data files
D) extends

Question 35

The DBMS ____ the SQL query using the chosen execution plan.

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

Question 36

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

A) procedure cache
B) file group
C) data files
D) extends

Question 37

To generate database object statistics manually,you could use the following syntax:____.

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

Question 38

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

Question 39

____ is/are the central activity during the parsing phase in query processing.

A) Database statistics
B) Data sparsity
C) SQL query
D) Query optimization

Question 40

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

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

Question 41

The ____ cache stores the most recently executed SQL statements.

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

Question 42

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

Question 43

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

Question 44

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.

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

Question 45

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

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

Question 46

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

Question 47

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

Question 48

The ____ table space is used as a temporary storage area for merge,sort,or set aggregate operations.

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

Question 49

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

Question 50

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

Question 51

DBMS query processing has ____________________ phases.

Question 52

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

Question 53

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 54

The ____ table space is used for transaction-recovery purposes.

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

Question 55

When setting optimizer hints,____ forces the optimizer to use the P_QOH_NDX index to process this query.

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

Question 56

The ____ table space is used to store end-user data.

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

Question 57

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

Question 58

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

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

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

____________________ is another name for table space.

Question 61

Use _____________________________________________ to provide balance between performance and fault tolerance.

Question 62

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

Question 63

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

Question 64

____________________ is a measure of how likely an index will be used in query processing.

Question 65

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

Question 66

List and describe some typical DBMS processes.

Question 67

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

Question 68

____________________ are special instructions for the optimizer that are embedded inside the SQL command text.

Question 69

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

Question 70

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