Miguel Lucero
Quiz by , created more than 1 year ago

CIS 3365 Quiz on CHAPTER 11: DATABASE PERFORMANCE TUNING AND QUERY OPTIMIZATION, created by Miguel Lucero on 06/04/2017.

1462
3
0
Miguel Lucero
Created by Miguel Lucero over 7 years ago
Close

CHAPTER 11: DATABASE PERFORMANCE TUNING AND QUERY OPTIMIZATION

CHAPTER 11: DATABASE PERFORMANCE TUNING AND QUERY OPTIMIZATION

Question 1 of 60

1

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

Select one of the following:

  • True
  • False

Explanation

Question 2 of 60

1

2. Good database performance is easy to evaluate

Select one of the following:

  • True
  • False

Explanation

Question 3 of 60

1

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.

Select one of the following:

  • True
  • False

Explanation

Question 4 of 60

1

4. Good database performance starts with good database design.

Select one of the following:

  • True
  • False

Explanation

Question 5 of 60

1

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

Select one of the following:

  • True
  • False

Explanation

Question 6 of 60

1

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

Select one of the following:

  • True
  • False

Explanation

Question 7 of 60

1

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

Select one of the following:

  • True
  • False

Explanation

Question 8 of 60

1

8. The SQL cache stores the end-user written SQL.

Select one of the following:

  • True
  • False

Explanation

Question 9 of 60

1

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

Select one of the following:

  • True
  • False

Explanation

Question 10 of 60

1

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

Select one of the following:

  • True
  • False

Explanation

Question 11 of 60

1

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

Select one of the following:

  • True
  • False

Explanation

Question 12 of 60

1

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

Select one of the following:

  • True
  • False

Explanation

Question 13 of 60

1

13. The SQL execution activities are performed by the query optimizer.

Select one of the following:

  • True
  • False

Explanation

Question 14 of 60

1

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

Select one of the following:

  • True
  • False

Explanation

Question 15 of 60

1

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

Select one of the following:

  • True
  • False

Explanation

Question 16 of 60

1

16. Indexes do not facilitate join operations.

Select one of the following:

  • True
  • False

Explanation

Question 17 of 60

1

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

Select one of the following:

  • True
  • False

Explanation

Question 18 of 60

1

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

Select one of the following:

  • True
  • False

Explanation

Question 19 of 60

1

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

Select one of the following:

  • True
  • False

Explanation

Question 20 of 60

1

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

Select one of the following:

  • True
  • False

Explanation

Question 21 of 60

1

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

Select one of the following:

  • True
  • False

Explanation

Question 22 of 60

1

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

Select one of the following:

  • True
  • False

Explanation

Question 23 of 60

1

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

Select one of the following:

  • True
  • False

Explanation

Question 24 of 60

1

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

Select one of the following:

  • True
  • False

Explanation

Question 25 of 60

1

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

Select one of the following:

  • True
  • False

Explanation

Question 26 of 60

1

26. RAID systems use a single disk to create storage volumes.

Select one of the following:

  • True
  • False

Explanation

Question 27 of 60

1

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

Select one of the following:

  • a. client SQL

  • b. database SQL

  • c. SQL performance

  • d. DBMS performance

Explanation

Question 28 of 60

1

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

Select one of the following:

  • a. client and server

  • b. database SQL

  • c. SQL performance

  • d. DBMS performance

Explanation

Question 29 of 60

1

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

Select one of the following:

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

Explanation

Question 30 of 60

1

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

Select one of the following:

  • a. procedure cache

  • b. buffer cache

  • c. supplements

  • d. extends

Explanation

Question 31 of 60

1

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

Select one of the following:

  • a. procedure cache

  • b. table space

  • c. data cache

  • d. listener

Explanation

Question 32 of 60

1

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

Select one of the following:

  • a. procedure caches

  • b. file groups

  • c. data caches

  • d. operation modes

Explanation

Question 33 of 60

1

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

Select one of the following:

  • a. after; before

  • b. after; after

  • c. before; before

  • d. before; after

Explanation

Question 34 of 60

1

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

Select one of the following:

  • a. data files; procedure cache

  • b. RAM; data cache

  • c. permanent storage; RAM

  • d. temporary files; procedure cache

Explanation

Question 35 of 60

1

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

Select one of the following:

  • a. buffer cache

  • b. procedure cache

  • c. SQL cache

  • d. permanent storage

Explanation

Question 36 of 60

1

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

Select one of the following:

  • a. buffer cache

  • b. procedure cache

  • c. data cache

  • d. permanent storage

Explanation

Question 37 of 60

1

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

Select one of the following:

  • a. optimizer

  • b. scheduler

  • c. listener

  • d. user

Explanation

Question 38 of 60

1

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

Select one of the following:

  • a. ANALYZE <TABLE/INDEX> object_name;

  • b. CREATE <TABLE/INDEX> object_name;

  • c. ANALYZE <TABLE/INDEX> object_name COMPUTE STATISTICS;

  • d. CREATE <TABLE/INDEX> object_name COMPUTE STATISTICS;

Explanation

Question 39 of 60

1

39. Automatic query optimization means that the:

Select one of the following:

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

Explanation

Question 40 of 60

1

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

Select one of the following:

  • a. parses

  • b. executes

  • c. fetches

  • d. processes

Explanation

Question 41 of 60

1

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

Select one of the following:

  • a. Executing

  • b. Parsing

  • c. Fetching

  • d. Delivering

Explanation

Question 42 of 60

1

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

Select one of the following:

  • a. parses

  • b. executes

  • c. fetches

  • d. processes

Explanation

Question 43 of 60

1

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

Select one of the following:

  • a. loop

  • b. range

  • c. row ID table access

  • d. full table

Explanation

Question 44 of 60

1

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

Select one of the following:

  • a. Database statistics

  • b. Data sparsity

  • c. A bitmap index

  • d. Clustering

Explanation

Question 45 of 60

1

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

Select one of the following:

  • a. hash index

  • b. sparse index

  • c. B-tree index

  • d. reverse index

Explanation

Question 46 of 60

1

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

Select one of the following:

  • a. query processing

  • b. query optimization

  • c. an index

  • d. a full table scan

Explanation

Question 47 of 60

1

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

Select one of the following:

  • a. Clustering

  • b. Partitioning

  • c. Query validation

  • d. Query optimization

Explanation

Question 48 of 60

1

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

Select one of the following:

  • a. ALL_ROWS

  • b. FIRST_ROWS

  • c. INDEX(P_QOH_NDX)

  • d. OPTIMIZATION_ROWS

Explanation

Question 49 of 60

1

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

Select one of the following:

  • a. batch

  • b. interactive

  • c. transaction

  • d. real-time

Explanation

Question 50 of 60

1

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

Select one of the following:

  • a. interactive

  • b. real-time

  • c. batch

  • d. transaction

Explanation

Question 51 of 60

1

51. The LIKE conditional operator is used by the OPERAND1.

Select one of the following:

  • a. P_PRICE

  • b. V_STATE

  • c. P_QOH

  • d. V_CONTACT

Explanation

Question 52 of 60

1

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

Select one of the following:

  • a. data cache

  • b. SQL cache

  • c. sort cache

  • d. optimizer mode

Explanation

Question 53 of 60

1

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

Select one of the following:

  • a. data

  • b. SQL

  • c. sort

  • d. optimizer

Explanation

Question 54 of 60

1

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

Select one of the following:

  • a. data

  • b. SQL

  • c. sort

  • d. optimizer

Explanation

Question 55 of 60

1

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

Select one of the following:

  • a. RAID

  • b. distributed

  • c. index-organized

  • d. in-memory

Explanation

Question 56 of 60

1

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

Select one of the following:

  • a. system

  • b. user data

  • c. temporary

  • d. rollback segment

Explanation

Question 57 of 60

1

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

Select one of the following:

  • a. striping

  • b. mirroring

  • c. partitioning

  • d. aggregating

Explanation

Question 58 of 60

1

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

Select one of the following:

  • a. system

  • b. user data

  • c. temporary

  • d. rollback segment

Explanation

Question 59 of 60

1

59. In the context of RAID levels, striped arrays provide:

Select one of the following:

  • a. increased read performance and fault tolerance.

  • b. increased fault tolerance but decreased performance.

  • c. increased read performance but no fault tolerance

  • d. neither fault tolerance nor good performance.

Explanation

Question 60 of 60

1

60. In RAID level 5,:

Select one of the following:

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

Explanation

Previous
Next