How do you interpret a query's explain plan?

Question

I'm building an Ajax site that runs off of a root-level index.html file and uses history.js for pushState/popState, which I have updating the urls such that they are nice and clean without hashes or bangs (Example: site.com/section/1).

How can I do a mod_rewrite so that when a user tries to link to site.com/section/1 or site.com/section (or anywhere other than the root), the server serves up site.com/index.html?

From there the js would load the requested content in the url via ajax.

Solution 1

I shudder whenever I see comments that full tablescans are bad and index access is good. Full table scans, index range scans, fast full index scans, nested loops, merge join, hash joins etc. are simply access mechanisms that must be understood by the analyst and combined with a knowledge of the database structure and the purpose of a query in order to reach any meaningful conclusion.

A full scan is simply the most efficient way of reading a large proportion of the blocks of a data segment (a table or a table (sub)partition), and, while it often can indicate a performance problem, that is only in the context of whether it is an efficient mechanism for achieving the goals of the query. Speaking as a data warehouse and BI guy, my number one warning flag for performance is an index based access method and a nested loop.

So, for the mechanism of how to read an explain plan the Oracle documentation is a good guide: http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/ex_plan.htm#PFGRF009

Have a good read through the Performance Tuning Guide also.

Also have a google for "cardinality feedback", a technique in which an explain plan can be used to compare the estimations of cardinality at various stages in a query with the actual cardinalities experienced during the execution. Wolfgang Breitling is the author of the method, I believe.

So, bottom line: understand the access mechanisms. Understand the database. Understand the intention of the query. Avoid rules of thumb.

Solution 2

This subject is too big to answer in a question like this. You should take some time to read Oracle's Performance Tuning Guide

Solution 3

The two examples below show a FULL scan and a FAST scan using an INDEX.

It's best to concentrate on your Cost and Cardinality. Looking at the examples the use of the index reduces the Cost of running the query.

It's a bit more complicated (and i don't have a 100% handle on it) but basically the Cost is a function of CPU and IO cost, and the Cardinality is the number of rows Oracle expects to parse. Reducing both of these is a good thing.

Don't forget that the Cost of a query can be influenced by your query and the Oracle optimiser model (eg: COST, CHOOSE etc) and how often you run your statistics.

Example 1:

SCAN http://docs.google.com/a/shanghainetwork.org/File?id=dd8xj6nh_7fj3cr8dx_b

Example 2 using Indexes:

INDEX http://docs.google.com/a/fukuoka-now.com/File?id=dd8xj6nh_9fhsqvxcp_b

And as already suggested, watch out for TABLE SCAN. You can generally avoid these.

Solution 4

Looking for things like sequential scans can be somewhat useful, but the reality is in the numbers... except when the numbers are just estimates! What is usually far more useful than looking at a query plan is looking at the actual execution. In Postgres, this is the difference between EXPLAIN and EXPLAIN ANALYZE. EXPLAIN ANALYZE actually executes the query, and gets real timing information for every node. That lets you see what's actually happening, instead of what the planner thinks will happen. Many times you'll find that a sequential scan isn't an issue at all, instead it's something else in the query.

The other key is identifying what the actual expensive step is. Many graphical tools will use different sized arrows to indicate how much different parts of the plan cost. In that case, just look for steps that have thin arrows coming in and a thick arrow leaving. If you're not using a GUI you'll need to eyeball the numbers and look for where they suddenly get much larger. With a little practice it becomes fairly easy to pick out the problem areas.

Solution 5

Really for issues like these, the best thing to do is ASKTOM. In particular his answer to that question contains links to the online Oracle doc, where a lot of the those sorts of rules are explained.

One thing to keep in mind, is that explain plans are really best guesses.

It would be a good idea to learn to use sqlplus, and experiment with the AUTOTRACE command. With some hard numbers, you can generally make better decisions.

But you should ASKTOM. He knows all about it :)

View more solutions

Comments

[Admin]

Uh, Rule mode doesn't have costs... so I guess your statement is correct in a sort of absolutest way but I would say that it's fundamentally inaccurate. If you say CHOOSE, you could get the RBO or CBO. CBO is the only one that calcs a cost.

[Admin]

I would quibble a little bit with your use of "large"... sometimes the data can be so poorly clustered around your index columns that a FTS would out perform an index scan for even 10% of the rows...

[ScottCher]

Table Scan's are too often seen as bad things and it is initially what inexperienced people would focus on. This is highly dependant on the number of records being returned from that table, there is a threshold when its faster to do a full table scan rather than index lookup.

[ScottCher]

Table scans are not all bad - depending on the number of records returned/processed from the table, a full table scan can be faster than an index scan (if you are going to bring back the records anyway, you'll do a index scan and a full read from the table - 2 steps instead of 1).

[TheSoftwareJedi]

Downvoted for the outrageous advice. 90% of performance problems are NOT solved by temp tables and splitting up a query. What world do you live in?!

[puzzledbeginner]

@Jedi, I live in a world where indeces are mostly right and databases are pretty much structured sensibly. I'd be interested to read your answer, though.

[David Aldridge]

On the 10% -- absolutely. If you have 200 rows per block and you're looking for 0.5% of rows, then you might theoretically have to access 100% of the blocks to get all the values anyway, so it gets even more extreme than 10%.

[Alexander Malakhov]

link is broken. Live link. Here is updated version (for 11.2)

[a_horse_with_no_name]

(Full) Table scans do not necessarily purge the memory cache.