SQL Engine - How to read a physical plan (Execution Plan | Execution Tree)

Oracle Database Sql Processing

About

How to read a physical plan

The figure below is an execution tree or physical plan, that shows the flow of row sources from one step to another.

In general, the order of the steps (relational operator) in execution is the reverse of the order in the execution plan, so you read the plan from the bottom up. Initial spaces in the Operation column indicate hierarchical relationships.

For example, if the name of an operation is preceded by two spaces, then this operation is a child of an operation preceded by one space. Operations preceded by one space are children of the SELECT statement itself.

Example

Oracle Database Sql Execution Parse Tree

In this figure each node of the tree acts as a row source, which means that each step of the execution plan either retrieves rows from the database or accepts rows from one or more row sources as input. The SQL engine executes each row source as follows:

  • Steps indicated by the black boxes physically retrieve data from an object in the database. These steps are the access paths, or techniques for retrieving data from the database.
    • Step 6 uses a full table scan to retrieve all rows from the departments table.
    • Step 5 uses a full table scan to retrieve all rows from the jobs table.
    • Step 4 scans the emp_name_ix index in order, looking for each key that begins with the letter A and retrieving the corresponding rowid (see “Index Range Scan”). For example, the rowid corresponding to Atkinson is AAAPzRAAFAAAABSAAe.
    • Step 3 retrieves from the employees table the rows whose rowids were returned by Step 4. For example, the database uses rowid AAAPzRAAFAAAABSAAe to retrieve the row for Atkinson.
  • Steps indicated by the clear boxes operate on row sources.
    • Step 2 performs a hash join, accepting row sources from Steps 3 and 5, joining each row from the Step 5 row source to its corresponding row in Step 3, and returning the resulting rows to Step 1. For example, the row for employee Atkinson is associated with the job name Stock Clerk.
    • Step 1 performs another hash join, accepting row sources from Steps 2 and 6, joining each row from the Step 6 source to its corresponding row in Step 2, and returning the result to the client. For example, the row for employee Atkinson is associated with the department named Shipping.

In some execution plans the steps are iterative and in others sequential. The plan shown in the SQL Row Source Generation is iterative because the SQL engine moves from index to table to client and then repeats the steps.

During execution, the database reads the data from disk into memory if the data is not in memory. The database also takes out any locks and latches necessary to ensure data integrity and logs any changes made during the SQL execution. The final stage of processing a SQL statement is closing the cursor.





Discover More
Card Puncher Data Processing
Hive - Engine

The SQL Processing engine of hive SQL query are converted to a physical operator tree which is optimized and converted to the underlining engine via the calcite engine. Most of the existing query optimizations...
Data System Architecture
RDBMS - Cursor (Iterator)

A cursor represents data returned by a query. In computer science, a database cursor is a control structure that enables traversal over the records in a database. A cursor: contains tabular data ...
Oracle Database Sql Execution Parse Tree
SQL Engine - How to read a query plan ?

How to read a query plan (ie a execution tree) ? Each row in the output table corresponds to a single step in the execution plan. The first step is the furthest indented to the right. As it's...
Logical Query Plan
Sql Engine - Logical Plan (Query)

A execution plan is an ordered set of steps to execute a query During SQL processing, the row source generator receives the optimal execution plan from the optimizer and produces an iterative plan, called...
Oracle Database Sql Processing
Sql Engine - Relational Operator (Data operations|Execution Plan Steps)

Relational operator They are the step of a plan (execution parse tree). List read () filter, sort, join aggregation .... Type of operation using the work area: SORT, HASH JOIN,...



Share this page:
Follow us:
Task Runner