Sql Engine - (Physical|Execution) Plan

Oracle Database Sql Processing

About

A physical plan (or execution plan) is an ordered tree of physical relational operator that represent the execution a query

It is also known as:

  • execution plan
  • execution tree

It's the output of the optimizer. that describes an optimum method of execution.

A physical plan shows how the operation will be executed/implemented whereas a logical plan do not.

The execution plan takes the form of a combination of steps that the Database uses to execute a SQL statement.

Each step returns a row set. Each step either retrieves rows of data physically from the database and/or prepares them in some way for the user issuing the statement.

In some execution plans the steps are iterative and in others sequential. A plan is iterative when for instance the SQL engine moves from index to table to client and then repeats the steps.

When executed by the SQL virtual machine, the plan produces the result set.

You can get the execution plan through various mechanisms.

You can get:

You can influence the execution plan (e.g. the query optimizer) by

The query plan might not be the same from one version of Oracle Database to the next. In recent versions, the optimizer might make different decisions, because better information is available.

Query Plan For The Same Query Response Time

Operations and Pseudo Code

For the below SQL,

select 
  * 
from 
  order o, item i
where
  o.order = i.order

You have two type of join.

The nested join will go faster than a hash join for a few records because it don't need to create the data structure

Option 1 - Nested Loop Join

  • the nested loop-join has O(n2) time complexity.
  • square complexity
for each record i in Item
   for each record o in Order
       if o.order = i.order
           return matching pair

Option 2 - Data Structure Join

  • Constant time
  • The hash-join has O(n) time complexity
for each record i in Item         
   insert into a data structure (such as a hash table)

for each record o in Order:
   lookup corresponding records in hashtable
   return matching pair

Reviewing plans and tuning

It's important when reviewing and tuning queries to emulate what your application does. You cannot tune a query with literals and expect a query that contains bind variables to have the same performance characteristics.

Tuning this sql :

select * from some_table where column = 55

is different of tuning this one :

select * from some_table where column = :bind_variable

Performance Tool kit

In single-user mode :

In multi-user mode :

Others





Discover More
Card Puncher Data Processing
Calcite - Physical Plan

in calcite. The physical plan is the relation algebra expression that describe how to perform the operation on the data. It's the output of the optimizer Example of output of a physical_plan (There...
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...
Card Puncher Data Processing
Oracle Database

Documentation about the Oracle database
Card Puncher Data Processing
Oracle Database - (Actual|Final) Execution Plan

The actual or final plan is the execution that was executed in order to retrieve the result of a SQL. optimizerquery planrow source generator V view contains the actual plan for a query that...
Card Puncher Data Processing
Oracle Database - Adaptive Plan

Adaptive plan is an adaptive functionality. The Adaptive plan functionality enables the optimizer to: generate multiple predetermined subplans defer the subplan decision until execution time based...
Card Puncher Data Processing
Oracle Database - Automatic reoptimization / (statistics|cardinality) feedback

Statistics feedback also known as: cardinality feedback Automatic reoptimization is an adaptive feature. It improves plans for repeated queries that have cardinality misestimates. adaptive plans...
Sql Developer Autotrace
Oracle Database - Autotrace

Autotrace is: a system variable of sql plus can also be found in SQL Developer AUTOTRACE provides you: an execution plan (such as explain plan) and some important regarding its actual execution....
Card Puncher Data Processing
Oracle Database - Buffer IO (Logical IO)

A buffer is a container for data. A logical I/O, also known as a buffer I/O, refers to reads and writes of buffers in the buffer cache. When a requested buffer is not found in memory, the database performs...
Card Puncher Data Processing
Oracle Database - DBMS_XPLAN

Dbms_xplan is a method for: querying and displaying the explain plan output. display the plan of a statement stored: in the Automatic Workload Repository (AWR) or in a SQL tuning set. It...
Card Puncher Data Processing
Oracle Database - Example of query Tuning (to decrease the logical I/O)

To decrease the , you have several possibilities : first, you must compute the statistic to give all information to the in order to find the best . second, to add an index (Not always good) precompute...



Share this page:
Follow us:
Task Runner