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.
