The following is the seventh part of a 12-part series on Oracle10g CBO internals and SQL tuning optimization. Each tip is excerpted from the not-yet-released Rampant TechPress book, "Oracle SQL and index internals," by Kimberly Floss. Check back to the main series page for upcoming installments.
EXPLAIN and understand
As someone who has worked in the database trenches for more than 15 years now, I have a terrible confession to make: I have never been really good at reading EXPLAIN plan output. Oh, I can do the basics like recognize table scans, spot Cartesian joins, and zero in on unnecessary sort operations; but when the EXPLAIN output "wave" starts rolling back and forth in large SQL EXPLAINs, I tend to get a little lost.
Some of the better SQL analysis tools are now sporting a new EXPLAIN format that makes following the access path trail a lot easier to read (see Exhibit 1). For people like me who were never good at traditional EXPLAIN output, it makes getting to the root of a bad SQL statement much simpler.
Whether you have access to the new, modern format shown in Exhibit 1 or still are forced to trudge through the standard EXPLAIN output, the EXPLAIN is the next stop on your SQL perfection checklist. The EXPLAIN is the tool to use in spotting obvious flaws in your code and brings to the surface what the database optimizer is doing underneath the covers to produce your desired result set. Remember, the EXPLAIN saved Jeff from launching the ultimate query from "you-know-where."
Correct obvious flaws
So what are some obvious red flags that can show up in an EXPLAIN? While such a large topic can not be covered in an chapter of this nature, we can go over some of the major items that should raise some eyebrows:
- Cartesian product. Jeff's query, which had an estimated response time of 56 years, suffered from a terrible case of Cartesian joins. His WHERE predicate did not contain a single correct join in the many million row tables he was trying to access. Although some optimizers will automatically try to rewrite SQL and will actually use a Cartesian product to accomplish its mission, seeing a Cartesian join in your EXPLAIN plan is usually not a good thing. If observed, check your WHERE predicate to ensure you are adhering to the N – 1 rule of thumb (for example, ten tables in a FROM clause will require nine proper join conditions).
- Table scan. So you thought you were using an index, did you? There are numerous conditions that can negate the use of an index (e.g., use of NOT, failing to use the starting column of a concatenated index, use of expressions such as WHERE PURCHASE_PRICE = LIST_PRICE * 1.2). Or perhaps the SQL is fine, but an improper indexing scheme is being used and needs to be changed. Remember, however, that you are really looking for table scans on large tables. Small lookup tables are often actually accessed faster when the database engine caches the whole table and scans it rather than using an available index.
- Unnecessary sort. Can your query do without the DISTINCT clause you have in the code? Can the UNION be replaced with a UNION ALL? Knowing when and how to yank sort activity out of an SQL statement can go a long way toward improving its response time.
- Nonselective index scan. If you've followed your checklist and understand the demographics of the objects used in your query, then you should know which indexes are selective and which are not. While cost-based optimizers should ignore indexes with poor selectivity, rule-based approaches may not. Be on the lookout for these types of scans because not every index scan is a good one.
About the author
Kimberly Floss is one of the most-respected Oracle database administrators in the U.S., and is president of the International Oracle Users Group (IOUG). With more than a decade of experience, Kimberly specializes in Oracle performance tuning and is a respected expert in SQL tuning techniques. She is an active member of the Chicago Oracle Users Group, and the Midwest Oracle Users Group, in addition to the IOUG. Kimberly Floss has over 15 years of experience in the information technology industry, with specific focus on relational database technology, including Oracle, DB2, Microsoft SQL Server and Sybase. She holds a bachelor's of science degree in computer information systems from Purdue University, specializing in systems analysis and design, and has an MBA with emphasis in management information systems from Loyola University.