Mohamed Ziauddin

Query processing and optimization in Oracle Rdb

Abstract.This paper contains an overview of the technology used in the query processing and optimization component of Oracle Rdb, a relational database management system originally developed by Digital Equipment Corporation and now under development by Oracle Corporation. Oracle Rdb is a production system that supports the most demanding database applications, runs on multiple platforms and in a variety of environments.

Optimizer plan change management: improved stability and performance in Oracle 11g

Execution plans for SQL statements have a significant impact on the overall performance of database systems. New optimizer statistics, configuration parameter changes, software upgrades and hardware resource utilization are among a multitude of factors that may cause the query optimizer to generate new plans. While most of these plan changes are beneficial or benign, a few rogue plans can potentially wreak havoc on system performance or availability, affecting critical and time-sensitive business application needs. The normally desirable ability of a query optimizer to adapt to system changes may sometimes cause it to pick a sub-optimal plan compromising the stability of the system. In this paper, we present the new SQL Plan Management feature in Oracle 11g. It provides a comprehensive solution for managing plan changes to provide stable and optimal performance for a set of SQL statements. Two of its most important goals are preventing sub-optimal plans from being executed while allowing new plans to be used if they are verifiably better than previous plans. This feature is tightly integrated with Oracles query optimizer. SQL Plan Management is available to users via both command-line and graphical interfaces. We describe the feature and then, using an industrial-strength application suite, present experimental results that show that SQL Plan Management provides stable and optimal performance for SQL statements with no performance regressions.

Dimensions based data clustering and zone maps

In recent years, the data warehouse industry has witnessed decreased use of indexing but increased use of compression and clustering of data facilitating efficient data access and data pruning in the query processing area. A classic example of data pruning is the partition pruning, which is used when table data is range or list partitioned. But lately, techniques have been developed to prune data at a lower granularity than a table partition or sub-partition. A good example is the use of data pruning structure called zone map. A zone map prunes zones of data from a table on which it is defined. Data pruning via zone map is very effective when the table data is clustered by the filtering columns. The database industry has offered support to cluster data in tables by its local columns, and to define zone maps on clustering columns of such tables. This has helped improve the performance of queries that contain filter predicates on local columns. However, queries in data warehouses are typically based on star/snowflake schema with filter predicates usually on columns of the dimension tables joined to a fact table. Given this, the performance of data warehouse queries can be significantly improved if the fact table data is clustered by columns of dimension tables together with zone maps that maintain min/max value ranges of these clustering columns over zones of fact table data. In recognition of this opportunity of significantly improving the performance of data warehouse queries, Oracle 12c release 1 has introduced the support for dimension based clustering of fact tables together with data pruning of the fact tables via dimension based zone maps.