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.
[IMAGE]
Exhibit 1
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
To continue reading for free, register below or login
To read more you must become a member of SearchOracle.com
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:
Believe it or not, some of the good SQL tuning tools on the market actually can auto-correct your code for you. Finding missing join predicates and unindexed WHERE statements is just a mouse click away in these tools.
Go to the main series page.
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.
