Skip to main content

About Execution Plans

🏠   Knowledge-base home > Execution Plans

What are Execution Plans?​

The execution plan show the steps chosen by the database optimiser for a particular SQL statement. A statement's execution plan is the sequence of operations the database performs to run the statement. Those step could include methods for scanning the rows in a table or index, the join method used to join tables together and any sorting required.

How do I generate an Execution plan?​

It varies between database engines but typically there will be a command such as EXPLAIN PLAN for <SQL statement> that can be used to return the execution plan for a statement. In some cases you can also retrieve a cached plan from the database of the existing plan for a statement that is still cached. V$SQL_PLAN for Oracle or sys.dm_exec_cached_plans for SQL Server for example, will return the cached plan for a statement.

How do I interpret an Execution plan?​

Execution plan steps will have a cost associated with them. The higher the cost, the more expensive that step is.

Here are the types of operations that can cause high execution plan costs (which will usually be associated with slow performance):

  • Full table scans (or sequential scans depending on the database type)
  • Unselective range scans
  • Late predicate filters
  • Wrong join order
  • Late filter operations

Which statement should I get an execution plan for?​

Usually you will want to get an execution plan for your top statements. See how to use wait events to identify your top SQL statements. As a developer you might also want to get an execution plan for your statements before they are released to production. Note that, plans can be different between dev and prod if there are differences in things such ass data volumes, schema version, parameters etc. (see below)

What can affect the cost of an Execution plan?​

Schema changes such as adding index can alter an execution plan. In fact this is the most common way to optimise a database statement. By adding appropriate indexes you can massively reduce the number of rows which need to be scanned by more efficient filtering, reduce the rows which need to be sorted since indexes have a natural sort order, and join tables more effectively.

The cost of an Execution plan could also change if the data in the underlying tables changes in size or distribution. Changes to the table and index statistics which track things like number of rows and column cardinality, could cause a different plan to be used for a statement.

Database parameters can also cause execution plans to change as they might make the database optimiser prefer a different table access or join method for example.

Tools for generating explain plans​

You can use one of the command line options above depending on the type of database. However we would recommend that you use a graphical tool (such as DBmarlin 😀) which can present the execution plan in an easy to digest format.

DBmarlin Trial