Query optimization must evolve with new workloads. As analytic and data warehouse workloads become more ubiquitous, optimization techniques that reduce the amount of data processed during query execution, enable shared computation and avoid expensive data access and joins must be rigorously explored. In this paper, we present aggregate-decomposition techniques as enhancements to an existing query transformation that performs grouping before joins. Consequently, the transformation generates more query rewrite candidates and can also be applied to a larger set of queries. Further, we introduce two new query transformations, i) subsumption of views and subqueries that explores opportunities for sharing computation and ii) union-all duplicator transformation for queries with disjunctive join predicates that removes the need for multiple data access and joins. These techniques are applicable to commonly noticed query patterns in customer workloads and provide significant performance benefit as indicated in our performance study. They have been implemented in Oracle RDBMS.

Despite best coding practices, software bugs are inevitable in a large codebase. In traditional databases, when errors occur during query processing, they disrupt user workflow until workarounds are found and applied. Manual identification of workarounds often relies on a trial-and-error method. The process is not only time-consuming but also requires domain expertise that users are often lacking. In this paper, we propose a framework to automatically mitigate errors that occur during query compilation (including optimization and code generation) without any user intervention. An error is intercepted by the database internally, a workaround is identified for it, and the query is recompiled using the workaround. The entire process remains transparent to the user with the query being executed seamlessly. The proposed technique handles SQL errors during query compilation and provides three types of mitigation strategies - i) quickly failover to one of the readily-available historical plans for the statement ii) apply targeted error-correcting directives (hints) identified from the optimizer context at the time of the error iii) modify the global configuration of the optimizer using hints. This feature has been implemented and will be released in an upcoming version of Oracle Autonomous Database.

Observability is a critical requirement of increasingly complex and cloud-first data management systems. In most commercial databases, this relies on telemetry like logs, traces, and metrics, which helps to identify, mitigate, and resolve issues expeditiously. SQL monitoring tools, for example, can show how a query is performing. One area that has received comparatively less attention is the observability of the query optimizer whose inner workings are often shrouded in mystery. Optimizer traces can illuminate the plan selection process for a query, but they are comprehensible only to human experts and are not easily machine-parsable to remediate sub-optimal plans. Hints are directives that guide the optimizer toward specific directions. While hints can be used manually, they are often used by automatic SQL plan management tools that can quickly identify and resolve regressions by selecting alternate plans. It is important to know when input hints are inapplicable so that the tools can try other strategies. For example, a manual hint may have syntax errors, or an index in an automatic hint may have been accidentally dropped. In this paper, we describe the design and implementation of Oracle's hint observability framework which provides a comprehensive usage report of all hints, manual or otherwise, used to compile a query. The report, which is available directly in the execution plan in a human-understandable and machine-readable format, can be used to automate any necessary corrective actions. This feature is available in Oracle Autonomous Database 19c.

Modern commercial database systems are increasingly evolving into a hybrid distributed system model where a primary database host system enlists the services of a loosely coupled secondary system that acts as an accelerator. Often the secondary system is a distributed system that can perform specific tasks massively parallelized with results fed back to the host database. Similar models can also be seen in architectures that separate compute from storage. As the scale of the system grows, failures of nodes become common, and the architectural goal is to recover the system with minimal disruption to the workload as seen by the user. This paper introduces a new framework that allows a host database to efficiently manage the availability of a massive secondary distributed system and describes a mechanism to achieve query fault tolerance at the primary database by transparently re-executing query (sub)plans on the secondary distributed system. The focus is on improving two important aspects of disruption ? downtime and transparency to the user. The proposed mechanisms achieve quick recovery, reduced duration of downtime and isolation of errors during query execution, thus improving execution transparency for the users.