Oracle International Corporation

Yali Zhu

Mohamed Ziauddin

Mohamed Zait

Dinesh Das

Hong Su

Benoit Dageville

Thierry Cruanes

Allison Waingold Lee

Andrew Witkowski

Cetin Ozbutun

Alan Paul Wood

Khaled Yagoub

Peter Clayton Belknap

You Jung Kim

Mengmeng Chen

Techniques are described herein for executing queries with a recursive clause using a plurality of processes that execute database operations in parallel. Each process of the plurality of processes either generate or are assigned a segment that is part of a temporary table. For each iteration of the recursive query, work granules are divided up and assigned to each of the plurality of processes. As each respective process produces a portion of a result set for a given iteration, that process appends said portion of the result set to the respective segment that the respective process manages. Each slave process then publishes, to one or more sources, a reference to the newly generated results. During the next iteration, any slave process may access any of the data from the previous iteration.

Parallel execution of queries with a recursive clause

Techniques are described herein to generate and to execute a query execution plan using static data buffering. After receiving a query with a clause that requires multiple iterations to execute, a database management system (DBMS) generates a plurality of plans that vary the order in which the database operations are executed. Within each plan, the DBMS identifies sets of rows within that plan that contain static data during execution of the query. Then, an additional step is added to each plan that includes loading the static set of rows in a database buffer cache. One or more database operations, from an iteration other than the first iteration, may be performed against the cached static set of rows. For each plan generated in this manner, a cost analysis model is applied, and the plan with the lowest estimated computational cost is selected for use as the query execution plan.

Static data caching for queries with a clause that requires multiple iterations to execute

Approaches, techniques, and mechanisms are disclosed for maintaining a set of baseline query plans for a database command. Except in rare circumstances, a database server may only execute a command according to a baseline plan, even if the database server predicts that a different plan has a lower cost. The set of baseline plans are plans that, for one reason or another, have been determined to provide acceptable actual performance in at least one execution context. When the database server receives a request to execute a particular command, the database server, if possible, always executes the command according to the lowest predicted cost baseline plan. The database server may evolve the plan baseline to include additional plans by generating and testing new plans in response to new requests to execute the database command, or as part of a query optimization or tuning process.

SQL execution plan baselines

An improved method, system, and computer program product is disclosed that provides a hybrid approach to optimization which allows different subsets of data accessed by a query to be optimized with different access paths and execution approaches. Transformations may be performed to re-write the query, which restructures the query in a way that facilitates the hybrid optimization process. Multiple transformations may be interleaved to produce an efficient re-written query.

Method and system for performing query optimization using a hybrid execution plan

Parallel execution of SQL statements is made fault-tolerant. Specifically, during parallel query processing, a new row source is created that allows checkpoints to be stored at certain points in the query processing. If a failure occurs, then the query can be restarted from the most recent checkpoint (rather than from the very beginning of the query). The checkpoints are implemented at operation boundaries where rows need to be redistributed among processes. These are natural checkpoint locations because (1) each subsequent operation can proceed from the output of the previous operation, which is stored as a checkpoint, and (2) these operation completions tend to be points in the computation where the query state is minimized, thus minimizing checkpoint storage requirements.

Making parallel execution of structured query language statements fault-tolerant

Approaches, techniques, and mechanisms are disclosed for maintaining a history of query plans executed for a database command, along with information related to each query plan. A database server receives a request to execute a particular command. The database server determines a plan for executing the particular command. The database server adds first information to a plan history associated with the particular command. The plan history comprises information related to a plurality of plans that have been generated for the particular command. The first information may include, for example, properties of the plan (including an outline of the plan) as well as statistics collected during execution of the plan. The database server may implement techniques for periodically refreshing information in a plan history. The database server may also implement techniques for purging old or less important plans.

Growing community of inventors

Foster City, CA, United States of America

Yali Zhu