数据库英文版第六版课后答案(45).docx

C H A P T C H A P T E R 24 Advanced Application Development Exercises Find out all performance information your favorite database system pro- vides. Look for at least the following: what queries are currently executing or executed recently, what resources each of them consumed (CPU and I/O ), what fraction of page requests resulted in buffer misses (for each query, if available), and what locks have a high degree of contention. You may also be able to get information about CPU and I/O utilization from the operating system. Answer: ? Postgresql: The E XP L AI N command lets us see what query plan the system creates for any query. The numbers that are currently quoted by E XP L AI N are: Estimated start-up cost Estimated total cost Estimated number of rows output by this plan node Estimated average width of rows ? SQL: There is a Microsoft tool called SQL Pro f iler . The data is logged, and then the performance can be monitored. The following perfor- mance counters can be logged. Memory Physical Disk Process Processor SQLServer:Access Methods SQLServer:Buffer Manager SQLServer:Cache Manager 191 192 Chapter 24 Advanced Application Development SQLServer:Databases SQLServer:General Statistics SQLServer:Latches SQLServer:Locks SQLServer:Memory Manager SQLServer:SQL Statistics SQLServer:SQL Settable a. What are the three broad levels at which a database system can be tuned to improve performance? b. Give two examples of how tuning can be done for each of the levels. Answer: We refer to performance tuning of a database system as the modi?- cation of some system components in order to improve transaction response times, or overall transaction throughput. Database systems can be tuned at various levels to enhance performance. viz. Schema and transaction design Buffer manager and transaction manager Access and storage structures Hardware - disks, CPU, busses etc. We describe some examples for perfor