1289-1291

Previous Table of Contents Next

Page 1289

Dynamic Parallel-Aware Query Optimization

Oracle's cost-based optimizer incorporates parallel execution considerations as a fundamental component in arriving at optimal query execution plans. As mentioned earlier, the optimizer dynamically computes intelligent heuristic defaults for parallelism based on the number of processors and the number of disk devices that store a given table. Evaluation of the costs of alternative access paths (table scan versus indexed access, for example) takes into account the degree of parallelism available for the operation, leading to execution plans optimized for parallel execution.

In addition to parallelism considerations, Oracle's cost-based optimizer includes a wealth of superior optimization techniques specifically targeted at common data warehouse operations. Star query optimization is a good example of this. Star queries involving a large "fact" table and multiple small "dimension" tables are intelligently optimized, delivering performance that matches special-purpose products from niche vendors . As a further example, parallel access of multiple underlying tables in a UNION ALL view incorporates several intelligent optimizations to deliver optimal performance.

Intelligent Performance Optimizations

In addition to dynamic parallel execution, Oracle's scalable parallel technology incorporates several efficient performance optimizations that specifically benefit typical data warehouse operations. Most of the parallel operations bypass the buffer cache, performing direct parallel database reads to deliver superior performance. Further, sorts, database loads, summary table creation, and index builds perform asynchronous, parallel direct writes to disk, achieving maximum throughput. These facilities effectively eliminate contention for the buffer cache between concurrent DSS operations and isolate DSS queries from concurrent OLTP activities on the system. Parallel table scans utilize an asynchronous read-ahead mechanism to achieve an overlap between I/O and processing, delivering much higher throughput for queries involving large tables. Logging can be optionally disabled for bulk operations such as data loads, index builds, and summary creation using the CREATE TABLE...AS SELECT operation to achieve substantial improvements in performance.

Oracle Parallel Query on Loosely Coupled Systems

The basic architectural elements of Oracle's parallel technology remain consistent across all hardware systems. There are, however, some key optimizations that utilize Oracle's unique shared access technology to leverage distinct characteristics of loosely coupled systems and deliver superior scalability, resource utilization, and availability benefits on these systems.

Function Shipping

Oracle's parallel execution on loosely coupled systems extensively uses the function-shipping strategy to perform work on remote nodes. Query server processes located on remote nodes are sent efficient messages, often in the form of modified SQL subqueries, to indicate the work that needs to be done.

Page 1290

Loosely Coupled Systems

This may come as a surprise to many purists because function shipping is typically associated with shared-nothing database systems, and on systems where shared-disk access is available, data shipping is the typical approach. Oracle's parallel architecture, with its combination of key elements of both systems, makes intelligent use of function shipping when the data to be accessed is located at the remote node, to deliver efficient parallel execution eliminating unneeded internode data transfer over the interconnect.

Exploitation of Data Locality

Each node on a shared-nothing hardware system has direct hardware connectivity to a subset of disk devices, and it is more efficient to access these local devices from the "owning" node. Oracle's parallel query execution exploits this affinity of devices to nodes and delivers superior performance on these multi-computer systems. As with other elements of Oracle's dynamic parallel architecture, this strategy works transparently , without any need to perform value-based partitioning of data. The system dynamically detects the locality of data and makes intelligent use of it in two ways:

Spawns query server processes on nodes where the data to be processed is located
Assigns local data partitions to each slave, to eliminate or minimize internode data movement

Such dynamic exploitation of data locality maximizes local data access and minimizes sub- optimal internode data transfer over the interconnect, delivering optimal performance on shared-nothing hardware systems.

Internode Parallelism

In principle, local data access is the preferred strategy on shared-nothing hardware systems; however, exclusive use of this approach, as in pure shared-nothing database architectures, leads to inefficient resource utilization. A number of factors can lead to this: First, data distribution across nodes typically get skewed over time, with additions and purges. Second, the data accessed by a number of queries may relate only to a subset of nodes. For example, if sales data is partitioned by month, a drill-down query on a specific calendar quarter involves only 25 percent of the nodes. In such situations, a pure partitioned execution strategy cannot leverage unutilized or underutilized processing power on other nodes, resulting in highly skewed resource utilization and suboptimal performance.

Oracle's intelligent combination of shared-nothing and shared-disk architectures makes efficient use of local access and shared access to avoid this problem. Initial work assignment to query servers is based on locality of data. In skewed cases, however, Oracle makes dynamic use of internode parallelism. Query servers located on remote nodes compensate for busy nodes by utilizing shared access, to deliver improved throughput and transparent utilization of all available processing power.

Page 1291

Superior Fault Tolerance

Oracle's implementation of the shared access scheme enables any node in a loosely coupled system to have direct logical access to all data and delivers unmatched levels of availability. In the worst case, even if only one node is available, all of the data is still accessible. Live instances recover transactions on failed instances, and the recovery is performed automatically without any additional administrative work. It's impossible to achieve such complete and transparent fault tolerance in a shared-nothing architecture. On these systems, there's at best a provision for supporting dual-ported disk systems resulting in a single backup node for each primary "owner" node. If both the primary and backup nodes fail, the subset of data owned by the primary node remains inaccessible.

The Oracle Advantage over Pure
Shared-Nothing Systems

This section contrasts Oracle's scalable "best of both worlds " architecture against pure shared-nothing database systems, illustrating the superior real-world applicability of Oracle's approach.

Static Data Partitioning and Parallelism

Pure shared-nothing database systems base their parallel execution strategy exclusively on static data partitioning, resulting in a scheme that is fundamentally flawed. Let's explain why. Static data partitioning is a valuable data management facility that can improve the manageability and availability of very large objects in VLDB systems. The use of value-based partitioning as a Very Large Database (VLDB) administration facility has been around a long time, much before the advent of parallel processing. Partitioning is also useful for query optimization in some cases, as it eliminates the need to examine unneeded data.

Deriving parallel query execution exclusively from this administrative segregation of data, however, introduces substantial administrative complexities and severely limits the applicability of parallel processing. A rigid partitioning-based execution approach can provide optimal performance only if the query patterns can be predicted ahead of time. In real-world data warehouse environments, this is an unrealistic requirement because what's "interesting" to analyze is neither predictable nor fixed and is often data-dependentthe knowledge worker decides what to look at based on results of the current analysis. Even if a subset of access requirements can be predicted, because partitioning can be done only along one set of attributes, this approach optimizes performance only for a restricted subset of queries, limiting the applicability of parallel processing.