CHAPTER 11: DATABASE PERFORMANCE TUNING AND QUERY OPTIMIZATION

1

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

1

2. Good database performance is easy to evaluate

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.

1

4. Good database performance starts with good database design.

1

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

1

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

1

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

1

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

1

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

1

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

1

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

1

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

1

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

1

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

1

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

1

16. Indexes do not facilitate join operations.

1

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

1

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

1

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

1

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

1

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

1

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

1

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

1

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

1

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

1

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

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.

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.

1

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

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 .

1

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

1

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

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.

1

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

1

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

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.

1

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

1

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

1

39. Automatic query optimization means that the:

1

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

1

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

1

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

1

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

1

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

1

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

1

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

1

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

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.

1

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

1

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

1

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

1

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

1

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

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.

1

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

1

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

1

CIS 3365 Quiz sobre CHAPTER 11: DATABASE PERFORMANCE TUNING AND QUERY OPTIMIZATION, criado por Miguel Lucero em 06-04-2017.

CHAPTER 11: DATABASE PERFORMANCE TUNING AND QUERY OPTIMIZATION

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

2. Good database performance is easy to evaluate

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.

4. Good database performance starts with good database design.

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

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

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

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

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

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

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

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

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

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

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

16. Indexes do not facilitate join operations.

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

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

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

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