I know you can read, you’re here. But maybe you’re a functioning illiterate.
Here’s what I mean. You spend a lot of time in the database writing queries, analyzing data, doing important stuff.
Most of the time, things go great for you. You can answer questions for people. You make things happen. The wheels of the business bus go round-and-round.
But there’s one thing that really, really scares you.
Execution/Explain/Access Plans.
Those things that describe the steps the database optimizer takes to put together the data for your SQL queries.
This used to be me.
I spent years working in Oracle every day. How could I not know how to read a plan? Probably the same way kids graduate from high school, not being able to read a lick. You fake it.
They don’t teach this stuff.
Everyone starts on the same page here. They might teach SQL, they might teach set theory, or even relational algebra. But they don’t teach database optimizers generally in most programs (excluding graduate level and database vendor certification classes.)
Just start reading.
The next time you find a query that isn’t running as well as you want it to, get the plan. Try to read it. Not all at once. Break it up into parts. Circle the words you don’t recognize. Look them up on-line. Do a Google.
After awhile this stuff will start to make sense and it will ‘just click.’ This will open such a huge world for you. There’s nothing to fear here, jump on in!
Homework: Read This
Reading the Execution Plan
6 Comments
Hi Jeff,
first (as a lot of people) thanks for your website. It’s amazing. I’ll be honest with you. I’m using sqldeveloper since 1 year because my laptop now is under ubuntu. Before I was under PL/SQL. First time I used SQLDeveloper I was not very happy but now with your site I like this tool and I found a lot of interesting options.
Now my question : I lost myself in you website in clicking links and links and I found this page and this link on understand a explain plan. Because as you said I really don’t like reading an explain plan (like regex) but I know it’s necessary. So I was very happy … but your link is broken 🙁 Do you have another link as explain plan for dummy ?
I fixed the link.
In terms of other links/resources, you can’t go wrong with Oracle-Base.
Thanks, I’ll read this
You should post your plan to your blog and invite community to read and interpret it for you!
Hate trying to work out the order and I’m failing on the last example of the homework 🙁 The id# doesn’t match the order given in the description of the sequence of events so it leaves me generally at a loss to know why it puts the hash join at #1 if that is the last event. But I would be pleased to see the Index return of 1 row before the join with the deptno table, yes? Yesterday I had one that looked like it was doing a 300,000 row read of a table for every row on a 30,000,000 row table, bit of a mess really…ground to an io halt! but the explain plan still left me a little boggled as to whether that was what was actually happening. I think you can’t be lazy with them. You have to knuckle into them hard, I’m an explain planner scanner…it’s not the right approach!
It can’t be a casual approach, that’s for sure. But I know that many people are intimidated and you can’t let that keep you out of the game. Finding a good plan viewer can help a lot too.