Partitioned Query Explain Plans

on November 28, 2012

Oracle optimizes partitioned queries differently than regular queries. Oracle tries to pick the fastest elapsed time to the data, not the lowest cost.

How the Optimizer scans partitions

The Optimizer typically scans partitions first:

Scans only relevant partitions (called pruning)
Try to avoid functions on partitioning key values in WHERE clauses
Try to avoid subqueries on partitioning key values
Maybe avoid indexes on partitioning keys (Oracle uses efficient multi-block scans with fewer I/O than using indexes and single block reads)
Want to avoid “long ops”operations

Oracle Parallel Server and RAC instances can break a SQL up across processors and return the pieces for a much faster response time on larger objects.

TIP

Consider where your data is in the partitioned object. If the selected rows are across the partition, it might be considerably faster to do a full-table scan (using multi-block read-ahead of course) than to do single block reads, even on a local-partitioned index.

A closer look at a partitioned query explain plan

The example below shows a rather busy partitioned query explain plan.

Hotka Page 88 Part Query

Example Partitioned Query

Follow the most indented operation around line 13 for the first step of this operation (i.e., the most indented using this block mode explain plan).

Like the CBO regular queries, it is equally important to eliminate most of the rows as early as possible in the processing.

Understanding the explain plan columns for a partitioned query

The Pstart and Pstop columns show the partitions being accessed.
- Generally, you want to see numbers in here that are a distinct subset of the entire set of partitions.
- The :BF0000 shown is a Bloom Filter, a type of hash table that tracks the matching rows from a join operation and prunes dependent on a Join condition.
- If you see KEY, then the partitioning range will be determined at run time. Review the query and see if there is a function or sub query on the partitioning key value that is not allowing for the pruning of partitions.
In parallel operations, the IN-OUT column shows the type of operation that Oracle will perform on this step.
- You really want to see text that starts with a ‘P’ in this column, which indicates parallel processing.
- If you see text that starts with an ‘S,’ that indicates serial processing.
Producers generate the intermediate result sets and consumers (think of these as the readers of the producers) are the ones that use the output from the producers.
The PQ Distribution column determines how the producers will distribute the result sets (rows) they processed to the consumers.
- Hash redistribution is used to achieve an equal distribution among the parallel server processes. A hash function is applied to the join column and the result dictates which consumer parallel server process should receive the row.
Broadcast
- Usually small result set
- Sent to seach server (consumer)
Range
- Parallel Sort operation
- If you see QC here it means the data is already in the correct order
Partitioning Key Distribution (Part Key):
- Target table is partitioned
- Producers will map rows to consumer on the partitioning column
Round Robin
- Randomly distributes data across consumers

Processing assignments

Granules are a way of assigning resources to parallel processing. The access method and the object type determine the number of granules. Only one granule is allocated at a time.

Individual partitions can be spread across processes using this granular approach to table block processing. Entire partitions can be assigned to single process too. How Oracle processed a particular explain plan step is obvious in the line above the access method: