- About Us
- Events and Webinars
- Contact Us
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:
Centroid is a cloud services and technology company that provides Oracle enterprise workload consulting and managed services across Oracle, Azure, Amazon, Google, and private cloud. From applications to technology to infrastructure, Centroid’s depth of Oracle expertise and breadth of cloud capabilities helps clients modernize, transform, and grow their business to the next level.