Deck 11: Database Performance Tuning and Query Optimization

Question 1

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 1

True

Question 2

The SQL execution activities are performed by the query optimizer.

Answer 2

False

Question 3

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

Answer 3

False

Question 4

The SQL cache stores the end-user written SQL.

Question 5

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

Question 6

Good database performance is easy to evaluate.

Question 7

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

Question 8

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

Question 9

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

Question 10

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

Question 11

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

Question 12

RAID systems use a single disk to create storage volumes.

Question 13

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

Question 14

Indexes do not facilitate join operations.

Question 15

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

Question 16

Good database performance starts with good database design.

Question 17

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

Question 18

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

Question 19

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

Question 20

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

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

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 23

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 24

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

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

Question 25

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 26

All data in a database are stored in ____ files.

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

Question 27

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 28

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

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

Question 29

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 30

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

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

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

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

Question 34

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 35

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

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 38

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

Question 40

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

DBMS query processing has ____________________ phases.

Question 42

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 43

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

Question 44

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

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

Question 45

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 46

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 47

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

Question 48

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

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

Question 49

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

Question 50

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 51

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

Question 52

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

Question 53

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 54

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

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

Question 55

____________________ is another name for table space.

Question 56

The ____ cache stores the most recently executed SQL statements.

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

Question 57

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

Question 58

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

Question 59

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

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

Question 60

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 61

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

Question 62

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

Question 63

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

Question 64

List and describe some typical DBMS processes.

Question 65

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

Question 66

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

Question 67

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

Question 68

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

Question 69

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

Question 70

Use _________________________ to provide balance between performance and fault tolerance.