Database Tuning: Principles, Experiments, and Troubleshooting Techniques

Database Tuning: Principles, Experiments, and Troubleshooting Techniques

Volumn , Issue , 2003, Pages 1-415

  Shasha, Dennis ^a   Bonnet, Philippe ^a  

^a NONE

Author keywords

[No Author keywords available]

Indexed keywords

EID: 84902403333     PISSN: None     EISSN: None     Source Type: Book    
DOI: 10.1016/B978-1-55860-753-8.X5000-8     Document Type: Book

References (151)

1. Graefe Goetz Query evaluation techniques for large databases. ACM Computing Surveys June 1993, 25(2).
2. IBM. Administration Guide V7.1 Volume 3: Performance (SC09-2945-00).
3. This survey is a good starting point for the tuner who wants to understand the algorithms involved in the execution of access plans.
4. This manual describes how to use DB2s explainer tools and how to interpret their output.
5. Berenson Hal, Bernstein Philip A., Gray Jim, Melton Jim, O'Neil Elizabeth J., O'Neil Patrick E. A critique of ANSI SQL isolation levels. Proceedings of the 1995 ACM SIGMOD International Conference on Management of Data 1995, 1-10.. ACM Press. M.J. Carey, D.A. Schneider (Eds.).
6. Fekete A. Serialisability and snapshot isolation. Proceedings of the Australian Database Conference 1999, 201-210.
7. Shasha Dennis, Llirbat Francois, Simon Eric, Valduriez Patrick Transaction chopping: Algorithms and performance studies. TODS 1995, 20(3):325-363.
8. Acharya Swarup, Gibbons Phillip B., Poosala Viswanath, Ramaswamy Sridhar Join synopses for approximate query answering. Proceedings ACM SIGMOD International Conference on Management of Data 1999, 275-286. ACM Press, Sebastopol, Calif. A. Delis, C. Faloutsos, S. Ghandeharizadeh (Eds.).
9. Benchmark Results: www.tpc.org/new_result/h-ttperf.idc.
10. Yong Chan Chee, Ioannidis Yannis E. Bitmap index design and evaluation. Proceedings ACM SIGMOD International Conference on Management of Data 1998, 355-366. ACM Press. L.M. Haas, A. Tiwary (Eds.).
11. Chaudhuri Surajit, Das Gautam, Narasayya Vivek R. A robust, optimization-based approach for approximate answering of aggregate queries. Proceedings ACM SIGMOD International Conference on Management of Data 2001, 295-306. ACM Press.
12. Chaudhuri Surajit, Dayal Umeshwar An overview of data warehousing and OLAP technology. SIGMOD Record 1997, 26(l):65-74.
13. Excelon: www.exceloncorp.com/.
14. Guttman Antonin R-trees: A dynamic index structure for spatial searching. SIGMOD'84 Proceedings of Annual Meeting 1984, 47-57. ACM Press, New York. B. Yormark (Ed.).
15. Haas Peter J., Hellerstein Joseph M. Ripple joins for online aggregation. Proceedings ACM SIGMOD International Conference on Management of Data 1999, 287-298. ACM Press. A. Delis, C. Faloutsos, S. Ghandeharizadeh (Eds.).
16. Harinarayan Venky, Rajaraman Anand, Ullman Jeffrey D. Implementing data cubes efficiently. Proceedings of the 1996 ACM SIGMOD International Conference on Management of Data 1996, 205-216. ACM Press. H.V. Jagadish, I. Singh Mumick (Eds.).
17. Hyperion: www.hyperion.com/.
18. Johnson Theodore, Shasha Dennis Some approaches to index design for cube forest. Data Engineering Bulletin 1997, 20(l):27-35.
19. KDB product: www.kx.com.
20. Kimball Ralph The Data Warehouse Toolkit: Practical Techniques for Building Dimensional Data Warehouses 1996, John Wiley & Sons.
21. Kotidis Yannis, Roussopoulos Nick An alternative storage organization for ROLAP aggregate views based on cubetrees. Proceedings ACM SIGMOD International Conference on Management of Data 1998, 249-258. ACM Press. L.M. Haas, A. Tiwary (Eds.).
22. Kotidis Yannis, Roussopoulos Nick Dynamat: A dynamic view management system for data warehouses. Proceedings ACM SIGMOD International Conference on Management of Data 1999, 371-382. ACM Press. A. Delis, C. Faloutsos, S. Ghandeharizadeh (Eds.).
23. Oracle Corp. Performance and scalability in DSS environment with oracle 9i April 2001.
24. RedBrick Software: www.ibm.com/software/data/informix/redbrick/.
25. Samet Hanan The quadtree and related hierarchical data structures. ACM Computing Surveys 1984, 16(2):187-260.
26. Sarawagi Sunita Indexing OLAP data. Data Engineering Bulletin 1997, 20(l):36-43.
27. Smith Stephen J., Berson Alex, Thearling Kurt Building Data Mining Applications for CRM 1999, McGraw-Hill Professional Publishing.
28. Smith Sybase IQ Indexes 1997, Sybase IQ Collection.
29. Teradata software: www.teradata.com/.
30. Thomsen Erik OLAP Solutions: Building Multidimensional Information Systems 1997, John Wiley & Sons.
31. TimesTen: www.timesten.com.
32. Valduriez Patrick Join indices. TODS 1987, 12(2):218-246.
33. FAME (the temporal database system), http://www.fame.com.
34. FinTime (a financial time series benchmark), http://cs.nyu.edu/cs/faculty/shasha/fintime.html.
35. Gray Jim, Helland Pat, O'Neil Patrick E., Shasha Dennis The dangers of replication and a solution. Proceedings of the 1996 ACM SIGMOD International Conference on Management of Data 1996, 173-182. ACM Press. H.V. Jagadish, I.S. Mumick (Eds.).
36. Jensen Christian S., Snodgrass Richard T. Semantics of time-varying information. Information Systems 1996, 21(4):311-352.
37. KDB product. The array database systems KDB and KSQL can be downloaded on a trial basis from www.kx.com.
38. Ripley B.D., Venables W.N. Modern Applied Statistics with S-Plus 1999, Springer-Verlag.
39. Roddick John F. Myra Spiliopoulou. A bibliography of temporal, spatial and spatio-temporal data mining research. SIGKDD Explorations 1999, l(l):34-38.
40. SAS (the statistical package), http://www.sas.com.
41. The TSQL2 Temporal Query Language 1995, Kluwer Academic Publishers. R.T. Snodgrass (Ed.).
42. Snodgrass Richard T. Developing Time-Oriented Database Application in SQL 2000, Morgan Kaufman.
43. Fin Time is a benchmark having typical Wall Street style.
44. This discusses the semantic pitfalls of replication. Work to overcome those pitfalls is continuingy and you can see pointers to that work in the citeseer database (http://www.citesser.nj.nec.com/).
45. http://www.stats.ox.ac.uk/ripley/ has a lot of useful functions.
46. Jensen, Snodgrass, and many others have worked for several years on bitemporal logic. The two are excellent writers, and we recommend their book, their paper, and even the TSQL2 manual.
47. Agrawal Rakesh, Somani Amit, Xu Yirong Storage and querying of e-commerce data. Proceedings of the 27th VLDB Conference 2001.
48. Using Business Statistics: A Guide for Beginners 1995, Crisp Publications. C. Carrigan, T. Dickey (Eds.).
49. Cuban Mark, Spoelstra Jon Marketing Outrageously 2001, Bard Press, San Francisco.
50. Datta A., Dutta K.K., Thomas H., Vandermeer D., Ramamrithan K., Fishman D. A comparative study of alternative middle tier caching solutions to support dynamic web content acceleration. Proceedings of the 27th VLDB Conference 2001.
51. Kleinrock Leonard, Gail Richard Queuing Systems: Problems and Solutions March 1996, John Wiley and Sons, Austin, Texas.
52. Menasce Daniel A., Almeida Virgilio A.F. Scaling for E-business: Technologies, Models, Performance, and Capacity Planning 2000, Prentice Hall, New York.
53. Mui Linda, Killelea Patrick Web Performance Tuning: Speeding Up the Web 1998, O'Reilly Nutshell, Upper Saddle River, N.J.
54. Oracle9i Application Server Database Cache. Available online at technet. oracle.com/products/ias/db_cache/db_cache_twpp df.
55. TimesTen Front-Tier Available online at. http://www.timesten.com.
56. This paper discusses an architectural suggestion that builders of e-commerce systems would do well to study.
57. Dickey and Carrigan outline the basics of marketing statistics.
58. The authors discuss the art of making a staid project look glamorous.
59. This paper shows the limited advantage of using the database cache for generating dynamic Web pages. Indeed the overhead of HTML and XML generation dominates response time. The authors propose to cache fragments of dynamic Web pages.
60. Kleinrock and Gail offer a lucid explanation of queueing theory in practice.
61. This book explains capacity planning methodologies for e-commerce, including some worked examples.
62. Most e-commerce texts are quite vague about performance, but this book gives some practical tips.
63. Burleson Donald K. Oracle High Performance Tuning with STATSPACK 2001, Oracle Press.
64. Elmasri Ramez A., Navathe Shamkant B. Fundamentals of Database Systems 1999, Addison-Wesley.
65. Griffiths Nigel, Chandler James, Marcos Costa de Souza Joao, Muller Gerhard, Gfroerer Diana Database Performance on AIX in DB2 UDB and Oracle Environments 1999, IBM.
66. IBM. Administration Guide V7.1, Volume 3: Performance (SC09-2945-00).
67. Oracle. Oracle 9i, Database Performance Guide and Reference (A87503).
68. Oracle. Oracle Enterprise Manager, Getting Started with the Oracle Diagnostics Pack (A88748-02).
69. Silberschatz Abraham, Korth Henry F., Sudarshan S. Database Systems Concepts 2001, McGraw-Hill Higher Education.
70. Whalen Edward, Garcia Marcilian, Adrien DeLuca Steve, Thompson Dean Microsoft SQL Server 2000 Performance Tuning Technical Reference 2001, Microsoft Press.
71. This is a comprehensive guide to regular performance monitoring and tuning using the STATSPACK tool.
72. Provides a thorough introduction to query processing.
73. This comprehensive performance tuning guide covers DB2's architecture, monitoring tools, and other tuning aspects.
74. This manual covers the use of Oracle's main monitoring tools, including basic usage of the STATSPACK tool.
75. Covers Oracle's more advanced monitoring and performance diagnostics tools.
76. This well-written popular book provides a functional description of query plans, as do many of the better introductory database textbooks.
77. General performance tuning guide where SQL Server tuners will find how to use the product's monitoring tools.
78. Delaney Kalen Inside MS SQL Server 2000 2000, Microsoft Press.
79. Durbin Jason, Ashdown Lance Oracle8i Distributed Database Systems, Release 2 (8.1.6). 1999, Oracle Corporation.
80. IBM DB2 UDB Administration Guide Version 7 2000, IBM Corporation.
81. Loney Kevin, Thierault Marlene Oracle9i DBA Handbook 2001, Oracle Press.
82. Niemiec Richard Oracle8i Performance Tuning 1999, Oracle Press.
83. Soukup Ron, Delaney Kalen Inside MS SQL Server 7.0 1999, Microsoft Press.
84. This book is a revision of the SQL Server 7 edition.
85. This document describes the Oracle heterogeneous services for accessing multiple database systems.
86. This document covers installation and planning issues for IBM DB2. In particular, it covers issues concerning the application interface and access to multiple databases.
87. A section of this book covers the tuning of JDBC applications. These principles apply to call-level interfaces in general.
88. A section of this book covers the details of the SQL Server application interface using ODBC, OLE DB, or ADO.
89. Abiteboul Serge, Hull Richard, Vianu Victor Foundations of Databases 1995, Addison-Wesley.
90. Celko Joe SQL for Smarties: Advanced SQL Programming 2000, Morgan Kaufmann.
91. Galindo-Legaria Ceptsar A., Joshi Milind Orthogonal optimization of subqueries and aggregation. Proceedings of the 2001 ACM SIGMOD International Conference on Management of Data 2001, 571-581. ACM Press.
92. Ganski Richard A., Wong Harry K.T. Optimization of nested SQL queries revisited. Proceedings of the Association for Computing Machinery Special Interest Group on Management of Data 1987 Annual Conference 1987, 23-33. ACM Press. U. Dayal, I.L. Traiger (Eds.).
93. Kim Won On optimizing an SQL-like nested query. TODS 1982, 7(3):443-469.
94. This is the classic book for the theory underlying good database design.
95. This book contains case studies of SQL programming techniques presented as puzzles.
96. This paper describes the techniques implemented in SQL Server 7 for optimizing correlated subqueries and aggregates.
97. This paper describes the infamous count bug (involving the transformation of counting aggregates in the presence of empty sets).
98. Original paper on nested subqueries optimization.
99. Chaudhuri Surajit, Narasayya Vivek R. An efficient cost-driven index selection tool for Microsoft SQL server. VLDB'97, Proceedings of 23rd International Conference on Very Large Data Bases 1997, 146-155. Morgan Kaufmann. M. Jarke, M.J. Carey, K.R. Dittrich, F.H. Lochovsky, P. Loucopoulos, M.A. Jeusfeld (Eds.).
100. Gaede Volker, Günther Oliver Multidimensional access methods. ACM Computing Surveys 1998, 30(2):170-231.
101. Johnson Theodore, Shasha Dennis Utilization of B-trees with inserts, deletes and modifies. Proceedings of the Eighth ACM SIGACT-SIGMOD-SIGART Symposium on Principles of Database Systems 1989, 235-246. ACM Press.
102. Lehman Tobin J., Carey Michael J. A study of index structures for main memory database management systems. VLDB'86 Twelfth International Conference on Very Large Data Bases 1986, 294-303. Morgan Kaufmann.
103. Mohan C. Aries/kvl: A key-value locking method for concurrency control of multiaction transactions operating on B-tree indexes. 16th International Conference on Very Large Data Bases 1990, 392-405. Morgan Kaufmann.
104. Oracle Corp. Performance and scalability in DSS environment with Oracle 9i April 2001.
105. +-trees cache conscious in main memory. Proceedings of the 2000 ACM SIGMOD International Conference on Management of Data 2000, 475-486. ACM. W. Chen, J.F. Naughton, P.A. Bernstein (Eds.).
106. Vossen Gottfried, Weikum Gerhard Transactional Information Systems: Theory, Algorithms, and Practice of Concurrency Control and Recovery 2001, Morgan Kaufmann.
107. This survey reviews work on multidimensional index structures.
108. This paper studies space utilization in B-trees and shows why free-at-empty gives better performance than marge-at-half for growing databases.
109. This paper introduces T-trees.
110. Practical algorithm unifying concurrency control for B-trees with recovery considerations.
111. + trees.
112. An encyclopedic treatment of theory and some practice of all research in transaction processing. From workflow to data structure concurrency control, this book has both breadth and depth.
113. Adams E. Optimizing preventive service of software products. IBM Journal of Research and Development 1984, 28(1).
114. Adams Steve Oracle 8i Internal Services for Waits, Latches, Locks and Memory 1999, O'Reilly.
115. Bernstein Phil, Hadzilacos Vassos, Goodman Nat Concurrency Control and Recovery in Database Systems 1987, Addison-Wesley.
116. Chamberlain Don A Complete Guide to DB2 Universal Database 1998, Morgan Kaufmann.
117. Delaney Kalen Inside MS SQL Server 2000 2000, Microsoft Press.
118. Gray Jim A census of tandem system availability. IEEE Trans. on Reliability 1990, 39(4):409-418.
119. Gray Jim, Graefe Goetz The five-minute rule ten years later, and other computer storage rules of thumb. SIGMOD Record 1997, 26(4):63-68.
120. Gray Jim, Putzolu Gianfranco R. The 5-minute rule for trading memory for disk accesses and the 10-byte rule for trading memory for CPU time. Proceedings of the ACM SIGMOD 1987 Conference 1987, 395-398. ACM Press.
121. Gray Jim, Reuter Andreas Transaction Processing: Concepts and Techniques 1993, Morgan Kaufmann.
122. Gray Jim, Shenoy P. Rules of thumb in data engineering. Technical Report MS-TR-99-100 2001, Microsoft Research.
123. Heiss Hans-Ulrich, Wagner Roger Adaptive load control in transaction processing systems. 17th International Conference on Very Large Data Bases 1991, 47-54. Morgan Kaufmann, Proceedings.
124. Jacobs Ken Concurrency control, transaction isolation and serializability in SQL92 and Oracle 7 July 1995, Oracle white paper.
125. Loney Kevin, Thierault Marlene Oracle9i DBA Handbook 2001, Oracle Press.
126. Niemiec Richard Oracle8i Performance Tuning 1999, Oracle Press.
127. Ozsu Tamer, Valduriez Patrick Principles of Distributed Database Systems 1999, Prentice Hall.
128. Savage Stefan, Wilkes John AFRAID: A frequently redundant array of independant disks. 1996 Usenix Technical Conference 1996.
129. Soukup Ron, Delaney Kalen Inside MS SQL Server 7.0. 1999, Microsoft Press.
130. Toigo John The Holy Grail of Data Storage Management 2000, Prentice Hall.
131. Watts David, McKnight Greg, Mitura Peter, Neophytou Chris, Gulver Murat Tuning Netfinity Servers for Performance 2000, Prentice Hall.
132. Weikum Gerhard, Vossen Gottfried Transactional Information systems: Theory, Algorithms, and Practice of Concurrency Control and Recovery 2001, Morgan Kaufmann.
133. This article analyzes the causes of Heisenbugs.
134. This small book details the internals of Oracles concurrency control mechanism. The author manages a very detailed FAQ about the internals of Oracle for Unix at www.ixora.com.au/q+a.
135. The classic practical theory book. Its outdated but treats the basic concepts very well. It is available online at http://www.research.microsofi.com/pubs/ccontrol.
136. This book describes DB2 functionalities. It is mainly aimed at application developers.
137. This book is a revision of the SQL Server 7 edition.
138. Analysis of failure causes on Tandem Non-Stop SQL.
139. Review of the five-minute rules and other rules of thumb for storage management.
140. This paper introduces the five-minute rule as an economic threshold for moving data from disk into memory.
141. The authors have a lot of experience building concurrency control and recovery systems, so if you are building a transactional system, this books code and its perspectives provide an excellent start.
142. Latest in the series of J. Grays rules of thumb papers.
143. Incremental method for setting the amount of concurrency in a transaction processing system.
144. This paper describes Oracle snapshot isolation.
145. This book contains numerous tuning tips for Oracle as well as queries for monitoring performances.
146. This book treats all aspects of distributed database systems, including query processing. Essential reading if you are building one.
147. Presents an algorithm to ameliorate the performance of parity checking in a disk array.
148. This book contains very complete sections on capacity planning and sizing as well as on the storage subsytem. It also introduces rules of thumb for tuning SQL Server 7.
149. This book reviews current storage management techniques and discusses strategies for administering large storage systems.
150. Operating system tuning for storage management and database performances.
151. An encyclopedic treatment of theory and some practice of all research in transaction processing. From workflow to data structure concurrency control, this book has both breadth and depth.