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.
Do you know what this means?
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
Twitter
RSS
GooglePlus
Facebook
May 05, 2011 @ 08:23:00
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!
May 05, 2011 @ 13:06:37
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.
May 05, 2011 @ 13:12:07
You should post your plan to your blog and invite community to read and interpret it for you!