How to Read an Execution Plan in Oracle

Learn Oracle SQL Tuning!

In this free tutorial you will learn how to generate and read (interpret) an execution plan in Oracle Databases.

Understanding what the Oracle Database does with your SQL is essential to tuning – and the execution plan is the key.

Oracle Certified Master DBA John Watson will provide a brief introduction (4 minutes) – which includes John’s tuning methodology, then demonstrate EXPLAIN PLAN, SQL*Plus AUTOTRACE and DBMS_XPLAN.DISPLAY_CURSOR.  In the demonstration, John will teach you:

  • How to read an execution plan
  • Find the 1st step in the plan
  • Decipher the order of the steps in the plan
  • That EXPLAIN PLAN can be very misleading

Prerequisites: To get the most from this tutorial, you should:

  • Know how to code SQL
  • Be familiar with SQL*Plus
  • Know – in very general terms – what an execution plan is.
  • Have a basic understanding of the Library Cache (this is where Oracle Database stores parsed SQL statements)
  • Have a basic understanding of the Cost Based Optimizer (this is the part of the database that parses your SQL, creates an execution plan.  Hopefully the correct – most efficient – plan).

Next Step:  12c Adaptive Execution Plans and 12c Re-Optimization of Execution Plans

Need Help? Call us! (888) 803-5607 

  • How to read an Oracle Database SQL Execution Plan (13:139) (click on video below)

Date: Mar 30, 2014


NOTE: Some corporate firewalls will not allow videos hosted by YouTube.

Transcript

How to read an Oracle Database SQL Execution Plan

>> John:  This is the first of a series of tutorials we intend to record on Tuning SQL. In this one, just the introductory tutorial, we aren’t actually going to do any tuning. This is just to make sure that we all have the current prerequisite knowledge to enable us to tune SQL. That prerequisite knowledge is the ability to understand what Oracle is actually doing with our code. To understand what Oracle is doing, we need to look at the execution plans for the problem SQL statements.

 

I intend to go through techniques for generating execution plans, how to read an execution plan, and then certain suspicious structures that we should look for in an execution plan. Certain operations that may be good, may be bad, but certain operations that once you investigate to see if they really are being done in the correct way.

 

[pause]

 

Why do you need to look at execution plans at all? It’s because they are a vital part of your tuning methodology. There is no automatic way to tune SQL. If there were, Oracle would do it for us.

 

Of course, there are tools that may help, that certain wizards provided by Oracle endorsed by third party companies. We’ve got the SQL Tuning Advisor. We have the SQL Access Advisor. But if these wizards could do the job, we wouldn’t need to tune SQL at all, but they can’t do it for us. All they do is apply general principles to particular problems. It may help but it’s never going to solve the problem completely.

 

[pause]

 

My own experience is that the various wizards very rarely give advice which is definitely wrong. But they would usually give advice that could be improved upon. So the approach I take is that if the client has the licenses, sure on the tools. But don’t just blindly do what they say. Look at the advice they’re giving, work out why they’re giving it and you will usually find you can do a much better job with the extra knowledge that they’ve provided. They can’t do the job for you. You have to do the work. And that means you have to develop a tuning methodology.

 

The methodology I follow, four simple steps. First, identify the problem SQL. That’s a SQL that is a problem in business terms. It means maybe the batch jobs that should run overnight but don’t finish until lunchtime. The screen that takes 30 seconds to refresh and you need it refreshed in one second. A report that takes five hours to run and you need it in 10 minutes. Find out the problem SQL.

 

Having identified the SQL, work out what Oracle is doing to it. What is the cost-based optimizer doing? How is it executing that SQL?

 

When we can see what the cost-based optimizer is doing, we then need to work out why the cost-based optimizer is doing it that way? Then the final step, if you think that the cost-based optimizer is perhaps not making the best decisions, you push the optimizer to make a different decision, to run the code in a different and better way.

 

Execution plans are vital part of this process. They will tell us what happened and will give us a lot of information to work out why it happened that way.

 

[pause]

 

There are three commonly used techniques for generating execution plans and I’ll go through them all. First, the use of Explain Plan. This does a hard parse but it doesn’t actually execute the statement, so the plan it generates may not be the one that would actually be used.

 

Then there is SQL Plus Autotrace. That does execute statements and that run explain plans against it and it shows you some execution statistics. For the whole truth, we need to either use DBMS_XPLAN.DISPLAY_CURSOR to look at information in the library cache stored within the cursor or we need to enable SQL trace and analyze the full detailed information gathered during run time.

 

[pause]

 

Now that we’ve had a look at the theory, let’s actually do it. Let’s generate and read an execution plan.

 

I’m going to work in the scott demonstration schema and this is just an absolutely normal release 12.1 database. Explain plan first.

 

[pause]

 

I’ll run the explain plan commands. Explain plan for select ename, dname from emp natural join dept is being explained. How do I show the plan? The easiest way is actually to use the dbms_xplan.display function which returns table. Select * from table (dbms_xplan.display). I’m relying totally on default. There are a number of documents you can put through. This shows me the execution plan that was developed. Remember explain plans are hard parse that doesn’t do anything else.

 

The plan that it’s come out with is this. How do we read a plan? Start at the top. Every one operation may or may not have child operations. A child operation is beneath and indented. This tells me that the select statement, operation ID 0 has one child operation – this nested loop, ID number one.

 

The nested loop has two child operations. How do I know that? Because there are two operations beneath it at the same level with indentation. The first child operation is this nested loop. The second child operation is not nested loop. So both two and five are child operations of one.

 

[pause]

 

Operation two itself has two child operations. It has table access full and index unique scan, operations three and four. The same level of indentation, therefore, both child operations of this.

 

This is the first operation to start. Hang on to that fact. The first operation is the first operation that has no child and that’s just the first operation that has no child. When we run the statement we’ll begin with the full scan of emp.

 

The second operation is the second child of this same nested loop which is index unique scan. To run this statement we’ll scan emp. As we scan emp, we’ll be doing index lookups into pk_dept. When we’ve completed those two, the scan of emp and the index lookup, we’ve implemented operation two, the nested loop. So the two child operations implement the parents.

 

[pause]

 

What happens after that? We’ve got another nested loop up here. The nested loop of one have two child operations. This and this. As we complete the nested loop here, we can begin to do this as well. We can do the table access into dept to retrieve the row.

 

Having done that, we’ve implemented that nested loop which implemented the select statement. The order in which these operations start it is operation three, operation four which implements two. Then operation five will start which will complete the implementation of one which implements zero. In this particular plan, it’s a full scan of emp, unique scan of pk_dept, table lookup into dept.

 

[pause]

 

But is this the truth? Is this what would actually happen? There are two things to consider. These column rows, this is estimated cardinality. Oracle thinks that that full scan of emp will return one row. Will it return one row? I don’t know. We’ll find out.

 

Furthermore is the actual plan that one will be used. That too is open to debates. Explain plan’s only a prediction. A predicted plan, unpredicted cardinalities. That moves us on to the next technique which is to use the autotrace facility. Very useful, very useful indeed.

 

To use it, set autotrace on. There are some other options. Now I’ll actually run the statement. Run the statement. Select ename, dname from emp natural join dept.

 

[pause]

 

And back come 14 rows.

 

What about the truth? We’ve got the same execution plan and the same predicted rows. So now we do know already that the estimated cardinalities were wrong. But what is correct of course is the statistics down here. That statement did do 15 consistent guess.

 

This is giving us a certain amount of useful information. It’s telling us of course exactly what was returned so we know that’s wrong and it’s telling us how much IO is involved. It is still isn’t necessarily telling us that this plan was the plan that was used. To determine the full truth, we need to take things a step further.

 

First, I shall disable autotrace. Now I need to run the statements again but this time with a hint. So I’m going to run the statement like this. I’m including the hint gather_plan_statistics. That instructs Oracle to gather detailed information about how the statement really was run. So I’ll run it. Back comes 14 rows. How do I now get the full information out?

 

[pause]

 

Another function call dbms_xplan.display_cursor. This is going to go to the library cache of the shared pool and pull out format allstats last, all possible information about the last statement I run out. There are few other options you can give it but in summary this is going to tell me all it’s got to know about these statements. What does it tell me?

 

[pause]

 

Very interesting indeed. It tells me the plan was in fact a hash join. What Oracle actually did – he did not do a nested loop starting with the full scan of emp then index lookups. It shows a totally different plan.

 

To read this plan what did it do? That has one child which is that. The hash join has two children, operations two and three. So the first thing that happened is the first operation with no child which is operation ID 2, full scan of emp.

 

Oracle scanned emp, read the table into memory, and now we see more interesting information. It did that once. Starts once. It expected one row. It actually got 14 rows. Estimated rows, actual rows. Oracle expected one row, it got 14. He read those 14 rows into memory then did a scan of dept. A hash join, you read one set into memory and then pass through the second row set probing the first set. It expected one row there, it got four.

 

This is the full truth. If determined, the explain plan was basically lying. Explain plans said that it would use a nested loop returning one row. In fact, Oracle decided to use a hash join and it returned 14 rows.

 

[pause]

 

This is very good information but still isn’t the full story. This is not telling us about the disc and memory I/O. To get enough information we need to enable SQL trace.

 

With that, I come to the end of our first segment of this series of lectures. In later segments we’ll be looking at using SQL trace to give us the whole truth, exactly what happened and why it happened that way. Then we’ll move on to the critical decisions. Most important being join order. In what sequence do you join your tables? If there’s such thing as a silver bullet in SQL Tuning it’s getting the join order.

 

Having determined the join order, we’ll look at the method to implementing those joins. Having determined the join method, we’ll look at the access method. How we’re getting to the actual data.

 

Then all the segments we’ll be looking at the common errors which I call the red flags. These are SQLs constructs that may be good, may be bad but are often used inappropriately.

 

Finally, how do we make the cost-based optimizer do its job better? How do we push it towards better decisions? That is all there is to SQL Tuning.

Copyright SkillBuilders.com 2017

×
Free Online Registration Required

The tutorial session you want to view requires your registering with us.

It’s fast and easy, and totally FREE.

And best of all, once you are registered, you’ll also have access to all the other 100’s of FREE Video Tutorials we offer!

 

×