Deck 14: Managing Database and Sql Performance

Question 1

The SQL execution activities are performed by the query optimizer.

Answer 1

False

Question 2

RAID systems use a single disk to create storage volumes.

Answer 2

False

Question 3

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

Answer 3

True

Question 4

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

Question 5

Good database performance is easy to evaluate.

Question 6

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

Question 7

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

Question 8

Indexes do not facilitate join operations.

Question 9

Good database performance starts with good database design.

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

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 12

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

Question 13

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

Question 14

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

Question 15

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

Question 16

The SQL cache stores the end-user written SQL.

Question 17

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

Question 18

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

Question 19

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

Question 20

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

Question 21

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 22

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 23

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 24

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 25

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 26

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 27

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 28

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

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

Question 29

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 30

All data in a database are stored in ____ files.

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

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 DBMS ____ the data and sends the result set back to the client.

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

Question 33

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 34

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

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

Question 35

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 36

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 37

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

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

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

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

Question 40

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 41

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 42

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

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

Question 43

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

Question 44

____________________ is another name for table space.

Question 45

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

Question 46

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 47

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

Question 48

DBMS query processing has ____________________ phases.

Question 49

The ____ cache stores the most recently executed SQL statements.

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

Question 50

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 51

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

Question 52

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 53

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

Question 54

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

Question 55

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

Question 56

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 57

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

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

Question 58

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 59

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

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

Question 60

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

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

Question 61

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

Question 62

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

Question 63

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

Question 64

Use __________________________________________________ to provide balance between performance and fault tolerance.

Question 65

List and describe some typical DBMS processes.

Question 66

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

Question 67

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

Question 68

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

Question 69

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

Question 70

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