Execution Plans, Partitioning, and Parallelism in Oracle

What is Oracle’s Execution Plan?

An Oracle execution plan is a detailed set of steps showing how Oracle will access the requested data.

When a SQL statement runs, the parsed execution plan is stored in a view called V$SQL_PLAN.  Knowing how to read an execution plan is very important for developers and DBAs alike in understanding how the optimizer decided the access path.  Generally, Oracle does a very good job coming up with the right execution plan, but in cases where it doesn’t or can’t, you need to know how to interpret the execution plan.

The steps in an execution plan are a set of database operators that either produce or consume rows.  Producer steps feed consumer steps, and consumer steps operate on the rows generated from producer steps.  Typically, you’d read an execution plan in the format below (tabular format), which is what you’d get from an EXPLAIN PLAN or select from DBMS_XPLAN.DISPLAY query:


Partitioning Explain Plans

Partitioning is a means to allow a table, index, or IOT to be divided into multiple, smaller segments called “partitions”.  Partitioning is used to physically separate extents into different segments in order to facilitate things like ILM, improve maintenance operations flexibility, and potentially improve performance.   In the context of execution plan reading, the information to look for centers around partition pruning and partition elimination.  Partition pruning happens when the predicate in a query is able to eliminate non-relevant segment partitions from the execution plan, saving IO and CPU resources.  Partition elimination and partition pruning information is contained in the PSTART and PSTOP columns in the execution plan detail.  Here’s what the values for these mean depending on the partition type:

  • * The Pstart column represents the first partition used in the operation
  • * Pstop represents the last partition used in the operation.  If Pstart = Pstop = a partition number, it means the query was able to prune all but one partition from the segment, which indicates that a good deal of partition elimination likely occurred and performance was likely better
  • * The greater the difference between Pstart and Pstop, the more partitions are accessed, which undermines any performance benefit of partitioning
  • * When using single-level partitions (i.e., tables portioned at a single level), the partition numbers in the Pstart and Pstop partitions are absolute partition numbers and can be mapped to the partition number/name from DBA_TAB_PARTITIONS
  • * With composite partitions, the partition is a logical entity, not represented on disk as a physical segment.  Each sub-partition is labeled from 1 to X, where X=the relative sub-partition within a partition.
  • * Also, all sub-partitions in a composite partition are given a “global” partition number, which represent the physical partition numbers associated with the physical segments
  • * When Pstart and Pstop = KEY, it means the optimizer is not able to determine at parse-time partition is being accessed.  Further, it indicates that Oracle thinks that partition pruning will be possible at run-time – this is called dynamic partition pruning
  • * If partition pruning does not occur at run-time or is not evident in the execution plan, check your query predicates and ensure they align with partition columns.