Intro To Execution Plans

When there is a performance problem in the database, what is the most important thing that you need to know?
The execution plan of the SQL Statement that is causing the problem.
When a SQL Statement starts running slower than it used to, what is the first thing you are going to check?
The execution plan. Did it change between executions?
What is intimidating to some DBAs?
Execution plans, especially the ones that span multiple pages.

Are you afraid of dealing with execution plans? Do you feel intimidated by execution plans? If you answered yes, most likely the root cause is the fact that you don’t have a good understanding of execution plans.
Usually we are afraid, and we try to avoid things we don’t understand.
But not anymore.

Do you know what execution plans are?
Do you know how to read them?
Do you know how to collect the information on execution plans?

If you answered NO to any of these questions, then this mini-series is for you.

Today is an intro to execution plans, and there is more to come next week.

What Is An Execution Plan?
Why Is It Important To Know The Execution Plan?
Where Do You Find Information About Execution Plans?

What Is An Execution Plan?

An execution plan is the blueprint that the optimizer uses to execute a SQL Statement. This plan describes the operations the optimizer executes to get the data for the SQL Statement. In other words, the execution plan will provide a set of “instructions” to the optimizer telling what operations to run, and how to run them, in order to get the results for the SQL Statement.

Examples of instructions would be “HASH JOIN”, “TABLE ACCESS FULL”, “INDEX RANGE SCAN” and so on.
These instructions tell the SQL engine to go get the data by a “FULL TABLE SCAN” of table X or by “INDEX RANGE SCAN” of index Y. Once the data is gathered, join the results with a “HASH JOIN”.

Does this make sense?

Why Is It Important To Know The Execution Plan?

Anytime you are troubleshooting a performance problem for a SQL Statement, the first thing you should look at is the execution plan.
You must obtain the execution plan for the SQL Statement that you are troubleshooting.

Why?

Because otherwise you are in the dark! You have no idea how the statement is executing, you have no idea what is happening behind the scene. The only thing you can do when you don’t have the execution plan, is to assume. And you know where assuming leads you…nowhere! A Good DBA Never Assumes!

When dealing with execution plans, there are a few things you need to know: how to get the execution plan of a SQL Statement, how to read the execution plan once you got it, and how to evaluate the execution plan (is it good or is it bad).

Where Do You Find Information About Execution Plans?

Oracle associates a numerical representation with the Execution Plan, and this is called Plan Hash Value. This number should come in handy when comparing execution plans. In theory, if the Plan Hash Value (PHV) is the same, the execution plans should be the same. In practice that is not always the case.

If the Execution Plan is so important, where can you find information about it?

There are lots of dictionary views where the PHV and associated information is available.

V$ views:
V$SQL_PLAN
V$SQL_PLAN_STATISTICS
V$SQL_PLAN_STATISTICS_ALL
V$SQL_WORKAREA

AWR views: (warning this requires extra licensing)
DBA_HIST_SQL_PLAN

Statspack:
STATS$SQL_PLAN

dbms_xplan package

Now you have a basic understanding of execution plans. Are you thirsty for more?

Follow me next week, when I’ll show you how to actually display the execution plan in multiple ways.

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

–Diana