Oracle International Corporation

Rushan Chen

Bhaskar Ghosh

Thierry Cruanes

Benoit Dageville

Mohamed Zait

Shrikanth Shankar

Patrick A Amor

Techniques are provided for executing query statements, or portions thereof, in parallel without using slave SQL to communicate to each slave the operations to be performed by the slave. Instead of generating one cursor for the query coordinator (QC) based on the original statement, and separate cursors for each DFO based on the slave SQL, the techniques involve sharing the cursor that is generated from the original statement among the various participants involved in the parallel execution of the operations specified in the original statement.

Compilation and processing a parallel single cursor model

Techniques are provided for generating execution plans for, and executing, database statements that contain filter subqueries. Upon receiving a database statement that contains a filter subquery, the database server builds an execution plan that includes a filter operation that is fed by a subtree that corresponds to the subquery. The database server performs a cost analysis to determine whether (1) the filter operation should be parallelized, and (2) the subquery tree should be parallelized. Techniques are also described for generating plans and executing queries where (1) both the filter operation and the subquery subtree are parallelized, (2) the filter operation is parallelized but the subquery subtree operation is performed serially, and (3) the filter operation is performed serially but the subquery subtree operation is parallelized.

Executing filter subqueries using a parallel single cursor model

Techniques are provided for performing a parallel aggregation operation on data that resides in a container, such as a relational table. During generation of the execution plan for the operation, it is determined whether partition-wise aggregation should be performed, based on the grouping keys involved in the aggregation and the partition keys used to partition the container. If partition-wise aggregation is to be performed, then the assignments given to the slave processes that are assigned to scan a container are made on a partition-wise basis. The scan slaves themselves may perform full or partial aggregation (depending on whether they are the only scan slaves assigned to the partition). If the scan slaves perform no aggregation, or only partial aggregation, then the scan slaves redistribute the data items to aggregation slaves that are local to the scan slaves.

Parallel partition-wise aggregation

A query coordinator handles a multiple-server dynamic performance query by sending remote query slaves (1) first information for generating a complete plan for the query, and (2) second information for participating in the dynamic performance view portion of the query. If the slaves on the remote servers are unable to use the first information to generate an equivalent query (for example, if they reside in a database server that has closed the database), then the slaves on the remote servers use the second information to participate in the dynamic performance view portion of the query.

Dynamic performance views with a parallel single cursor model

A method is described for parallel processing of sequence operations, in which contention for the sequence operation is reduced among multiple parallel processes. Contention is reduced by caching sequence values locally for each parallel process. Each process accesses the sequence operation, or an instance level cache of sequence values, in a batched manner, thereby returning a block of unique sequence values instead of a single sequence value. The block of sequence values, or at least information that indicates a range of sequence values that define the block, is then cached locally in association with a given process of the multiple processes. Hence, future requests for sequence values from the given process are serviced from the local cache, rather than having to access the sequence operation and risk contention for the operation with other processes.

Growing community of inventors

Belmont, CA, United States of America

Rushan Chen