Allison W. Lee

Closing the query processing loop in Oracle 11g

The role of a query optimizer in a database system is to find the best execution plan for a given SQL statement based on statistics about the objects related to the tables referenced in the statement. These statistics include the tables themselves, their indexes, and other derived objects. Statistics include the number of rows, space utilization on disk, distribution of column values, etc. Optimization also relies on system statistics, such as the I/O bandwidth of the storage sub-system. All of this information is fed into a cost model. The cost model is used to compute the cost whenever the query optimizer needs to make a decision for an access path, join method, join order, or query transformation. The optimizer picks the alternative that yields the lowest cost. The quality of the final execution plan depends primarily on the quality of the information fed into the cost model as well as the cost model itself. In this paper, we discuss two of the problems that affect the quality of execution plans generated by the query optimizer: the cardinality of intermediate results and host variable values. We will give details of the solutions we introduced in Oracle 11g. Our approach establishes a bridge from the SQL execution engine to the SQL compiler. The bridge brings valuable information to help the query optimizer assess the impact of its decisions and make better decisions for future executions of the SQL statement. We illustrate the merits of our solutions based on experiments using the Oracle E-Business Suite workload.

Snowtrail: Testing with Production Queries on a Cloud Database

Database as a service provided on cloud computing platforms has been rapidly gaining popularity in recent years. The Snowflake Elastic Data Warehouse (henceforth referred to as Snowflake) is a cloud database service provided by Snowflake Computing. The cloud native capabilities of new database services such as Snowflake bring exciting new opportunities for database testing. First, Snowflake maintains extensive knowledge of historical customer queries, including both the query text and corresponding system configurations. Second, Snowflake is multi-tenant, which provides easy access to metadata and data that can be used to rerun customer queries from a privileged role. Furthermore, the elastic nature of Snowflakes data warehouse service allows testing with these queries using a separate set of resources without impacting the customers production workload. This paper presents Snowtrail, an infrastructure developed within Snowflake for testing using customer production queries with result obfuscation. Running tests with production queries provides us with direct insight into the impact of improvements and new features on customer workloads. It enables testing on queries of more shapes and complexity than can be manually constructed by developers. Snowtrail is also used to help ensure the stability of the online upgrade process of the system.