Intelligent optimizer statistic generation for relational databases

Abstract

This dissertation proposes a supplementary method for statistical upkeep techniques, in context of relational databases which depend on cost-based query optimizers. We identify that optimum statistical upkeep is composed into a number of underlying challenges. Furthermore, we seek to address these challenges through techniques which are influenced from prior database activity. Particularly, we refer towards past workload patterns to influence our decision making for upcoming choices concerning scheduling of the optimizer statistical process. We also recognize the need to prioritize between varied optimizer statistics given the large number of presented variations and intertwined cost. We use prior optimizer generated plans so as to recognize statistical inconsistencies, and recommend statistical upkeep accordingly. Particularly we use outlier detection techniques to identify expensive query executions, and model query plans as acyclic tree models to isolate plan deficiencies. We present these methods, and more, through our integration of machine learning techniques in a relational database environment. Furthermore, we present our techniques in an empirical study against data which is generated by an industry-wide recognized workload generator. We expose these techniques across a number of data variations and volumes, in our efforts towards a more robust outcome. Our results indicate that integrating machine learning techniques in a DBMS environment is not only sustainable, but also beneficial in aiding the optimizer statistical process. These results are reported for attempts made at deciding when and which statistics to schedule. Furthermore, our results indicate that statistical relevant decisions are improved by using prior workload baselines as a guide. Finally, we conclude that optimizer statistic decision making can be improved through influence of prior DBMS activity, and integration of machine learning techniques.