Percentages of Rows Read by Queries as an Operational Database Quality Indicator

  • Paweł Lenkiewicz
  • Krzysztof Stencel

Abstract

Trace files generated during operation of a database provide enormous amounts of information. This information varies from one DBMS to another—some databases produce more information than the others. There are many research projects which aim at analysing workloads of databases (not necessarily the trace files). Many of them work online in parallel with the usual business of a DBMS. Such approaches exclude a holistic tackling of trace files. To date, the research on offline methods had only a partial scope. In this paper we show a comprehensive method to analyse trace files off-line. The aim of this analysis is to indicate tables and queries which are not handled well in current design of the database and the application. Next, we show case-studies performed on two dissimilar database applications which show the potential of the described method.

Preview

Unable to display preview. Download preview PDF.

Unable to display preview. Download preview PDF.

References

  1. 1.
    Sattler, K.U., Geist, I., Schallehn, E.: Quiet: Continuous query-driven index tuning. In: VLDB, pp. 1129–1132 (2003)Google Scholar
  2. 2.
    Schnaitter, K., Abiteboul, S., Milo, T., Polyzotis, N.: Colt: continuous on-line tuning. In: Chaudhuri, S., Hristidis, V., Polyzotis, N. (eds.) SIGMOD Conference, pp. 793–795. ACM, New York (2006)Google Scholar
  3. 3.
    Bruno, N., Chaudhuri, S.: An online approach to physical design tuning. In: [6], pp. 826–835Google Scholar
  4. 4.
    Chaudhuri, S., Narasayya, V.R.: Autoadmin ’what-if’ index analysis utility. In: Haas, L.M., Tiwary, A. (eds.) SIGMOD Conference, pp. 367–378. ACM Press, New York (1998)Google Scholar
  5. 5.
    Agrawal, S., Chaudhuri, S., Narasayya, V.R.: Automated selection of materialized views and indexes in sql databases. In: Abbadi, A.E., Brodie, M.L., Chakravarthy, S., Dayal, U., Kamel, N., Schlageter, G., Whang, K.Y. (eds.) VLDB, pp. 496–505. Morgan Kaufmann, San Francisco (2000)Google Scholar
  6. 6.
    Proceedings of the 23rd International Conference on Data Engineering, ICDE 2007, The Marmara Hotel, Istanbul, Turkey, April 15-20, 2007. IEEE (2007)Google Scholar
  7. 7.
    Bruno, N., Chaudhuri, S.: Physical design refinement: The “Merge-reduce” approach. In: Ioannidis, Y., Scholl, M.H., Schmidt, J.W., Matthes, F., Hatzopoulos, M., Böhm, K., Kemper, A., Grust, T., Böhm, C. (eds.) EDBT 2006. LNCS, vol. 3896, pp. 386–404. Springer, Heidelberg (2006)CrossRefGoogle Scholar
  8. 8.
    Zilio, D.C., Zuzarte, C., Lightstone, S., Ma, W., Lohman, G.M., Cochrane, R., Pirahesh, H., Colby, L.S., Gryz, J., Alton, E., Liang, D., Valentin, G.: Recommending materialized views and indexes with ibm db2 design advisor. In: ICAC, pp. 180–188. IEEE Computer Society, Los Alamitos (2004)Google Scholar
  9. 9.
    Dageville, B., Das, D., Dias, K., Yagoub, K., Zaït, M., Ziauddin, M.: Automatic sql tuning in oracle 10g. In: [14], pp. 1098–1109 (2004)Google Scholar
  10. 10.
    Agrawal, S., Chaudhuri, S., Kollár, L., Marathe, A.P., Narasayya, V.R., Syamala, M.: Database tuning advisor for microsoft sql server 2005. In: [14], pp. 1110–1121 (2004)Google Scholar
  11. 11.
    Valentin, G., Zuliani, M., Zilio, D.C., Lohman, G.M., Skelley, A.: Db2 advisor: An optimizer smart enough to recommend its own indexes. In: ICDE, pp. 101–110 (2000)Google Scholar
  12. 12.
    Shasha, D., Bonnet, P.: Database tuning: principles, experiments, and troubleshooting techniques. Morgan Kaufmann Publishers Inc, San Francisco (2003)Google Scholar
  13. 13.
    Lightstone, S.S., Teorey, T.J., Nadeau, T.: Physical Database Design: the database professional’s guide to exploiting indexes, views, storage, and more. The Morgan Kaufmann Series in Data Management Systems. Morgan Kaufmann Publishers Inc, San Francisco (2007)Google Scholar
  14. 14.
    Nascimento, M.A., Özsu, M.T., Kossmann, D., Miller, R.J., Blakeley, J.A., Schiefer, K.B. (eds.) (e)Proceedings of the Thirtieth International Conference on Very Large Data Bases, Toronto, Canada, August 31 - September 3 2004. Morgan Kaufmann, San Francisco (2004)Google Scholar

Copyright information

© Springer-Verlag Berlin Heidelberg 2010

Authors and Affiliations

  • Paweł Lenkiewicz
    • 1
  • Krzysztof Stencel
    • 1
    • 2
  1. 1.Polish-Japanese Institute of Information TechnologyWarsawPoland
  2. 2.Institute of InformaticsWarsaw UniversityPoland

Personalised recommendations